scavenging records not simply by date (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 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.

 

[David R]

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.

 

[ipupkin]

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?

 

[ipupkin]

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).]

 

[ipupkin]

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);