none
Importing Excel Data over foriegn key RRS feed

  • Question

  • I am currently working in the asset tracking template. The primary key "Contact name" in the contacts table is linked to the foreign key "Owner" in the assets table. I have an excel spreadsheet that defines all of the assets and their associated owners. When I import the data into the asset table everything populates except the owner field. I think this is because that field is defined as a number which I understand it has to be because it is linked to a primary key.

    My end goal to create multiple individual reports per owner listing the assets they own. The template works great, I just don't want to have to manually add owners names each time.

    What do you recommend I do?

    Tuesday, July 12, 2016 9:53 PM

Answers

  • I'm looking at the "Asset Tracking Updated" template.

    The primary key in Contacts is ID. It's understandable your Excel file won't have that value.

    The way I would proceed is by LINKING rather than importing the Excel file. Then create an Append query that copies the data from the Excel linked table into Assets, at the same time joining with the Contacts table on Company, in order to "look up" the corresponding ID value to be stored in Owner.

    BTW, I think it is fishy that Assets.Owner is not required. What does that really mean, a free-floating asset?


    -Tom. Microsoft Access MVP


    Tuesday, July 12, 2016 10:03 PM

All replies

  • I'm looking at the "Asset Tracking Updated" template.

    The primary key in Contacts is ID. It's understandable your Excel file won't have that value.

    The way I would proceed is by LINKING rather than importing the Excel file. Then create an Append query that copies the data from the Excel linked table into Assets, at the same time joining with the Contacts table on Company, in order to "look up" the corresponding ID value to be stored in Owner.

    BTW, I think it is fishy that Assets.Owner is not required. What does that really mean, a free-floating asset?


    -Tom. Microsoft Access MVP


    Tuesday, July 12, 2016 10:03 PM
  • >>> I just don't want to have to manually add owners names each time.

    According to your description, you could use the OutputTo method to output the data in the specified Access database object (a datasheet, form, report, query) to several output formats:
    DoCmd.OutputTo acOutputQuery, "YourQueryName", acFormatXLSX, , True
    For more information, click here to refer about DoCmd.OutputTo Method (Access)

    • Proposed as answer by David_JunFeng Wednesday, July 20, 2016 2:29 PM
    Wednesday, July 13, 2016 10:47 AM