Deletion of rows based on Conditions

[Jean]

Hi,

This is a sample of the data i am working on:

2012088552 300 04/27/2007 16:53:14
2012091284 300 04/28/2007 19:41:34
2012421319 300 04/25/2007 11:52:00
2012817722 300 04/28/2007 12:49:36
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59
I want to keep all data where the date is 04/27/2007 and delete all the
other rows
EXCEPT the ones that have the same value in the first column as the value
where the date is 04/27/2007. This means the result would be:

2012088552 300 04/27/2007 16:53:14
2013558438 300 04/25/2007 12:15:59
2013558438 300 04/27/2007 17:06:59

Any help is greatly appreciated!

 

[Jerry Whittle]

DELETE
FROM TblJean
WHERE TblJean.Field1 In (SELECT TblJean.Field1
FROM TblJean
WHERE Fix([Field3])=#4/27/2007#);

I'm assuming that Field3 includes both the date and time. If Field3 has only
the date, change the WHERE clause to:

WHERE [Field3]=#4/27/2007#);

 

[John Spencer]

To display only the records you want you would use

SELECT Table.*
FROM Table
WHERE Table.Column1 IN
(SELECT T.Column1
FROM Table as T
WHERE DateColumn >= #04/27/2007# and
DateColumn < #04/28/2007#)

IF you really want to PERMANENTLY delete all the OTHER records, you can use
the above query to identify the records to not delete
BACKUP your data first, there is no UNDO other than a backup copy of the
data.

DELETE
FROM Table
WHERE Table.Column1 NOT IN (
SELECT Table.Column1
FROM Table
WHERE Table.Column1 IN
(SELECT T.Column1
FROM Table as T
WHERE DateColumn >= #04/27/2007# and
DateColumn < #04/28/2007#))

That will be slow if you have a large number of records.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

Hi John,

Your solution should work faster if DateColumn is indexed and there's a lot
of records.