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 delete old events from EventLog table. Event is old, if it had happened before last cleaning.

[Tank #] [date] [event type]
--------------------------------
501 01/01/2002 0 <-old record, must be deleted
501 02/01/2002 1 <-old record, must be deleted
501 03/01/2002 1 <-old record, must be deleted
501 04/01/2002 0 <-last cleaning, preserve it
501 05/01/2002 1 <-preserve it
501 06/01/2002 1 <-preserve it

702 01/02/2002 0 <-old record, must be deleted
702 02/02/2002 1 <-old record, must be deleted
702 03/02/2002 1 <-old record, must be deleted
702 04/02/2002 0 <-last cleaning, preserve it
702 05/02/2002 1 <-preserve it

Any idea would be appreciated.

Look at the steps of the previous post I made on your related topic. You should be able to use the Totals Query functions to get the information you want here as well.

Well, this is exactly what I've tried.

The first query named CleaningDates uses aggregation function to get last cleaning date for each tank:

SELECT TankID, Max([DateField]) AS LastofDateField
FROM tableEvents
WHERE (((EventType)=0))
GROUP BY tableEvents.TankID;

The SELECT query which shows records for deletion works quite OK.

But when attempting to run identical DELETE query:

DELETE [tableEvents].[TankID], [tableEvents].[DateField], [tableEvents].[EventType]
FROM Tank INNER JOIN CleaningDates ON [tableEvents].[TankID]=[CleaningDates].[TankID]
WHERE (([tableEvents]![DateField] < [CleaningDates]![LastofDateField]));

I receive

"Specify the table containing the records you want to delete"

If I change the query a little bit:

DELETE tableEvents.*
FROM Tank INNER JOIN CleaningDates ON [tableEvents].[TankID]=[CleaningDates].[TankID]
WHERE (([tableEvents]![DateField] < [CleaningDates]![LastofDateField]));

I receive

"Operation must use an updatable query"

Is there something I've misunderstood?

Oops, it should be
"DELETE DISTINCTROW"
to work.

Thanks for replies and Best Regards!

[This message has been edited by ipupkin (edited 04-24-2002).]

Hello, All

And that's another way of scavenging old events. It requires single query only and seems to be more evident.

DELETE *
FROM tableEvents t
WHERE EXISTS(
SELECT * from tableEvents tt
where tt.TankID=t.TankID
and tt.EventType=0
and tt.DateField>t.DateField);