I have an existing list of personnel that I am trying to associate with an imported list of computers. Specifically, I want to replace the current columns listing the names of the personnel assigned to each computer with the proper foreign key referencing them in the personnel table. Since these sheets both began life in Excel, I'm not sure how to import this and make that happen.


This may be a little hackier than what you had in mind, but you can achieve this by importing your Personnel Name and ID data to a new sheet and then using Excel's lookup functions on this imported data to get the IDs for the personnel in the computer list.

Step 1: Import your Access Personnel ID data to Excel.
Just use Excel's Get External Data feature to get the data from Access. You can put the imported data on a new sheet. You'll probably choose to delete the sheet when you're done anyway.

enter image description here

Step 2: Use INDEX and MATCH to look up the Personnel IDs for the names in your computer list.
In a separate column on the sheet with your computer data, you can use a formula like the following to look up the ID for the Employee named in A3.

=INDEX(Table_Database10.accdb[[#All],[EmployeeID]],MATCH(Formulas!A3,Table_Database10.accdb[[#All],[Last Name]],0))

You can get the database references by selecting the appropriate columns from your imported data. Excel will generate the database field references.

enter image description here

Step 3: Copy values from these formulas over your Personnel names in your computer data. After doing this you can remove the data and formulas you added in the previous steps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.