Importing old data into multiple tables (1 Viewer)

johnbornsworth

New member
Local time
Today, 04:30
Joined
Oct 29, 2019
Messages
5
Hello there! I would like to import data from an old cloud database of ours (Podio) into a new MS Access database we've developed. Problem is the Access app has more tables than Podio and that's over my head. A easy example is Contact Types. In the exported Excel sheet from Podio we have a flat file, contact name, address, type. Then in Access we have contact name, address and a foreign key (precise word?) for contact type who's data we store in another table (ContactType).

Another example is my excel sheet has a "Created by" field, which is derived from my employees. The Access database Contact table pulls this "Created by" information from an Employees table. All that's stored in the Access Contacts table for who created the record is a number, related to the employee.

So how can I import these excel records into two tables, not just one? Can I perform this with a well-made append query?

John Born
Tree Detective
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:30
Joined
Feb 19, 2002
Messages
42,970
Taking a flat file and normalizing it is easy enough to do if you understand the data. Typically you would start by running totals queries on the "parent" table data to group all the duplicates into a single row. Then convert the totals query to an append query. To append the "child" table records, you join the source table with the new "parent" table on the unique fields and when you append the child records, you include the FK by picking up the PK from the new "parent" table.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:30
Joined
Feb 19, 2013
Messages
16,553
Agree with Pat, one append query for each (affected) table in Access. I would add it is important to get the order in which you run them correct - update all parent tables first, followed by child tables. So for CRM data a single excel file might have data relating to customer, customer type, contact, address and address type.

So the order of append queries would be

customer type
address type
customer
contact
address
 

Users who are viewing this thread

Top Bottom