[dba-Tech] Importing data into Access and creating primary and foreign keys

[Rocky Smolin]

I this a one-time conversion of existing membership data?

I usually start by importing the spreadsheet into a table and processing the
rows one-by-one in VBA.

I'd add the Membership record if it was not already there, and save the PK
of the membership record (new or existing).  

Then I'd add the member record if it was not already there, and save the PK
of the member record (new or existing)/

Finally I'd add the two saved PKs to the Associate table.

HTH
Rocky


-----Original Message-----
From: dba-Tech [mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of
Susan Harkins
Sent: Saturday, August 04, 2018 6:17 AM
To: Discussion of Hardware and Software issues
Subject: [dba-Tech] Importing data into Access and creating primary and
foreign keys

Here's the setup: We're moving Excel data (so no pk/fk values or
relationships) to Access. The db has five tables: one parent, one child, two
lookups, and an associate between the parent and child to support a mtm
relationship. 

This will be a small db that tracks memberships. Memberships is the parent;
members is the child. Each membership can have more than one member and each
member can have more than one membership -- hence the mtm. All values belong
to the individual membership and are stored in that table. The child
(members) table stores only member names the PK, and a FK to the memberships
table.

Appending records into the parent table and generating pks for each record
is no problem. Appending records into the child table and generating pks for
each record is no problem. However, how do I get the PKs from the parent and
child tables into the associate table as FKs? 

It's not so big that we couldn't enter the members manually, but it seems
like we shouldn't have to. 

Any help would be appreciated. I used to know how to do get the FK values
into a child table, but I have forgotten -- I don't work with Access
anymore. I actually wrote about it, but that was ages ago and I can't find
the article. However, I'm sure I never had the experience with a mtm, only
one to many.  

Susan H. 

_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com