Deleting duplicates records from access query

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
Asatoma SadgamayaAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
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.
Dale FyeOwner, Dev-Soln LLCCommented:
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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Then you must exclude the columns from the query, or use Max, Min, Last, or First on these.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Saravanan AyyappanIT ProfessionalCommented:
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
PatHartmanCommented:
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.
Asatoma SadgamayaAnalystAuthor Commented:
Thanks Dale, you have directed me to pin point the problem..
Dale FyeOwner, Dev-Soln LLCCommented:
glad I could help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.