Delete duplicate records from a query

J

Jean

Hi and thanks for any help, I created a query based on 2 tables and for some
reason i have duplicate rows. What is the cleanest way to delete duplicate
rows?

is it to place "distinctrow" after the SELECT?

Thanks a lot!
 
J

John W. Vinson

Hi and thanks for any help, I created a query based on 2 tables and for some
reason i have duplicate rows. What is the cleanest way to delete duplicate
rows?

is it to place "distinctrow" after the SELECT?

Thanks a lot!
Click to expand...

Be careful about "DELETE" in this context. A Query has no independent
existance. Deleting a row from a Query will delete it (permanently and
irrevokably) from the underlying Table.

What exactly are you seeing? If you have two tables in a one to many
relationship, a Query joining the two tables will show as many repeats of the
record in the "one" table as there are matching records in the "many". This
isn't duplication, or redundancy; it's exactly how such queries are designed
and intended to work! Are the records in fact duplicate in all fields?

You can set the Unique Values property of a query to show each duplicate
record only once (based on the fields that you show as visible in the query);
this query will not be updateable though.

John W. Vinson [MVP]
 
J

John W. Vinson

Hi John thanks for your reply, how can i set the unique values ? THANKS!
Click to expand...

Open the Query in design view. Right mouseclick the grey background of the
tables, and select Properties from the dropdown (or use the View menu item).
Change the Unique Values property to Yes.

Note that if you have any fields from the "many" side table selected, you'll
still get all unique records for those fields, even if they are duplicates in
the "one" table.

Could you describe the data you have, and what you EXPECT to see? Maybe you'ld
be better served by a Form based on the "one" side table with a Subform based
on the "many".

John W. Vinson [MVP]
 
J

Jean

Hey thanks a lot! that works, i tried unique records but didn't work!
Example:

ID Account ID Contract ID Payment N0 Net Cash

1 22 278 28288 $30
1 22 278 28288 $30
2 23 278 2899 $20
3 23 278 2900 $15

Since i need to calculate total net cash, i want to get rid of duplicate
rows( just keep one) so i would have a total of $65 instead of $95 and it
looks like changing Unique Values to Yes work!

Thanks!
 
J

John W. Vinson

Hey thanks a lot! that works, i tried unique records but didn't work!
Example:

ID Account ID Contract ID Payment N0 Net Cash

1 22 278 28288 $30
1 22 278 28288 $30
2 23 278 2899 $20
3 23 278 2900 $15

Since i need to calculate total net cash, i want to get rid of duplicate
rows( just keep one) so i would have a total of $65 instead of $95 and it
looks like changing Unique Values to Yes work!
Click to expand...

It sounds like you have corrupt data in your table. Do you in fact have two
records stored in your payments table for Payment No 28288? You shouldn't!
That table should have a Primary Key to prevent two of the same payment from
being inserted in the first place!

John W. Vinson [MVP]
 
J

Jean

Actually this is a query and not a table, so most likely this is ok to
happen, can you please confirm?

Thanks!
 
J

John W. Vinson

Actually this is a query and not a table, so most likely this is ok to
happen, can you please confirm?
Click to expand...

No, I cannot confirm anything; *YOU* have the database in front of you, you
can see the query, you can see the table. I cannot.

I have no idea what you mean by "this is ok to happen" either.

John W. Vinson [MVP]
 
J

Jean

true but i think it is normal :) Thanks!

John W. Vinson said:
No, I cannot confirm anything; *YOU* have the database in front of you, you
can see the query, you can see the table. I cannot.

I have no idea what you mean by "this is ok to happen" either.

John W. Vinson [MVP]
Click to expand...
 
S

scubadiver

I have just come across your thread. Did you sort it out because I am
wondering whether you understand database normalisation (no offence you
understand!)
 
Top