Query: Remove all but the first occurance

N

Noozer

Looking for a query that will remove any records, EXCEPT the first occurance
where there are duplicate values in certain fields.

e.g.

Customer database. If there are records that are duplicated in the
customername, telephone and postal fields, I want to delete all those
records except for the first. How about delete all those records except for
the last occurance?

Thanks!
 
D

Duane Hookom

Records are like marbles in a bag. There is no First and no Last unless you
have a unique value in a field that identifies your perception of First and
Last.
 
N

Noozer

They don't all come in together..

One of the records needs to be first and one needs to be last in a specific
query.

SELECT customername, telephone, postal FROM mytable ORDER BY customername,
telephone, postal;
 
D

Duane Hookom

Assuming your record information is written on marbles in a bag. If you have
the identical information written on two or more records, then there is no
reliable method of determining first or last in a query.

If you had a timestamp stored in one of the fields or possibly an Autonumber
field, you could use this to determine most recently added.
 
N

Noozer

Duane Hookom said:
Assuming your record information is written on marbles in a bag. If you
have
the identical information written on two or more records, then there is no
reliable method of determining first or last in a query.

If you had a timestamp stored in one of the fields or possibly an
Autonumber
field, you could use this to determine most recently added.
Click to expand...

But I don't want the most recently added, or the first added.

I want the record that Access is pointing to directly after executing a
query.

Alternately, I'm interested in the changes need to do the same task, except
not deleting the final record that Access will find for a specific query.

Dates that the records were created on, or edited, or last used have no
bearing on the results of my task.
 
R

RealGomer

It actually seems you may not understand his simple question. I have the same
need for a query to identify and then delete duplicate records.
I believe what Noozer is asking is quite simple and straightforward.
Each record has some unique identifier, such as an account number. In the
database, account 123456 shows up four times. He wants to identify instances
second, third, fourth, fifth, and sixth, leaving only instance first.
To put it another way, Macy's tracks each customer's purchases by
department. So customer 123456 buys something in Dept. 25, then buys
something in Dept. 46, then in Dept. 58. Macy's wants to see if customer
123456 was in their store recently. So, they only want to see one, a single,
a solitary, a unique, one and one only, instance of account 123456.
Which in my mind is what the Find Duplicates query should so, return only
those records that repeat, not EVERY instance.
 
Top