Report advice

Question

We have a table that had all of our trucks. All trucks are supposed to be inspected each day and the results logged. We import a report showing which trucks have had their inspection each day. The number of inspections and the number of days that truck has been driven populate a report. I have a report that has all of the inspections done on each truck and I need one that only shows the most recent inspection whether it was yesterday or last month or last year. I'm not sure how to go about getting that latest inspection without having them all. Thank you for the assistance.

------------------------------
Steve Pearson
------------------------------

LauraThacker · Answer

Question: Are your Truck Inspections records connected to a single-parent, most likely a "Truck" record which identifies one truck from another? If you do not, you will need one because you want to be able to identify which of "many" truck inspections is the last-one for a given Truck (even if you plan to report it in your Truck Inspections table).

When you have your parent-table (most likely "Trucks"); you want to do the following.

1. Build a summary field in the relationship to Summarize the Maximum [Record ID#]. You can call this [Max Truck Inspection RID] (it does not need to be reportable)
2. Build a lookup field in the relationship on the right-side of the relationship (Truck Inspections) and lookup the value [Max Truck Inspection RID] (it does not need to be reportable). So now, you are displaying the record ID# of the latest Truck Inspection for a given truck across all the child-Truck Inspection records.
3. Build a formula-checkbox in the Truck Inspections table. Call it [Latest Truck Inspection]. Use the following formula:

[Record ID#] = [Max Truck Inspection RID]

This will auto-evaluate to "true" when the criteria is met.

4. Now, in your report, add a filter where the [Latest Truck Inspection] is equal to checked

This will now filter out Truck Inspections which are not the "latest one". Obviously any other filters you apply may filter out Truck Inspections that meet other criteria. A good way to "test" this; is to display a List All report with the column [Latest Truck Inspection] and visually check that the latest imported one is marked as checked; before applying the filter where you may not be displaying that column.

------------------------------
Laura Thacker (IDS)
laura@intelligentdbs.com
(626) 771 0454
------------------------------

Sign up

Login to the community