SQL query search by ID and get another attribute of the record

I’m working on a DB where I need to search MAX(ID), where ID is an autonumber, (basically the most recent record/last record) and then when found I want to get the receipt number. e.g.

receipt table

ID         DATE        Number
2          ###          1423
3          ###          1452
4          ###          1401

I’m currently retrieving Max(Number) but due to program requirement, most recent is not always the highest. So I want to retrieve the highest ID value then get the value of Number to increment. I tried nested queries but no results. Although I’m familiar with VBA, I’m not as good with complex queries and optimization.

Answer

You could use DMax to get the maximum ID value from your table. For example, in the Immediate window …

? DMax("ID", "YourTable")

You can use that expression in a query to ask for the row with the highest ID value.

SELECT y.ID, y.DATE, y.Number
FROM YourTable AS y
WHERE y.ID=DMax('ID', 'YourTable');

But DMax is Access-specific. If you want an approach which is portable to other databases you can use a subquery to retrieve the highest ID value.

SELECT y.ID, y.DATE, y.Number
FROM YourTable AS y
WHERE y.ID=(SELECT Max(ID) FROM YourTable);