Deleting duplicates records from access query

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
My access query is return lots of duplicates in it. I tried with group by and union, but because of huge number of columns, I am not able to delete those.

please haelp

Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
for the duplicates, you need to determine whether:

1. it's from raw data
2. it's the result of joining statement

by then to determine how you would like to "delete" the duplicates.

more details are needed here before something can be proposed.
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
I'm inclined to think this is the result of a join which is not joined on all of the linking fields, or where one table has multiple records for each record in another table (as in a 'details' table).

Can you show us the query design? either as a picture of the query grid with tables and joins or as a sql statement?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then you must exclude the columns from the query, or use Max, Min, Last, or First on these.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Saravanan AyyappanIT Professional

Commented:
Hi,


Here is an example for removing duplicates. plug in the fields that constitute a dup. Be sure to make a copy of the table first in case something goes horribly wrong. [grin]
DELETE *  
FROM T_Data  
WHERE (SELECT  Count(*)
       FROM T_Data AS T  
       WHERE Nz(T.F1, "") =  Nz(T_Data.F1, "")
       AND Nz(T.F2, "") =  Nz(T_Data.F2, "")
       AND Nz(T.F3, "") =  Nz(T_Data.F3, "")
       AND Nz(T.F4, "") =  Nz(T_Data.F4, "")  
       AND T.ID <= T_Data.ID) > 1;

=========


Another example which would delete all rows bar one with the same LastName value, retaining the row with the highest (MAX) value of the key (ContactID) per last name:

DELETE *
FROM Contacts AS C1
WHERE ContactID <>
     (SELECT MAX(ContactID)
      FROM Contacts AS C2
      WHERE C2.LastName = C1.LastName);

If the table does not currently have a candidate key, simply add an autonumber column to act as the key.

========


You may also visit this page for reference:  https://support.office.com/en-us/article/delete-duplicate-records-with-a-query-4e6c3183-689c-439d-9062-326c85d80a83
Distinguished Expert 2017

Commented:
To be clear - you cannot delete duplicates from a query.  Delete is a physical operation.  You can delete duplicates from tables.

As the others have said, the problem is almost certainly with your join or because you are including detail IDs instead of not selecting them.

Posting the database with the actual data and telling us which query is causing the problem will be the most expedient way to resolve the issue.  If you can't do that, we can still help you but it will take time to slog through all the potential issues.

Please start by posting the actual SQL string.  Also include a picture of your relationship diagram.  Make sure we can see the primary keys and how the tables are related.

A small set of data from each table in a spreadsheet would allow us to replicate your problem.

Author

Commented:
Thanks Dale, you have directed me to pin point the problem..
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad I could help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial