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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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 ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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, Developing Solutions 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.
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Saravanan AyyappanIT ProfessionalCommented:

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]
FROM T_Data  
       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:

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:
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, Developing Solutions 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.