I am trying to make a query that will extract the most recent record entered into a table.
I have three fields in my query:
RecordID (autonumber primary key of table)
Employee Number
Date
the query works but gives me multiple records in the dynaset...I only want one record.

I need to know what criteria to use to extract the highest(furthest from zero) number from the RecordID field.

Thanks in advance..

SELECT TOP 1...
ORDER BY [DateField] DESC, etc

You could also use a Totals query and search out the Max [DateField] However to do this you can only include the date/time field, then use that result in another query to find the Employee who did it, etc.

David R

[This message has been edited by David R (edited 04-11-2002).]

You can do this with nested queries:

Query1:
Select EmployeeNum, Max(SomeDate) As MaxDate
From YourTable
Group By EmployeeNum;

Query2:
Select q.EmployeeNum, q.MaxDate, t.RecordId
From Query1 as q Inner Join YourTable as t ON q.MaxDate = t.SomeDate AND q.EmployeeNum = t.EmployeeNum;

If there are multiple RecordId's for any given EmployeeNum and SomeDate combination, Query2 will return multiple rows.

The dynaset I have is working except that it returns multiple records. I just want a way to select only the top record...Should this not be easy?
please pardon my ignorance.

thanks again
p.

It's OK
I've figured it out...Had to change the Query to Select Top 1 in the SQL view.
seems to work.
thanks for the help.
This forum is very useful!
p