Comparing Large Amounts of Data in Excel & Access

[Minka]

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.

 

[SydneyGeek]

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