importing then normalising data

[philfer]

Hello,

I am trying to import some excel data that comes from a daily extract into a database

The problem is that the excel data is not normalised but the access database is.

I am attaching the database below below as an example. The excel file

Firstname Lastname Department

Harry David Accounting

Jerry Seinfeld Sales

George Costanza Warehouse

has the department names but Table1 has a department number foreign key which is in a relationship with Table2.

Is there a way to import the excel data daily in a normailsed way from Access

Thanks
Phil

 

[gemma-the-husky]

no - import it into a table

there are some tools to autoanalyse and normalise a table

or you can inspect it, and do it manually (which is what i would do)

 

[Thomko]

Hello Phil,

do I assume correcly that you only want to load Table1? If the load could result also in new entries in Table2 (Departments) it gets more complicated.
Do I also assume correctly that all departments in the Excel list exist in the department table?

Under the assumption above you could do it like this:
1) Import the Excel sheet into a temporary table
2) Create an Append Query that joins the temp table with the department table, replacing the department name with the ID and appending records to Table1

Then you can automate the procedure (import Excel, empty Table1, run append query) with a macro or VBA.

HTH
Thomas

 

[philfer]

Hi everyone,

Thanks for your advice.

I tried doing the above and imported the Excel worksheet into a new table called "Sheet1"

I then tried to create an append query but am getting a little stuck.

I feel I'm quite close

Can you help

Thanks
Phil

 

[raskew]

Hi -

INSERT INTO
    Table1 
   (Firstname
  , LastName
  , Dept) 
SELECT
    Sheet1.Firstname
  , Sheet1.Lastname
  , Table2.DeptID
FROM
   Sheet1 
LEFT JOIN
   Table2 
ON
   Sheet1.Department = Table2.DeptName;

HTH - Bob