Help please...

[Alex McDevitt]

Hi,

Now stay with me on....

Query1:
field1ID(key),field2ID, field3(Date)

field1ID=autonumber
field2ID=ref to another table(table ID field)
field3 = date

How do I return field1ID,field2ID,max(field3) without returning all fields(because I can't use 'Groupby' with field1)

Heres some data to explain better....
Field1 Field2 Field3
1 2 26/02/03 'Row1
2 2 01/01/03 'Row2
3 5 15/05/03 'Row3
4 5 02/06/03 'Row4

So, I want to return...

1 2 26/02/03 'Row1
4 5 02/06/03 'Row4

 

[barbarossaii]

Hi Alex,

these links could help:

http://www.access-programmers.co.uk/forums/showthread.php?threadid=57085&highlight=top+per+group

http://www.access-programmers.co.uk...5156&highlight=Top+invoices+for+each+customer


Just use the described solution to draw the "top 1" records from ur tables (i.e. the record with the maximum in one column).


HTH,
Barbarossa II