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

[John Bartow]

If that’s how you do memberships then that's how you should record it.
If you use a primary key that’s an auto number the name field is rather irrelevant. 

-----Original Message-----
From: dba-Tech <dba-tech-bounces at databaseadvisors.com> On Behalf Of Susan Harkins
Sent: Saturday, August 04, 2018 2:09 PM
To: 'Discussion of Hardware and Software issues' <dba-tech at databaseadvisors.com>
Subject: Re: [dba-Tech] Importing data into Access and creating primary and foreign keys

Of course, we have another money wrench, but I think I've thought my way around it. Originally, memberships could have two adult names and they entered both names in the same field. <gasp> Sorry guys -- I wasn't there and had nothing to do with the original structure. 😊 

I think I'll use Excel's Text to Column feature to get all those names in individual columns before importing it into Access to start the conversion. With the pks in that big table -- as John suggests, I think I can separate them using a couple of append queries. 

I think... 😉 

Putting the fks back in the big table is a bit of genius. 😊 I think we can get this done with very little manual cleanup. 

Thanks guys!

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


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