Help Create query for over due calibration?

[Luong]

Hello,

I have a tool calibration database and want to create an
over due tools report through a query. As the tools came
back from calibration, I enter in the database the
calibration info which include the nextduedate.
Inside the calibration table, there's a field called
ToolID (key value) for a specific tool.

When I peform a <Now() under the nextduedate column, it
shows all records that are pass today's date. I want a
report to show only 1 record of each tool with the latest
nextduedate that is passed today's date (< today date).

Any help/suggestion is greatly appreciated. Thanks

 

[Ken Snell [MVP]]

Something like this, perhaps:

SELECT ToolName, Max(NextDueDate)
FROM TableName
GROUP BY ToolName
HAVING Max(NextDueDate) < Date();

 

[Luong]

Ken,

Where do I put the expression you suggested below? THanks

 

[Ken Snell [MVP]]

I posted an SQL statement that defines a query. Open a new query, don't
select any tables, close the table window, go up to toolbar and click Query
View icon and select SQL view. Paste the statement in the window, and change
the names of the table (TableName) and the fields to the real names.