RePost Summing Data In A Report

[Guest]

I posted this back on the 16th October with no response, I hope someone can
help this time.
I am trying to write a report where records are selected for pieces of
equipment. In a linked table I record the date and amount of oil added to
each piece of equipment. If the oil is completely changed out I record the
date of the oil change, the amount of oil added and tick a check box to
indicate that the oil was changed.
I have a report that shows the details for each piece of equipment. I need
to add a sub report to sum the oil added from the last time the oil was
changed, not including the changed amount.
How do I Sum the oil added after the last oil charge, not including the
quantity of oil used in the change?
The fields in the linked table are:
Equip ID (number), DateTopUp (date), OilAdded (number), OilChanged,
(Checkbox)

All help is appreciated

Nick

 

[John Spencer]

You will need a query that looks something like the one below for the source
of the sub-report. Since it uses a non-equi join you won't be able to
construct it using the design view, but must use the SQL view


SELECT EquipID, Sum(OilAdded) as OilSinceLastChange
FROM YourTable INNER JOIN
(SELECT EquipID, Max(DateTopUP) as LastChange
FROM YourTable
WHERE OilChanged = True
GROUP BY EquipID) as Q
ON YourTable.EquipID = Q.EquipID
AND YourTable.DateTopUp > Q.LastChange



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..