Comparing Large Amounts of Data in Excel & Access

Minka

New Member
Joined
Nov 29, 2005
Messages
1
I have 2100 records of data in Access. I just got an updated version of the research data in an Excel Spreadsheet with an additional 300 entries. Most of the data overlaps.

I am trying to Export the data from Access into Excel so that I can compare the old list (which has been edited) and new list (thats completely raw).

After I come up with the new additions, I would then like to import on the new stuff back into the Access database.

Is there a way that I can import the new data into Access and have it pull out the duplicates? Is that the best way?

Please help, I am new at this and don't really know what I am doing. :(
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Try the following:

1. Import the new data into Access, using File | Get External Data | Import. Save it to a new table called Temp.

2. Create a Union query to get a unique dataset -- this will only work if ALL fields in both tables have the same names.
So...
Create a new query in Design View. Close the box that prompts you for tables.
Click the SQL button at the top left of the grid.
Let's say your tables are MainData and Temp. The SQL statement would be:
SELECT * FROM MainData

UNION

SELECT * From Temp;

Run the query to see how it looks. Save it as qryUnion

Now to find the records that don't already exist in the system. Build a new query with your original table (MainDate in this example) and the union query. Join them on a unique key value. Still in Design view, do this:
a. Double-click the asterisk on the union query. This *** all fields to the grid.
b. Right-click the join line and pick the option that shows all records for the union query but only existing records for the table. Take a look to see how it works.
c. Back to Design view. Double-click the first field in the table to add it to the grid. In the Criteria row for this field, type Is Null
d. Click off the check box for this field -- it doesn't need to be displayed.
Save this query. Let's call it qryNewRecords.

Now you need to turn this into a table. You can create a separate Make-Table query that converts the output of qryNewRecords into a table.
Check the help to see how to set it up. This table should be called NewData or somesuch.

Finally, you can append these new records to the original table. The Help can show you how to do that.

A lot of words, but you'll find it is quite easy to run when you have it set up. Down the track you can automate it if this is a regular chore.

Denis
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,128
Messages
5,857,533
Members
431,884
Latest member
Gcmoore63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top