Filter Report with Most Recent Dates

[ahuffman24]

Hi,

I have a database that tracks the calibration of tools for my company. I have two tables, one that tracks the tool itself, and one that tracks the calibration of each tool. I have them linked with a 1 to many relationship so that multiple calibrations can be recorded for each individual tool. I also have a report that shows when the tools need calibrated, and it is filtered based on the desired calibration date; the user enters the desired calibration date, and the report filters lists each tool with a calibration due date equal to or older than the desired calibration date. The filter works, but the issue is that tools that have been calibrated more than once show the most recent due date as well as previous due dates. Is there a way to set the filter so that it only shows the most recent due date and not all of the due dates for each tool?

Thank you,



Alison

[Bulzie]

Can you add a "Calibration Complete" check box field to mark past ones so you can exclude those on your report? Or on the report query, select the Totals button and maybe try GroupBy on the ToolID field and Max or Last on CalibrationDate?

[ahuffman24]

@Bulzie, I tried doing as you said, and I am getting an error. It seems that if the Totals button is selected, each field must have something selected from the dropdown menu of the Totals row. Should I select something in the Totals row for each of the other fields? Here is a screenshot of my query and the error I am getting:






Thank you,

Alison

[Bulzie]

Try this, create a query on the ToolCalibration table and just include ID and Last_Cal_Date and do the totals GroupBy on ID and Last on Last_Cal_Date so you get only the last record per ID. Create 2nd query with first query linked to the Tool Calibration table linking by ID and selecting all the fields from the ToolCalibration table. Now this 2nd query should have just the 1 record for each tool but includes all the fields. Now add this 2nd query to your example above inplace of the ToolCalibration table and see if that works.

[ahuffman24]

Just to clarify, the first query includes the ID and LastCalDate fields from the ToolCalibration table, and the second query includes all fields of the ToolCalibration table? And then I link them together?

[Dave14867]

I have a similar database that I developed for the same purpose, I have reports built in so I can run a report for a given month and see all tools needing calibration for that month. If you can zip up a copy of your database, I'll be happy to assist you.

[ahuffman24]

That would be great! Thank you so much.

[Bulzie]

Yes basically you are nesting the queries. Query1 is just that ToolCalibration table and those 2 fields to get you the Last date for each tool ID. Query2 is Query1 linked to ToolCalibration table by ID which should now show unique records per tool but gives you all the fields from the table. Query3 is the one you have up top but replace the ToolCalibration table with Query2.

But also check out what Dave may have too, sounds like he has done something similar.

[Dave14867]

I will take a look and get back to you, It looks like your getting all previous calibrations, correct, sorry if I am being redundant, I just want to fully understand what your looking for.

[Dave14867]

Alison,

Take a look at the attached file. On the "Intro" form is a new Command Button, select that, enter the beginning and end dates and a new report will open showing only the tools to be calibrated between the dates entered.

I hope this helps. If you need other assistance please let me know.Dave-GageCalibrationRecords 1-4-20.zip

[ahuffman24]

@Dave, thank you so much! I will try it out and let you know how it works. I really appreciate your help!

[ahuffman24]

@Dave, your design works, but my boss is concerned that they may not specify a beginning date that is back far enough to cover the tool that needs calibrated. For example, if I want to check tools that are due on 1/7/20 and I specify the beginning date as 1/7/19, that would only cover tools that were calibrated exactly a year ago. Some tools may have longer calibration intervals, and in that case, specifying the dates wouldn't cover those tools. It would be a pain for them to have to calibrate the exact beginning date based on the calibration interval for each tool. What would be ideal is if the user enters the current date and Access pulls the most recent previous calibration dates on or before the current date. Right now, my database has no way to differentiate between the most recent date and an old date; it just pulls all the previous calibration dates and puts them on the report. Is there any way that Access could pull the most recent date from the table and only display that date on the report?

[Dave14867]

Allison,

Currently it takes the next Cal Due Date and uses that, based on the interval. If you want I can see about putting the previous Cal date on the report but I don't see a reason for it, but let ma know.

[Dave14867]

Alison,

Attached file has last date calibrated.

Dave-GageCalibrationRecords 1-8-20.zip