Importing Data and AUTONUM issues

[ChristopherK]

I am currently working with an Access database with multiple tables.
Relationships between the tables are not defined in Access. Rather SQL is
being used to append data to the tables from entries on the forms and to join
tables when pulling data. (i.e., I am using SQL within the VBA code in
Access to make associations between tables (Using Joins in the FROM clause)
rather than setting up relationships between the tables within Access.)

Many of these tables utilize the AUTONUM data type as their primary key.
These AUTONUM values are then used as foreign keys in other tables to
associate data between the tables.

The data needing to be imported is in Excel and requires some serious
scrubbing prior to import, and so I want to be able to specify the values for
all fields in Excel prior to import to ensure the proper records are
associated with each other. My issue is once I import a table with AUTONUM
values, how do I easily make sure these values go into the related tables as
foreign keys with their correct records?

For example, I have a list of 2,000 stores on a table. I can import all the
stores and the AUTONUM field increments as expected. Next I need to import a
table of 1,500 store managers. Some managers have multiple stores, some
managers have no store as of yet. How do I get all the store managers
associated with the right store(s) without manually entering in each AUTONUM
as created on the stores table on the correct record of the managers table?

The curent data source has the store name in one column and the store manager
in another in Excel - but I want to create separate tables to normalize the
data.

My best answer to-date is to import the first table, make note of which
AUTONUM is created for each record and then manually copy those into Excel as
a foreign key prior to import of the related table. Unfortunately as I have
several thousand records on each table, that process would take just as long
as manual data entry through the database front end forms. I could perhaps
export the first table from Access back into Excel and Vlookup the AUTONUM
fields based on store name if I include the store name temporarily on the
managers table in Excel.

Is there an easier way around this?

Any help would be greatly appreciated, thank you!

 

[Clifford Bass]

Hi Christopher,

Try this: Only go in one direction. That is, from Excel into Access.
Link to the Excel data from Access. Then using an append query that lists
store on only one row (select distinct...), import your stores into the
stores table. Likewise, import your managers into the managers table.
Finally, use an append query that joins the linked Excel table with the
stores table and the managers table to import into your store-managers table,
grabbing the store and manager numbers from the appropriate tables. It will
link on the store name and the manager name. Note that if you have different
managers with the same name, you could temporarily make them unique in Excel
for the purpose of the import. So if you have a John Smith at Store A and a
different John Smith at Store B, you might change their names to John-A Smith
and John-B Smith. Then after the import, just fix those few names by hand.

Hope that helps,

Clifford Bass