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

[Mike Webb]

[Sorry for the cross-post in the GettingStarted NG; sent it to the wrong
group.]

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

 

[John Vinson]

[Sorry for the cross-post in the GettingStarted NG; sent it to the wrong
group.]

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).

Well... DON'T USE DATASHEETS. Subdatasheets have *very* limited
utility, and are a major drag on performance.

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.

PK's can be Autonumber or Number; if they're Numbers then you need to
either manually or programmatically assign unique values to each
record (which is why Autonumbers are handy).

One misconception here:

Having a relationship *does not mean you will see data from the other
table*!!! If there are no records in tblJoinContactsAndCategories,
then you will not see any categories when you view a Contact; if there
are records in tblJoinContactsAndCategories with the ID of the Contact
record that you're viewing, then you will see the CategoryID's (*but
not the category names*) for those records.

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

And ID and Subcategory_ID are Long Integers right?

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.

Your table structure is correct. There was no SNAFU. There was only a
misunderstanding of how this works!

Try this:

Create a Form based on tblAddressList, with the name, address fields,
etc.

Use the Subform toolbox wizard to create a Subform based on
tblJoinContactsAndCategories. Make this a Continuous Subform (view the
form properties and set Continuous as the default). Use
tblAddressList.ID as the Master Link Field and ID as the Child Link
Field.

On the Subform put a Combo Box, bound to SubCategory_ID, based on the
Category table. The Combo Box wizard will set this up for you - the
Subcategory_ID will be the bound column, but it will be invisible;
what you will see in the combo box is the category name.

Now you can bring up the record for Joe Jones; on the Subform you can
now use the dropdown combo box to select "Donor" and "Volunteer" and
"Professional Ornithologist" as categories for him. You can move to
another record and select that person's list of categories. Move back
to Joe's record and you'll see the three originally selected values.