removing a record if one field is duplicated

[reddwarf]

I have a UNION query which looks at three tables.

it pulls

REF - ACTIVITY_NEEDED - DATE NEEDED - TEAM - OWNER and some other fields

What i want ...

if a REF has three acitivities it only shows the OLDEST ONE according to DATE NEEDED.

is this possible ? any one got any ideas ?

eg


REF - ACTIVITY_NEEDED - DATE NEEDED - TEAM - OWNER
1 ACTION CLAIM 01/01/10 2 SLAD
1 CLOSE CLAIM 01/11/10 2 SLAD
2 VIEW CLAIM 05/11/10 5 PETE
3 ACTION CLAIM 01/11/10 6 DAN
1 PAY PEOPLE 15/10/10 2 SLAD

from the above, i would want the query only to return


REF - ACTIVITY_NEEDED - DATE NEEDED - TEAM - OWNER
2 VIEW CLAIM 05/11/10 5 PETE
3 ACTION CLAIM 01/11/10 6 DAN
1 PAY PEOPLE 15/10/10 2 SLAD

does that makes sense >?

 

[vbaInet]

Create a Totals Query and use Max as the Date aggregate.