Query Records of Max Date

[messier]

I have a linked table of which another department updates on a monthly basis. Each month they will update the total number of customers.

Eg,.
31 Dec 2019 - 8 records
31 Jan 2020 - 10 records
29 Feb 2020 - 9 records
31 Mar 2020 - 12 records
(total rows in table 39)



I would like to pull only the 12 latest records. However, dynamically. I don't want to know what is the latest date, the query should automatically know that 31 Mar 2020 is the lasrest and pull the 12 records only.

I'm using the simple) query design and not writing codes.


Thanks in advance.

[pbaldy]

Put a descending sort on the date field, and in the query properties set the top values property to 12. In SQL view it will look like:

SELECT TOP 12 DateField, NumberField
FROM TableName
ORDER BY DateField DESC

[messier]

I would like not to count how many records. Ideally, my query should dynamically pull the number of records automatically based on latest date.

[pbaldy]

Ah, well, I might do it dynamically by perhaps:

SELECT DateField, NumberField
FROM TableName
WHERE DateField = (SELECT Max(DateField) FROM TableName)

[messier]

Thank you. Let me try this later. I think I saw it worked (had to leave my workstation).

However, can you share, why my criteria for DateField cannot be Max(DateField)?
I would like to understand the difference. Thank you!