Import selected columns from excel

[Chrisopia]

I was following some advice on normalising my database, and one key point, which now makes sense, is putting addresses in their own table.

This now means I can categories addresses and have multiple addresses per customer, e.g. shipping, billing, marketing and home. It also saves data - for those who do not have an address.

The main issue is now to import large quantities of data from excel I need to prepare 2 excel files and make sure the CustomerID match those on both files - that's after manually typing in the ID's

I can do this no trouble, but I need to create an automated version for the end user. Some way to import only part of an excel spreadsheet into one table and another part into another table, using the auto number from customerID to populate the Address table with the same ID's...

does this make sense?

Some starting point will be much appreciated.

Thanks in advance!!

 

[PaulO]

I would start by ...

1) LINKING the Excel spreadsheet to the Database ...
2) Create a new blank database table with key fields into which you will be copying the address data
3) Then creating an Append query wherein you select the fields within the records you wish to copy across

Hope this gets you going at least ...

 

[Chrisopia]

Would linking the excel to the access mean that data changes in Excel would affect the data in access? Data needs to be solid, e.g. not move around or editable after import..

I was thinking of making a basic template excel file so users can fill the spreadsheet and click "import" without a wizard popping up asking to find the file (and do the import through VBA)...

the problem with the append query is it won't log the new CustomerID assignments to link the addresses with each customer?? - or will it?
Otherwise it seems only an append query could split the data into 2 tables, which I should have thought of... it's just keeping the customers linked to their address I suppose is the main issue...