Table Redesign - need a "sanity check"

[Mike Webb]

Using Access 2K2
Exp. level: Beginner

Background: We are a non-profit engaged in avian (bird) research. We have
a list of about 1900 points of contact in an Excel file that had categories
in the last column. By that I mean a contact can be categorized as a
"donor", a "volunteer", belonging to "US Fish and Wildlife Service", and
someone we "Sent Christmas Card" to -- as an example. I took it over and
decided to migrate it to Access - a learn-as-I-go project. After much
reading and perusing newsgroups, I created a table with all contact info and
a separate table with the categories. Then I made a Join table using the
PK's from the first 2 tables as FK's and a One-To-Many relationship from
each pointing to the Join table. Took awhile to tweak it so it would work,
but I feel (after trying this week to design a data entry form) that this
may be too unwieldy in the long run, and may not be conducive to data entry
and record updates/deletions.

My idea (for which I need a "sanity check"): Drop the Join table. Add the
PK from the Contacts table to the Categories table as an FK and then go
through every row and match each FK to all associated Categories. For
example:

PK FK (from Contacts table) Category
1 1 donor
2 1 US Fish and Wildlife Service
3 2 Sent Christmas Card, etc.

My question for everyone (and I apologize if this is overlong): Is this a
better way to handle this information, and conducive for more efficient
report creation, queries, etc.?

TIA,
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a 501(c)(3) organization

 

[John Vinson]

My idea (for which I need a "sanity check"): Drop the Join table. Add the
PK from the Contacts table to the Categories table as an FK and then go
through every row and match each FK to all associated Categories. For
example:

This will NOT WORK.

It would mean that each category can apply to one, and only to one,
Contact - the one pointed to by the ContactID in the Contacts table.

PK FK (from Contacts table) Category
1 1 donor
2 1 US Fish and Wildlife Service
3 2 Sent Christmas Card, etc.

This is your *junction* table, not your Contacts table. Surely more
than one contact was a Donor? What will be the PK of the next "donor"
record?

My question for everyone (and I apologize if this is overlong): Is this a
better way to handle this information, and conducive for more efficient
report creation, queries, etc.?

Let's fix the three-table problem. It's the correct table structure;
and it's *perfectly routine* to have Forms to enter data for many to
many relationships.

 

[Mike Webb]

Okay, thanks. I'll stick with what I've got and work on the data-entry
form.

Mike