Results 1 to 2 of 2
  1. #1
    ExcelGrad is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    7

    Excel import and identifying foreign key in destination table

    A user wants to import participation data from Excel but only add records where the participating contact exists in a Contacts table. For any records where the participating contact does not exist in the Contacts table, the user wants to be able to identify and disposition those records: either add them to both tables or isolate them for further investigation.

    In another post I described my approach of using email address as the primary key in a table of contacts (tblContacts). I had a table that captured participation of the contacts in events (tblParticipation). In tblParticipation I had email address as a foreign key and enforced the requirement that the email address exist in the tblContacts in order to add a record to tblParticipation.

    I received a comment in the earlier post about potential issues with using email address as primary key. It was suggested that I use a unique number as primary. Email address would still be in tblContacts and uniqueness would be enforced.

    Table structures would be:
    tblContacts
    ContactPK (Auto Number)
    Email (Short Text)
    LastName (Short Text)
    FirstName (Short Text)
    Address, Phone, etc.

    tblParticipation
    ParticipationPK (Auto Number)
    ContactFK (Double)
    Email (Short Text)
    Event (Short Text)
    Date, Role, etc.

    My question regards how to import participation data from Excel into tblParticipation if I change tblContacts primary key as indicated above. The Excel data would have an email address but not the contact’s unique number PK.

    My thinking is:
    - Import Excel data into a temporary table (ContactFK field will be empty).
    - Run an update query to update the ContactFK field in temporary table with the ContactPK if email is found in tblContacts.
    - Run an append query to tblParticipation for records in temporary table with a non-NULL ContactFK field (contact exists in tblContatcs).
    - Run a delete query to remove the records from temporary table that were just appended.

    This would leave records in the temporary table with NULL ContactFK, meaning the email address in import does not exist in tblContacts. User can decide whether to append the remaining records to tblContacts. If so, tblContacts email field will be populated with imported data and the ContactPK will be assigned. The user can then repeat the queries above to add the records to tblParticipation.



    If they choose not to append to tblContacts, then the temporary table provides a list of import records to investigate.

    Please let me know if I'm over-complicating this and if there are better ways to approach. Thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your plan sounds reasonable to me.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-08-2019, 03:18 PM
  2. Replies: 1
    Last Post: 04-25-2015, 01:44 PM
  3. Replies: 1
    Last Post: 04-25-2015, 01:41 PM
  4. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  5. Replies: 4
    Last Post: 12-17-2012, 01:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums