Ask a new question

Remove duplicates in a query

I have two columns...

The first columns have machine names and the second column is the user who owns that machines

I would like to run a query that deletes all the dups...keeping only one of them.
So if you have four machine names and four of the same user names. I want it to find all the machine names and delete all except for one.

How do I do that?

I did a query wizard "find match" but it wont keep one of each.
Does the table have a primary key, for example an AutoNumber field?
---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Yes it does. Auto Number. I tired basing it off that but I'm lost somewhere. You not going to believe this but I was a Access developer for 10 years. Been out of the loop for the last 8. How can I forget something this basic?
Thanks for your reply!
ramramram

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Try a query like this, where

Table1 = table name

ID = primary key

MachineName, Owner = fields on which you're looking for duplicates.

 

DELETE Table1.ID
FROM Table1
WHERE Table1.ID Not In (SELECT Min(ID) FROM Table1 GROUP BY MachineName, Owner)

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

This query gives me option boxes to put in the value before it displays.
I need something that will strip all dups leaving only one of each machine name and user.
Am I doing something wrong?
Thank you
ramramram

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You must of course use the exact names of your table and the fields in it, you can't just try to run the query as I posted it. If you have names with spaces or punctuation, enclose the name in square brackets [ ], e.g.

 

DELETE [My Table].[Key Field]
FROM [My Table]
WHERE [My Table].[Key Field] Not In (SELECT Min([Key Field]) FROM [My Table] GROUP BY [Machine Name], [Machine Owner])

---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Right I changed all of the field names. Still get the same dialog boxes.
let me try something else.
Thank you
ramramram

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

You must have a mistake somewhere - check the spelling very carefully.
---
Best wishes, HansV
https://www.eileenslounge.com

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Duh! I was naming the query not the table. Back to basics!  Thank you so much!
ramramram

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated October 5, 2021 Views 220 Applies to: