My question is basically this about MS Access: How do you import data into an existing table if at least one of the fields actually contains a foreign key related to another table?
Let me give an example:
1. "tblEmployee" with fields "EmployeeID", "EmployeeName", "StateID"
2. "tblState" with fields "StateID" and "StateName"
In the field "tblEmployee.StateID", I have numbers derived from the field "tblState.StateID".
I have the relationships set up, but I don't actually have it as a lookup field. I read (on the internet, haha) that it's not a good idea to make the lookup field at the table level, but instead that you should use form fields as combo boxes to select data from one table and insert the data into another one. (Let me know if this is unclear)
Now, my issue is that when I try to import data into my table "tblEmployee", it won't work because the Excel file has the State name rather than my StateID stored in the file for import. I know that I can go in to the Excel file, and change all the State names to numbers based on my table "tblState".
Example: Alabama might have a foreign key of "1"…etc. instead of the name itself stored in my table "tblEmployee". But it's very cumbersome to have to change every field to numbers ahead of time before importing into my table.
I'm guessing that I could do this import if I did make the lookup fields at the table level instead of only at the form level, but I'm wondering if there is a better way to do this.
Let me know if I'm being unclear. I looked around forums and did google searches for this, but I'm not exactly sure what question to ask. If all you have is a link to this question already answered, that works for me.