Microsoft Access import foreign key

Tags:
Microsoft Access
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: Tables/fields involved: 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. Thanks.


Software/Hardware used:
MS Access
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: