Delete duplicates AND update fields

[lehcarrodan]

Thought it would be easy with the query wizard.. Not so much.

So I have a whole lot of customers in tblCustomers
Problem is I imported from our old database so some data is duplicated so to start clean I want to delete duplicates.
BUT I want to store the CustomerNotes field of the duplicate entries all in one

EX.
CustID Name LastN CustNotes
CustID1 John Jacob abc
CustID2 John Jacob def
CustID3 John Jacob klsdhlh

WHAT I WANT TO KEEP
CustID Name LastN CustNotes
CustID1 John Jacob abc def klsdhlh


It comes up with 30,000 duplicates with the query wizard.
Any ideas??? Thank youuu

 

[Uncle Gizmo]

I think you're going to need something like this to concatenate the notes together:-

Display Field Data Horizontally

You are also going to need a unique list based on first name, last name. Have a look at this video >>> https://youtu.be/19vrIlOU1Yk?t=2m38s <<< where I demonstrate how to extract unique records. I think this video is based on a single field so you might have to combine two Fields into one and then do unique on that, or there might be another way I haven't given it much thought.

I think you stand a good chance of using losing your unique ID number when you move the records to a new table. You might have to transfer the old ID over to maintain any references between tables. You can use the transferred old ID to control the process of changing the ID throughout the rest of your database to make the records match up again...

 

[arnelgp]

its not Delete and Update, its
Update then Delete.

there are two process to make

1. Update the CustomerNotes field.
to update your field, try the ConCatRelated()
function to gather all CustomerNotes for a
single customer. you can search it on this
forum or on the net

2. Delete Duplicates:
the query should look like this:

DELETE (SELECT COUNT(*) FROM yourTable AS T1 WHERE (T1.CustomerID = yourTable.CustomerID)) AS Expr1, *
FROM yourTable
WHERE (SELECT COUNT(*) FROM yourTable AS T1 WHERE (T1.CustomerID = yourTable.CustomerID))>1;

*****************
REMEMBER:
backup your table first before doing Delete Query.
this action is undoable.

also change "yourTable" with the correct table name
from the query i gave you.

 

[lehcarrodan]

Thanks, you guys are awesome.
I will be working on this Monday but I think you've given me a pretty good idea.