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

[John Colby]

Susan,

My suggestion would be to give the parent table a unique ID (PK) - 
preferably an auto-increment.  I am assuming that you are carving sets 
of fields out of "one big record".  Now the "one big record" has a 
unique identifier, you can put that into the child records as you create 
them as a temporary FK.  Make sure that each and every table you create 
has an auto-increment PK in it.  That tells you which "one big record" 
each child record came from.  Now add a FK back in the "one big record" 
for the child table, and using that FK in the child record, place the 
matching PK from the child back into the FK in the parent record.  Do 
that for each child table carved out.

If you are carving out tables (fields in the parent) which themselves 
have "child table" fields (not normalized once carved out), iterate the 
concept.

Membership

Member

Mbr-Mbrshp will contain a record for each member with a given membership 
PK.  Each member record will have the membership from which it came 
since you placed that in there (as a FK) when you created the member table.


That at least gives you a starting point for the process.


On 8/4/2018 9:16 AM, Susan Harkins wrote:
> 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
>

-- 
John W. Colby