scavenging records not simply by date

[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.

 

[petersdaniel]

You can quickly do this as a two part query structure using the access query designer. For example purposes, I called my demo table TANK with the fields you described above...

First Query finds the latest occurence of Cleaning for a given tank. I saved this query as CleaningDates

SELECT Tank.Tank, Max(Tank.LastMaint  ) AS CleaningDate
FROM Tank
WHERE (((Tank.EventID)=0))
GROUP BY Tank.Tank;

The Delete Query calls the Previous Query which I named CleaningDates

DELETE [Tank].[Tank], [Tank].[LastMaint  ], [Tank].[EventID]
FROM Tank INNER JOIN CleaningDates ON [Tank].[Tank]=[CleaningDates].[Tank]
WHERE (([Tank]![LastMaint]<[CleaningDates]![CleaningDate]));

Looks like it works given your data. Good Luck
petersdaniel@hotmail.com
&quot;If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut.&quot; --Albert Einstein

 

[ipupkin]

This is exectly what I've tried.

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

But attempting to run DELETE query:

DELETE [Tank].[Tank], [Tank].[LastMaint ], [Tank].[EventID]
FROM Tank INNER JOIN CleaningDates ON [Tank].[Tank]=[CleaningDates].[Tank]
WHERE (([Tank]![LastMaint]<[CleaningDates]![CleaningDate]));

I receive

&quot;Specify the table containing the records you want to delete&quot;

If I change the query a little bit:

DELETE [Tank].*
FROM Tank INNER JOIN CleaningDates ON [Tank].[Tank]=[CleaningDates].[Tank]
WHERE (([Tank]![LastMaint]<[CleaningDates]![CleaningDate]));

I receive

&quot;Operation must use an updatable query&quot;

Is there something I've misunderstood?

 

[ipupkin]

Oops, it should be
&quot;DELETE DISTINCTROW&quot;
to work.
Thanks for replies and Best Regards!

 

[ipupkin]

This works as well. Is there any pitfalls here?

DELETE *
FROM Tank t
WHERE EXISTS(
SELECT * from Tank tt
where tt.tank=t.tank
and tt.eventid=0
and tt.LastMaint>t.LastMaint
);

 

[petersdaniel]

Looks like it will plus it is a little more compliant with ANSI SQL standard method. I generally don't recommend EXISTS statements unless I know the user is able to code SQL and know how to build sub selects. Many of the people seeking information here tend to not know the differnece between a table or a query, so I keep things a little more &quot;elementary&quot;

All I recommend when you are trying to test out these theories is that you produce a backup table. Generally speaking, I do a make table query that produces a zz_tablename and copies the data over. I then work with that test data untill I get my results. Then if it is ready, you can quickly edit the SQL by removing the zz_ prefix off the table name. petersdaniel@hotmail.com
&quot;If A equals success, then the formula is: A=X+Y+Z. X is work. Y is play. Z is keep your mouth shut.&quot; --Albert Einstein