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.