Please help

  • Thread starter Thread starter cameron
  • Start date Start date
C

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
 
cameron said:
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).
Click to expand...


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.
 
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).
Click to expand...

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
 
Back
Top