How do I make a query return just 1 record

[Steve Haack]

I have a querydef that returns a set of records, sorted by the ID
(autonumber) field.

All I need is the first record in the set. How can I tell the query to give
me just that first record?

Thanks,
Steve

 

[anlu]

Hi Steve,

you can use the TOP x statement in the SQL, e.g.

SELECT TOP 1 field1, field2, ..., fieldn
FROM table
ORDER BY field1

Regards,
anlu

 

[fredg]

I have a querydef that returns a set of records, sorted by the ID
(autonumber) field.

All I need is the first record in the set. How can I tell the query to give
me just that first record?

Thanks,
Steve

Access does not store records in any particular order, so the 'first
record' can be rather elusive.
What determines what the first record in the set is?
The record ID number?
If the ID number is an AutoNumber field, AutoNumbers are not
necessarily in consecutive order.

With the above caveat understood, you can create a Top 1 query (sorted
Ascending on the [ID] field).

Select Top 1 YourTable.[ID] from YourTable Order by YourTable.[ID];

A better solution, if you want the 'first entered record' is to add an
[EntryDate] Date/Time datatype field to the table. Set it's default
value to =Now().
Upon a record being entered and saved, the current date and time of
entry will be added to the record.

Then use that field in the Top 1 query:
Select Top 1 YourTable.[EntryDate] from YourTable Order by
YourTable.[EntryDate];