Lost this post first time around, don't have it in me to fully recreate the context. Basically, I'm building an Access solution for a medium sized org, have to clean and import 15 years of dirty data from many, many, discrete Excel sheets.
In the current Excel "solution" each row has contact information for Contacts, with columns for email(s), phone(s), address(es), etc. Sometimes the user entered multiple emails/phones/etc into the same relevant column for a Contact, sometimes they added another row for the same Contact.
I've structured the Access database so that the contacts table is related many-to-many with emails, phones, addresses, etc. tables.
My question: is it at all possible to import data from Excel into Access and specify which column from Excel goes to which attribute in which table in Access, all while maintaining referential integrity?
If not, what, if any, are the best practices for importing/cleaning data into many-to-many related tables?
In the current Excel "solution" each row has contact information for Contacts, with columns for email(s), phone(s), address(es), etc. Sometimes the user entered multiple emails/phones/etc into the same relevant column for a Contact, sometimes they added another row for the same Contact.
I've structured the Access database so that the contacts table is related many-to-many with emails, phones, addresses, etc. tables.
My question: is it at all possible to import data from Excel into Access and specify which column from Excel goes to which attribute in which table in Access, all while maintaining referential integrity?
If not, what, if any, are the best practices for importing/cleaning data into many-to-many related tables?