Access Merge Duplicate records while keeping some of the old information

[CatYost]

Hi,

I am pretty new to access but I have created and run queries and reports. Every week we are updating information in our database and it takes forever, is there a way to automate this process?

What I have:
(This info stays the same) [This info is updated]
ID ( Performer Name Phone Email Dress Size Shoe Size ) [Chest Waist Hip Outseam Arm Length]

We use a sperate program to take measurements so that information is imported from Excel (from a scanner program), then appends the table with the information with parenthesis. So esentially i would like to overwrite the data from some fields while not touching others.

Right now I am having to manually select and copy the bracket info and paste it into the original (smaller pkey) a few rows at a time, then delete the newer one (bigger pkey) I do this in batches of about 20-30 people and it takes forever. I would also eventually like to train someone else to do this particular task and it would be much simpler to show them a few button presses and not a complex process.

I have tried looking at some tutorials but none of them really touch what I am actually attempting.

If it is impossible to do with this current set up but you have a different set up method (Merging tables maybe) that would work I am happy to do that.

Thank, Cat

 

[MajP]

This can be fully automated. Can you provide a sample input and desired output or even a small db with some data? That would speed up getting you a full solution.
But looking at your example it should be a simple insert query if the ID of the new data matches the ID of the old data. If not, is Performer Name unique in the DB?

 

[CatYost]

This is the processing software that takes our scanned information and takes the measurements and the options that are built into the system

This is what is exported to excel currently

This is the set up of the database that I inherited. Each record has a unique to it. Then we have their first and last name, i cannot grantee that the first or last names are unique as we have well over 500 records currently. There are basically two sets of information the info we update directly in access (name, phone, email, height, dress size etc.) This information goes from the ID to Glove in the example below. Then there is the information that is gotten from the SizeStream Studio software, (Head and over). Sometimes these feilds are blank, if this is the first time we are seeing this performer, but the first set is filled in already. Usually there is a set of data in those Head and over fields that is being updated.

Currently I have a quiery that finds all the duplicates using both their first and last names as a combination since appending the file and adding the new info creates a new record and ID number. Then I copy and past the new info into the old record and delete the new one.

I really appreciate your help and your time!

 

[bastanu]

I would suggest you split the table into a tblPerformer (with ID,FName,LName,DOB,etc.) and another one with the data coming from SizeStream via Excel. In that table add a ImportedOn date field and set its default value to Now(). You can now import the data and not only have the most current record for each performer (based on the Max(ImportedOn)), but also the full history of size changes - might get you in trouble with some performers .

Cheers,
Vlad