One-to-Many Relationship isn't working right; request assistance/advice

[Mike Webb]

Using Access 2K2
Experience Level: Beginner

Problem: Have established the one-to-many relationships between 2 main
tables and a join table, but I can't see any data in the subdata sheets (my
term for the datasheet you see when you click on the "+" symbol in the first
column in a table).

Description: I was designing a data entry form and couldn't get it to work.
Someone on the Forms newsgroup gave me some ideas and I found that my PK's
are set to "Number" vice "AutoNumber". (I imported these some time ago when
I was first learning Access.) Since I was getting errors for trying to save
a record with a Null value, I figured I needed to change the PK's to
"autonumber". I printed out the relationships window so I could reestablish
them again the same way, then deleted the relationships. I then made the
changes by deleting the existing PK and adding in a new PK with the same
name, with the only change being the datatype. I then tried to reestablish
the relationships and was "locked into" one-to-one relationships. Looked at
properties and Help and saw I had to change the indexing for the FK's to
"No". Did so and was able to get One-to-Many set up. Still can't see any
data from the other tables, though.

Table design:
tblAddressList -- ID is the PK and set to autonumber
tblAddressListCategories -- SubCategory_ID is the PK and set to autonumber
tblJoinContactsAndCategories -- Table_ID is the PK, ID and SubCategory_ID
are the FK's

One-to-Many's got from the first 2 tables to the latter.

Reasoning: Each contact in tblAddressList is assigned one or more
categories, such as "donor", "US Fish and Wildlife Service", "Advisory
Board", etc. Looking at the rules for normalization, I put that data into
two tables and used a "join" table. This worked fine before my "SNAFU" this
morning.

What did I do wrong (besides fail to back up my database before I made a
change like this)? How can I fix it?

TIA, and sorry if the post is too long. Wanted to make sure the reader got
enough info to visualize my setup and problem.

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

 

[Rolls]

Use a separate table for each entity.

1) tblPerson

(Address is an attribute of Person)

2) tblCategory

Use a separate table for each relationship between entities.

3) tblPerson-Category

This table consists of one FK (numeric - long integer) field for each of the
two PK fields in the first two tables. If the same person has two
categories there will be two records.

Entity tables are filled in first, relationship tables next, using the
respective table's PK and descriptive fields as a rowsource for a combo box
that will, when selected, add the PK value to the FK field in the
relationship table.

There's a one:many or Parent:Child relationship between
tblPerson:tblCategory. This table should be displayed in a subform with a
combo box that will allow the user to add as many records for each person as
there are available categories, and save then to tblPerson-Category.

Let's say that you also want to display phone numbers for each person. You
would set up an entity table:

4) tblPhoneType

Which could have Home, Work, Cell, Fax phone types. You'll also need a
relationship table

5) tblPerson-Phone

which works the same way tblPerson-Category works.

You can expand the database schema indefinitely in this manner. After the
tables are set uo you can add them to the relationship window.

 

[Mike Webb]

Use a separate table for each entity.

1) tblPerson

(Address is an attribute of Person)

2) tblCategory

Use a separate table for each relationship between entities.

3) tblPerson-Category

This table consists of one FK (numeric - long integer) field for each of the
two PK fields in the first two tables. If the same person has two
categories there will be two records.

Entity tables are filled in first, relationship tables next, using the
respective table's PK and descriptive fields as a rowsource for a combo box
that will, when selected, add the PK value to the FK field in the
relationship table.

There's a one:many or Parent:Child relationship between
tblPerson:tblCategory. This table should be displayed in a subform with a
combo box that will allow the user to add as many records for each person as
there are available categories, and save then to tblPerson-Category.

Let's say that you also want to display phone numbers for each person. You
would set up an entity table:

4) tblPhoneType

Which could have Home, Work, Cell, Fax phone types. You'll also need a
relationship table

5) tblPerson-Phone

which works the same way tblPerson-Category works.

You can expand the database schema indefinitely in this manner. After the
tables are set uo you can add them to the relationship window.