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

[Susan Harkins]

Rocky, it’s a one-time thing, and there's less than 500 records. Once it's in Access, the others will continue to use the Excel sheet. 😊 

Susan H. 


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

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