Importing fields from multiple columns in an Excel spreadsheet into a single row in Access

Question

We get new data for our database from an online form that outputs as an Excel sheet. To normalize the data for the database, I want to combine multiple columns into one row.

Example, I want data like this:

ID | Home Phone | Cell Phone | Work Phone

1 .... 555-1234 ...... 555-3737 ... 555-3837

To become this:

PhoneID | ID | Phone Number | Phone type

1 ............ 1 ....... 555-1234 ....... Home

2 ............ 1 ....... 555-3737 ....... Cell

3 ............ 1 ....... 555-3837 ...... Work

To import the data, I have a button that finds the spreadsheet and then runs a bunch of queries to add the data.

How can I write a query to append this data to the end of an existing table without ending up with duplicate records? The data pulled from the website is all stored and archived in an Excel sheet that will be updated without removing the old data (we don't want to lose this extra backup), so with each import, I need it to disregard all of the previously entered data.

I was able to make a query that lists everything out in the correct from the original spreadsheet (I entered the external spreadsheet into an unnormalized table in Access to test it) but when I try to append it to the phone number table, it adds all of the data repeatedly. I can remove it with a query to remove duplicate data, but I'd rather not leave it like that.

Answer 1

You could run a delete query on the table where you store the queried data and then run your imports.

Assuming that the data is only being updated.

The delete query will remove all records and then you can run the import to repopulate the table - therefore no duplicates.

Answer 2

There are several possible approaches to this problem; which one you choose may depend on the size of the dataset relative to the number of updates being processed. Basically, the choices are: 1) Add a unique index to the destination table, so that Access will refuse to add a duplicate record. You'll need to handle the possible warning ("Access was unable to add xxx records due to index violations" or similar). 2) Import the incoming data to a staging table, then outer join the staging table to the destination table and append only records where the key field(s) in the destination table are null (i.e., there's no matching record in the destination table).

I have used both approaches in the past - I like the index approach for its simplicity, and I like the staging approach for its flexibility, because you can do a lot with the incoming data before you append it if you need to.