delete only first

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have a table: name, id, desc. I have duplicated rows. I
need to search based on name and id and delete the row at
the first occurrence but not the rest. How can I do that?
Can it be through query?
Thanks,
Jim.
 
Hello,
I have a table: name, id, desc. I have duplicated rows. I
need to search based on name and id and delete the row at
the first occurrence but not the rest. How can I do that?
Can it be through query?
Thanks,
Jim.
Click to expand...

Not easily, because Access tables have NO order. If you have multiple
rows with identical data in all fields, Access has absolutely no way
to distinguish which is which.

If you have some OTHER field that you can use to sort the records and
define which is "first", you can use a Delete query with a call to
DMin() as a criterion - but I'd need to know more about your table
structure to be specific.
 
I have chnaged the structure a little bit. Here is the case
I have two tables with the fields name,id,desc. The rows
might be duplicated in each table, I need to look at first
table and delete those rows in the second table. I could
not use unmatch query because it deletes all the rows in
the second table without checking if it is duplicated or
not. If first table contains a row once and it appears in
table2 twice, only first row should be deleted in table2,
not both.
Thanks,
Jim.
 
I have chnaged the structure a little bit. Here is the case
I have two tables with the fields name,id,desc. The rows
might be duplicated in each table, I need to look at first
table and delete those rows in the second table. I could
not use unmatch query because it deletes all the rows in
the second table without checking if it is duplicated or
not. If first table contains a row once and it appears in
table2 twice, only first row should be deleted in table2,
not both.
Click to expand...

Again:

THERE IS NO FIRST RECORD.

THERE IS NO SECOND RECORD.

A table is an unordered "Bucket" of data. If you dump a bunch of golf
balls in a bucket, which one is "first"?

If the records are identical, meaning that you have no Primary Key,
there is no way that Access can distinguish the records from one
another; in fact, you will not be able to run a Delete query to delete
one record and not the others.

You'll need to have a primary key. You have no choice!
 
Back
Top