complicated subtotal of field value (1 Viewer)

[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 "1" happened 2 times after the last event "0")

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 "1" happened once after the last event "0")

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 is appreciated.

 

[David R]

I managed this with two queries:
Query1: (Totals Query)
Source: tableEvents
TankID, Group By
DateField, Last
EventType, Where, Criteria: 0

Query2: (Totals Query)
Source: tableEvents, Query1
Draw a join line between TankID in Query1 to tankID in tableEvents.
Query1.TankID, Group By
tableEvents.EventType, Where, Criteria: 1
tableEvents.DateField, Where, Criteria >[Query1].[LastofDte]
Query1.LastofDateField, Group By
tableEvents.DateField, Count

Only drawback is that this query doesn't show any Tanks which have 0 usages since the last cleaning. You'd probably have to add a third query level to get a result on every tank.

HTH,
David R

[This message has been edited by David R (edited 04-22-2002).]

 

[ipupkin]

Thank you, David, it was helpful!

I still have to some extent similar question, but I've posted it as a new thread. http://www.access-programmers.co.uk/ubb/Forum3/HTML/003531.html