group check box records

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

I have a report based on a query. How can I group records by the last time
(max date) a check box is true?
 
Sorting and grouping in a report is always accomplished by using the Sorting
and Grouping dialog in the report design view.
 
I haven't been able to figure out how to accomplish what I want or if it's
possible. My table has 15 yes/no fields to track tasks performed. All tasks
may or may not be performed each time. I would like to have a report that
shows the last time each task was done as in:
Date Field A, Field B, Field C, Field F, Field G, Field J, Field K
Date Field D, Field I
Date Field E
Date Field H
Is this possible and if so how would I go about it?
 
Your table structure is not normalized so it causes complications. If you
can't organize it so each task creates its own record then you can use a
normalizing union query.

Can you provide additional in formation about the primary key and actual
field names?
 
I'm tracking truck service records. There is a Repairs table with ServiceID,
Truck #, date, and mileage fields. The service table has RoutineServiceID,
OilChange, OilFilter, FuelFilter, TransmissionChecked, etc fields and is
related to the Repairs table in a 1 to Many relationship. It seemed at the
time these tasks were all related and the correct way to go. I'm not sure
how many records would have to be changed to correct this but if that's the
best solution it's probably possible.
 
What field in the service table relates to a what field in the repairs table?
Does the service table have a primary key? Are these your actual table names?
 
Sorry I left out the links. The repairs table is tblServiceDetails
withServiceDetailsID as the PK. The service table is tblRoutineService with
RoutineServiceID as the PK and also includes the field ServiceDetailsID as FK
to tblServiceDetails.
 
You can create a union query with SQL like:

SELECT RoutineServiceID, ServiceDetailsID, "OilChange" as Service
FROM tblRoutineService
WHERE OilChange = True
UNION ALL
SELECT RoutineServiceID, ServiceDetailsID, "OilFilter"
FROM tblRoutineService
WHERE OilFilter = True
UNION ALL
SELECT RoutineServiceID, ServiceDetailsID, "FuelFilter"
FROM tblRoutineService
WHERE FuelFilter = True
UNION ALL
SELECT RoutineServiceID, ServiceDetailsID, "TransmissionChecked"
FROM tblRoutineService
WHERE TransmissionChecked = True
UNION ALL
-- etc --

You can then join this query to other tables to get the dates.
This allows grouping by a vehicles and Service with the Max of a Service Date.
 
Back
Top