Solved: importing then normailising 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 depatment 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

[CreganTur]

Please explain what you mean by normalizing data- what exactly are you trying to accomplish?

You posted an Access 2007 formatted database. Most people here do not have 2007.

[philfer]

Hello,

It wouldnt let me upload a .mdb file it said that the only valid extension was .accdb. I'll try again but maybe I am doing something wrong.

I am trying to get the excel data :-

Firstname Lastname Department

Harry David Accounting

Jerry Seinfeld Sales

George Costanza Warehouse

to automatically be apended to Table1 but the Department will end up as 1,2,3 according to the department number in Table2

Table1 had fields ID, Firstname, Lastname, Dept
Table2 had fields DeptID, DeptName

And there is a relationship between the two tables on DeptID=Dept



Thanks
Phil

[CreganTur]

It wouldnt let me upload a .mdb file it said that the only valid extension was .accdb.
You have to zip .mdb's.


to automatically be apended to Table1 but the Department will end up as 1,2,3 according to the department number in Table2
Well, this depends on how you're importing the Excel data. If you're using an ADO or DAO connection you can run the recordset through some conditionals to change the department name to a department number.

Or you can import the spreadsheet into a temporary table, run an update query with conditionals to change the department names to numbers, append the temp table records to your real table, and then delete the temp table.

As you can see, there are a few options for what you want to accomplish.