Need some advice on how to import data over

[jonathanchye]

Hi,

I am in the process of upgrading a clients database from Excel format into Access.

I am running into a slight problem because in the Excel spreadsheet everything is in one worksheet but in my database I have normalised them to I have linked tables (subdatasheets) and I am not sure how to "import" data over except manually keying each on in (which I don't hope to do as there are thousands of records...)

In the Excel spreadsheet the fields are :

Department, Customer (each department have own customers), Customer Site, Date of Order, Order ID, Product Name, Amount, Component ID (products could be split into components), Component Name, Component Cost.

So in my Database I have the following table structure :

tblMain
PKMainID
fkProductID
pCustSite
dDateOfOrder

tblProduct
PKProductID
fkCompID
nAmount
pProductName

tblComp
PKCompID
pCompName
nCost

I hope you guys get understand what I am trying to say As you can see the Excel Spreadsheet contains everything in one table but in my Access database I have 3 tables. The way they are related fields in tblComp and tblProduct will only be created after tblmain is populated.

Is there anyway possible to create a query that can handle this sort of import?

 

[John Big Booty]

I'd import the data into a temporary table in your DB, and then from there go through a process of normalising the data and appending it to your existing tables from there. Have a look at the sample posted in this thread.

 

[jonathanchye]

I'd import the data into a temporary table in your DB, and then from there go through a process of normalising the data and appending it to your existing tables from there. Have a look at the sample posted in this thread.

Thanks for your reply Had a quick look at noticed you have used different queries to normalise data. One question though, your tables doesn't seem to be related directly (the lines that join them in Relationships view) although I do notice they have the related IDs - will your example still work for me?

 

[John Big Booty]

My sample is only designed to provide you with an example of how you might approach your problem, so in that sense yes you should be able to use the same principals in your DB.