Reports - How to Exclude Old Data? - Save the Trees!

[Guest]

Hi,

I track 60 vehicles. Each one has a varying number of mechanical records
associated with it.

The report, printed daily, is organized by vehicle. But some vehicles have
dozens of old mechanical records that don't need to be printed out. How do I
get the Report to print out just the last 2 or 3 records? (Without deleting
the old records.)

This would save many trees! Thanks.

 

[Guest]

Check this link on "TOP n records per group"

http://allenbrowne.com/subquery-01.html

 

[Guest]

Hm, interesting. So, the solution is to use queries and learn SQL. I think
I see where to go with this. I now have an afternoon project!

The trees thank you.

 

[Guest]

Update: it doesn't work. Learned SQL queries, subqueries, and aliasing,
very carefully, but no go. Goodbye, trees.

 

[Guest]

Can you post the SQL you tried, so I can see the table name a fields that are
involved.
See if I can help, it's for the trees.

 

[Guest]

Thanks, perhaps later (I tried several); I think it's time for me to go to
the library and make a more thorough study of SQL, its refinements, and its
fundamentals, instead of shortcut-hacking-and-begging-for-help. Possibly
there is a data type problem. A weekend project.

 

[Doug Munich]

This probably isn't the most elegant way, but here's one way. Suppose
you have a vehicle table tblVehicles with fields ID and VehicleNumber, and
you have a maintenance table tblMaintenance with fields ID, VehicleID (which
relates to ID in tblVehicles), Date, and Comment. I created a query for the
report which was like

SELECT tblVehicles.VehicleNumber, tblMaintenance.Date,
tblMaintenance.Comment, 1 AS [Counter]
FROM tblVehicles INNER JOIN tblMaintenance ON tblVehicles.ID =
tblMaintenance.VehicleID;

so this gives you all the maintenance records with a constant 1 on each
record.

I then set up a basic report with grouping by VehicleNumber and the rest
of the fields in the detail section sorted according to Date descending.
Then I went to design view clicked on the Counter field, opened the
properties and set "Running Sum" to "over group" on the Data tab. (When you
look at the report at this point you'll get all the maintenance records with
a running counter as the last column.)

Then I clicked on the detail section, opened the properties and under
Events clicked on the "On Format" event, code builder and put the following
line in the event handler

If Counter > 2 Then Cancel = True

Thus for each vehicle I only get the two most recent maintenance records
(if there are two or more).

Doug Munich