complicated subtotal of field value

[ipupkin]

Hello, All
My database should help to track usage of some kind of tanks. There TWO events that can happen to a tank and that we want to track - event "0" (cleaning) and event "1" (using).
The aim is to count how many times was every tank used after it was cleaned.
All events are entered into EventLog table:

[Tank #] [date] [event type]
--------------------------------
501 01/01/2002 0
501 02/01/2002 1
501 03/01/2002 1
501 04/01/2002 0 <-last cleaning
501 05/01/2002 1 <-using after cleaning
501 06/01/2002 1 <-using after cleaning
(for tank 501, event &quot;1&quot; happened 2 times after the last event &quot;0&quot

702 01/02/2002 0
702 02/02/2002 1
702 03/02/2002 1
702 04/02/2002 0 <-last cleaning
702 05/02/2002 1 <-using after cleaning
(for tank 501, event &quot;1&quot; happened once after the last event &quot;0&quot

Can I build a query resulting in such dataset:

[tank #] [last cleaning date] [used .. times]
-----------------------------------------------
501 04/01/2002 2
702 04/02/2002 1

Any idea appreciated.

 

[dalchri]

I recommend the following query:

SELECT [Tank #], COUNT(*) AS [Used Times]
FROM (
SELECT [Tank #], MAX([Date]) AS LastCleaned
FROM YourTable
WHERE [Event Type]=0
GROUP BY [Tank #]
) Cleanings INNER JOIN YourTable
ON Cleanings.[Tank #]=YourTable.[Tank #] And Cleanings.LastCleaned<YourTable.[Date]

The subquery determines when each tank was last cleaned. Joining the subquery back onto the original table leaves only the records after the last cleaning by Tank #. Counting these remaining records by Tank # gives you the number of times a tank was used since its last cleaning.

Note, this query will not produce a count for tanks that have never been cleaned!

 

[dalchri]

Shoot, I forgot a group by clause. I meant to type this:

SELECT YourTable.[Tank #], COUNT(*) AS [Used Times]
FROM (
SELECT [Tank #], MAX([Date]) AS LastCleaned
FROM YourTable
WHERE [Event Type]=0
GROUP BY [Tank #]
) Cleanings INNER JOIN YourTable
ON Cleanings.[Tank #]=YourTable.[Tank #] And Cleanings.LastCleaned<YourTable.[Date]
GROUP BY YourTable.[Tank #]

 

[ipupkin]

Thank you so much, dalchri, you've saved my hours!

I have to some extent similar question, but I've posted it as a new thread.

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/701/qid/257660