Prev: Query, linked tables
Next: Append only new records/entire record makes unique identifier
From: Iram on 12 May 2010 11:57
Hello,
I have a query that finds all records with a particular "Case#". I changed
the query to do totals and grouped by Case#'s. I have another field called
"PrisonID" in which I want to see all records except for the Max (except the
highest number PrisonID). Eventually I need to change this query into a
delete query so that it automatically deletes all duplicate records with the
lowest PrisonID.
Can you help me figure this out?

Thanks.
Iram/mcp

From: John Spencer on 12 May 2010 15:53
Are you saying you want to delete all records for each case EXCEPT for the one
with maximum PrisonID?

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

The following could be slow

DELETE
FROM SomeTable
WHERE PrisonID <>
(SELECT MAX(PrisonID) FROM SomeTable as T
WHERE T.[Case#] = SomeTable.[Case#])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Iram wrote:
> Hello,
> I have a query that finds all records with a particular "Case#". I changed
> the query to do totals and grouped by Case#'s. I have another field called
> "PrisonID" in which I want to see all records except for the Max (except the
> highest number PrisonID). Eventually I need to change this query into a
> delete query so that it automatically deletes all duplicate records with the
> lowest PrisonID.
> Can you help me figure this out?
>
> Thanks.
> Iram/mcp
>
 | 
Pages: 1
Prev: Query, linked tables
Next: Append only new records/entire record makes unique identifier