Don't show duplicates 1

[jadams0173]

I need to get the most recent records from tblActions using tblHeader.MO as criteria without returning duplicates for the same PROBLEM in tblactions.

SELECT tblHeader.MO, tblActions.PROBLEM, tblActions.OWNER, tblActions.AREA, tblActions.PRIORITY, tblActions.INPUTDATE, tblActions.DATECLOSED, tblActions.ONREPORT, tblActions.EnteredBy, tblActions.ActionComment, tblActions.CLOSEBY
FROM tblHeader INNER JOIN tblActions ON tblHeader.HeaderID = tblActions.HEADERID
GROUP BY tblHeader.MO, tblActions.PROBLEM, tblActions.OWNER, tblActions.AREA, tblActions.PRIORITY, tblActions.INPUTDATE, tblActions.DATECLOSED, tblActions.ONREPORT, tblActions.EnteredBy, tblActions.ActionComment, tblActions.CLOSEBY
HAVING (((tblHeader.MO)="123456"));

The table structures are:

tblHeader tblActions
ID (PK) (autonumber) HeaderID(FK)
MO ID(PK) (autonumber)

to make a 1 to many relationship.


example as of now:

MO Problem Owner InputDate CloseBy DateClosed
1234 test me 2/21/2007 2/22/2007 01/01/1900
1234 test1 you 2/21/2007 2/25/2007 01/01/1900
1234 new me 2/21/2007 2/22/2007 01/01/1900
1234 new1 us 2/21/2007 2/26/2007 01/01/1900


What I would like is:
MO Problem Owner InputDate CloseBy DateClosed
1234 test1 you 2/21/2007 2/25/2007 01/01/1900
1234 new1 us 2/21/2007 2/26/2007 01/01/1900






I tried to have patience but it took to long! -DW

 

[jadams0173]

Not to be impatient, but is this doable in one query? I'm still working on it.

I tried to have patience but it took to long! -DW

 

[PHV]

How the query should know that 'test' and 'test1' are same problem ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Hey phv. I was going to use the Max of the ID in tblactions to return the currect actions. You bring up a good point.


Here is what I have now. I'm trying to come up with a way to combine these 2 queries and I think I will have my solution. The first query is the same as posted above.

SELECT H.MO, A.PROBLEM, A.OWNER, A.AREA, A.PRIORITY, A.INPUTDATE, A.DATECLOSED, A.CLOSEBY, A.ONREPORT, A.EnteredBy, A.ActionComment
FROM tblHeader AS H INNER JOIN tblACTIONS AS A ON H.HeaderID = A.HEADERID
GROUP BY H.MO, A.PROBLEM, A.OWNER, A.AREA, A.PRIORITY, A.INPUTDATE, A.DATECLOSED, A.CLOSEBY, A.ONREPORT, A.EnteredBy, A.ActionComment
HAVING (((H.MO)="123456"));

This query will return the ID's from tblActions that I need show. Is there a way to make the above query use the results of the query below and retrun only those records?

SELECT tblHeader.MO, Max(tblActions.ID) AS MaxOfID, tblActions.PROBLEM
FROM tblHeader INNER JOIN tblActions ON tblHeader.HeaderID = tblActions.HEADERID
GROUP BY tblHeader.MO, tblActions.PROBLEM
HAVING (((tblHeader.MO)="123456"));

I tried a couple of different ways but kept getting an error about not using the EXIST keyword.



I tried to have patience but it took to long! -DW

 

[jadams0173]

OK, I'm still working but not much closer. I'm still trying to somehow "merge" two queries together. The 2 queris have a PK FK on HeaderID.

If I could some how get this query:

SELECT tblActions.ID, tblHeader.MO, tblActions.PROBLEM, tblActions.OWNER, tblActions.AREA, tblActions.PRIORITY, tblActions.INPUTDATE, tblActions.DATECLOSED, tblActions.CLOSEBY, tblActions.ONREPORT, tblActions.EnteredBy, tblActions.ActionComment
FROM tblHeader INNER JOIN tblActions ON tblHeader.HeaderID = tblActions.HEADERID
GROUP BY tblActions.ID, tblHeader.MO, tblActions.PROBLEM, tblActions.OWNER, tblActions.AREA, tblActions.PRIORITY, tblActions.INPUTDATE, tblActions.DATECLOSED, tblActions.CLOSEBY, tblActions.ONREPORT, tblActions.EnteredBy, tblActions.ActionComment
HAVING (((tblActions.ID) In (212,213,214,215,216,217,208,209)) AND ((tblHeader.MO)="123456"));

to use this query in place of 212,213,214,215,216,217,208,209 I'd have it

SELECT tblHeader.MO, Max(tblActions.ID) AS MaxOfID, tblActions.PROBLEM
FROM tblHeader INNER JOIN tblActions ON tblHeader.HeaderID = tblActions.HEADERID
GROUP BY tblHeader.MO, tblActions.PROBLEM
HAVING (((tblHeader.MO)="123456"));

I'm trying to do this, but I'm stuck. Please help. I've tried but keep getting the error stating my query would return more than one value and I need to use the EXIST keyword.



I tried to have patience but it took to long! -DW

 

[PHV]

Something like this ?
SELECT H.MO, A.PROBLEM, A.OWNER, A.AREA, A.PRIORITY, A.INPUTDATE, A.DATECLOSED, A.CLOSEBY, A.ONREPORT, A.EnteredBy, A.ActionComment
FROM tblHeader AS H INNER JOIN tblACTIONS AS A ON H.HeaderID = A.HEADERID
WHERE H.MO='123456' AND A.ID In (
SELECT Max(Y.ID) FROM tblHeader AS X INNER JOIN tblActions AS Y
ON X.HeaderID = Y.HEADERID WHERE X.MO='123456' GROUP BY Y.PROBLEM)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

Thanks PHV! That worked great.

I tried to have patience but it took to long! -DW