Please help

[cameron]

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

 

[Marshall Barton]

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).

Assuming you have a date of calibration field or a
calibration completed field, then just put Is Null in its
criteria. This way, you'll only see records that have not
been calibrated and are late for the next one.

 

[John Vinson]

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).

You should be able to use a bit more sophisticated criterion: a
Subquery. On the criteria line under nextduedate put

=(SELECT Max([nextduedate]) FROM yourtable AS X WHERE X.ToolID =
yourtable.ToolID)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps