Anyone?

Does it matter which one you keep?

Untested but could get the max of each group
Store those IDs, then create another query excluding them and DELETE what is left:

SELECT id, customer, MAX(total) FROM  purchases GROUP BY id, customer
ORDER BY total DESC;

Thanks Alex. Doesn't Matter Which one is kept, as long as 1 instance remains like the 2nd pic
("A Potiwal" isn't actually a duplicate because the gross is different).

Tried your method supplementing "customer" with ClientName and "total" with ClientID

SELECT OrderT.id, OrderT.clientname, Max(OrderT.ClientID) AS MaxOfClientID
FROM OrderT
GROUP BY OrderT.id, OrderT.clientname;

Not sure where to go from here

Ah nuts. I thought i was on to something good - tried using Ricks idea of importing and setting "ClientID" as Indexed No Duplicates but instead of allowing 1 instance of each and failing the rest, it just outright refused :(

Good news is i found an alternative - using Excels remove duplicates function. I just need to use access to create the "ClientID" in the first place.

I would love to see a way of being able to do this in access. A simple, remove duplicates process without the normalization and table splitting in the Normalizing Data which made it difficult to follow.

Thanks anyway mate

Depends if you mind doing it in stages.
You could use that as a SubQuery to then filter out the original.
Or a MAKE TABLE with just the ID column then use that to filter out to the ones you want to delete.

I'm walking away from it for now. I've gone down the rabbit hole and come out in China! Now that i know how to do part B, i've forgotten how to do part A. I swear there is something wrong with my brain! Frustrating.

I'll come back to it next week and start from fresh

Just as a side note the MAX would usually be something that is different, so ClientID being the same wouldn't work.

Dan,

If you wanted to go down the programming route you could delete all duplicates with a Recordset loop.
In my simple example (see screenshot) I only did 2 fields but should be easily modified for your needs.

Alex Cheers, makes sense

Kevin - That looks exactly what i'm looking for, though i'm only up to expert 7 so haven't done anything with recordsets yet, though i do know the principal due to other videos. This will be a good idea for a tool i can keep in case i get any more de-dupeing to do.

Thanks guys

Setup a 2nd table and create a composite key on those two fields. Append query your records into the new table. Voila. No programming required.

I always overlook the simple solutions.

I usually do too. My first thought is always a programming solution as well. I instantly thought recordset too. One thing that making the TechHelp videos has helped me with is to take a moment to think if there's an easier, basic solution first.

Me Likey! Thanks Rick

Its great to have the different options. I'm not afraid of the programming, Any opportunity is an opportunity to learn - i like kevins method for creating a long term tool for the future but ricks is great for getting the job done simply. Again, great opportunity to learn, so thank you

Kevin, been playing with your suggestion. Came across a few errors i had to work around including one where i was referencing ClientName in the delete instead of ID. Glad to say i got it working though!

2 Things......

- How did get the count column on your form without a query? Did you just Use DCount in a text box control source?

- I'd like to set up a similar thing for future spreadsheets so i'm thinking
     - Normalize in Excel as normal
     - Import Into Access
     - Use a config form to specify table name and maybe a list box to select which fields to check for dupes.
It's a little beyond my abilities yet since I know little about variables and recordsets. I'm guessing the selections on the "Config" form will have to be stored as a variable then fed into the De-Duping button. Exciting, if a little scary as well. I'm going to carry on my the course for now but really appreciate all you've done mate.

I'll check out Composite Key in a bit. Rick, can this be added to the video list?
Edit: To elaborate, add Composite Key to the shortcut menu drop down yo

You are correct. DCount in Control Source.

Just got a situation now where i'm going to compare two spreadsheets. I'm going to load them into one table but the DCOUNT will be helpful. How do I count the dupes like you did? Thanks

Thanks mate

Almost got it but i'm up against these dreaded double double quotes again. This time i'm trying to put the count in a query rather than control source. I'm also removed the Gross field, just want a count of how many duplicate "ClientNames" there are. I've watched Double Double Quotes and believe this is right but i'm getting a #error

Remember you need quotes around a string.

"ClientName=""" & [ClientName] & """"

Double Double Quotes

Thanks Kevin. Must have seen that video many times and i get the premise.

In your example, you have (Minus Other bits)
"ClientName=""" & [ClientName] & """ AND Gross=" & [Gross]

So...

"ClientName"
"" & [ClientName] & ""
"

I tried this as "ClientName=""" & [ClientName] & """ as well. No Luck

No Way! Just missing the one set of quotes? Man i'm getting there but this is definitely my achillies heal!

I think i'm 90% there but can only hope that practice in the course will get me more familiar with it all

Yeah, it took me a while too. In fact I still often make mistakes as well. Half the fun is figuring out what went wrong and fixing it.

hmm... I suppose "fun" is an alternative way to call it!