Access Merge Duplicate records while keeping some of the old information (1 Viewer)

[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?

 

[Pat Hartman]

The scanner file can probably be imported directly into Access so you shouldn't even need Excel as an intermediary.

If you were going to append the data from the external scanner/Excel file, you could link to the file and run an append query. However, since you want to update existing rows, you can't use this method because Access requires all tables in an update query to be updatable and the linked flat-file tables (excel and the scanner) are not updateable via an update query. That means that you would need to import the data into an Access table first. Then you can join the imported table to your permanent table and use an update query to update existing data. You can also append new rows using an append query.

Since you are going to have to actually import the data before applying it (unless you want to write code and do it that way), you will be causing your database to bloat. We can offer suggestions to get around that problem also.

 

[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