Need to normalize/transfer after import

[Warren]

**Not sure which discussion group to post this***

I have successfully imported a data list from an excel spreadsheet into a
temporary Access table. Now I want to transfer the data from that temp table
to existing access tables but don't know where to start or the best way to do
it.

Can somebody refer me to some websites with example procedures. I know I'll
need to use SQL but am not sure about macros.

I want to setup a procedure that transfers from the temp table to the
permanent table(s) and need to be able to use it again and again (automated)
as the data will continue to be generated in excel. (For now, I'm okay with
manually importing From excel to the temp Access table.) Each time I execute
this procedure, it will need to append data to existing data residing in the
permanent tables. Also, I'll be concatenating field values from more that
two fields in the temp table to one field in the permanent table.

Thanks for any direction! -Warren

 

[Klatuu]

Well, for starters, you will not want to use a make table query for your
import. The other queries you will need to build to move the data into the
permenant tables will need to have the same table name available for each
execution.

There are a couple of ways you can accomplish this. One would be to link to
the spreadsheet rather than import it and run append queries against the
linked spreadsheet. This method will produce the least amount of database
bloat. But, sometimes, there can be issues with the data types assigned to a
linked spreadsheet. Access makes guesses as to what they should be and may
get it wroing.

The other is to use a predefined table to import the spreadsheet to. If you
use this method, it is necessary to first delete any existing data in the
table before you do the import. A simple delete query will do that.

Then to move the data to the permanent tables, you just need to build append
queries to add the new data to the permanent tables. Where you need to
concatenate fields, use a calculated field that includes the fields to
concatenate. For example, if your spreadsheet has first name and last name
as two fields and your permanent table has it as one field, you could use
something like:
FullName: [FirstName] & " " & [LastName]