Import to several tables in one process

[Jacques]

Hello,
I need to make a process where we can import a monthly Excel file into
Access 2007.
The issue here is that the data coming from the Excel file, needs to go to
several tables in the Access DB.

For example. The Excel file will have:

FName, LName, Street, City, State, Zip, HoursWorked, MoneyPaid

In the Access DB this information is held as

Table 1: FName, LName
Table 2: Street, City, State, Zip
Table 3: HoursWorked, MoneyPaid

and so forth.

How can we make a query, or process, that imports the Excel file, as new
info into the necessary tables.

Also, some records would not need to be added. In other words, we can get
records with the same persons name in the Excel file that show up as
duplications (because the Excel file is a generated query from another DB),
but of course that person only shows up once in Table 1, and we only need
them to show up once, and Table 1 has a one-many relationship with Table 2,
which has a one-many with Table 3 and so on.

Thanks beforehand

 

[Jacques]

Just to recap, I think the easiest way to ask the question, is how to map
fields in a spreadsheet to fields that are in more than one table when doing
an import?

 

[Ken Snell]

Import the EXCEL data into a single table, Then use append queries to copy
the data into the permanent tables.

 

[Jacques]

Append will only let you append to one table at a time.
I need to append to multiple tables at a time.

Also, I need to append to some, while not appending to others based on the
data.

If Table1: already has the person, and nothing has changed, then only
Table2: and 3 would actually take new records.
Or even the converse, where the records being imported already exist in
Table1 and 2, but additions are needed to Table3.

The issue is I'm getting data that isn't normalized. Its coming as a CSV
from another DB, on a weekly basis.
We need to import the data into a DB where the CSV contains fields that
stretch across 6 tables in our system.

I hope that makes it more clear.

 

[Ken Snell]

Run multiple append queries to append the data to multiple tables. You use
the append queries to distribute the nonnormalized data to the normalized
tables.

 

[Jacques]

Oh that is so nasty.
How do I not duplicate info in certain tables?

 

[Ken Snell]

By left-joining the temporary/interim table to the permanent one, and only
importing records where the primary key value in the permanent table is
NULL.