In an Excel spreadsheet i have case fields and contact fields - the same fields are in my db but are in two separate tables which are related in a query.
How do i import this data into Access so that cases are related to contacts?
Thanks,
John
In an Excel spreadsheet i have case fields and contact fields - the same fields are in my db but are in two separate tables which are related in a query.
How do i import this data into Access so that cases are related to contacts?
Thanks,
John
download the excel file to the same place everytime, i.e.: c:\temp\File2Import.xls
overwriting it each time.
link this as an external table. (done only once)
build an append query to import the data from the xl file.
put this query in a macro
you may need to run a 'fix data' query before or after the import.
do this for all sheets in the xl file.
then the steps are:
1. save the file,
2. run the macro.
done.
I'm not sure this is within my skill set. Here are some questions I have. Thanks.
INSTRUCTION MY QUESTIONS THOUGHTS? download the excel file to the same place everytime, i.e.: c:\temp\File2Import.xls overwriting it each time. I only have one Excel sheet. I’m not sure why multiple downloads are necessary. Am I to split the Excel Sheet into two – one for cases table and one for contacts table?
link this as an external table. (done only once) Do I import as a new table? Also, my db is split. Assuming I import to front end?? build an append query to import the data from the xl file. Not sure how to do this.Can you point me in the right direction? put this query in a macro Need help here too. what is the macro for and how do i build? you may need to run a 'fix data' query before or after the import. Is this to make sure the fields/cells are the same data type with same column headings? do this for all sheets in the xl file. Only one sheet then the steps are: 1. save the file, 2. run the macro.
You really have not provided enough information to provide a focused response to your initial or subsequent post. But by the sound of it you will need 2 or more append queries. Would need answers to all of the following
- you say case fields - does that mean there is more than one column in the excel file with cases?
- ditto contacts?
- does your excel data have a primary key column?
- if so, is that something that needs to be in access?
- which is the 'parent' table in access? cases or contacts?
- what is the relationship between cases and contacts? one to many (in which case which is the one?) or many to many? i.e. one case can have many contacts and one contact can have many cases.
- do cases and/or contacts already exist in access which are also in the excel file?
- does your access tables contain the required primary and foreign key fields
- from your second post this is a one time exercise, never to be repeated