Working with many-to-many relationships in Access 2007

[lexrpcv]

Hi, I'm new to Access but have been studiously using the help, tutorials, and
forums. I seem to have the tables laid out but am now stuck at getting the
relationships worked out. Basically, I have 3 tables: 1-Contacts (people),
2-Programs , and 3- Organizations. Each contact works with one organization
but each organization may have more than one contact, so that is a
one-to-many relationship where the organization is one and the contacts are
many. So far so good.

However, each organization may work with more than one program, and each
program may have more than one organization (organizations collaborate to
sponsor programs). Similarly, each contact may work with more than one
program (but different contacts in each organization may work on different
programs). So I have also followed the tutorials and created two junction
tables, using contact ID and Program ID in one and Organization ID and
Program ID in the other.

What I don't understand now is...how do I work with these? I have created
basic forms for each table and the one-to-many functions thus far seem to be
working. But when I add to the program form the Contact ID (I'm selecting
the Contact ID field from the junction table, should I select it from the
Contact table?), it gives me a ?Name error. I want to be able to select all
of the contacts who work with that particular program--i.e. to list the
people involved in the program. Similarly, in the Program form I want to be
able to select all of the Organizations which work with that program...and
finally in an Organization form I want to select all of the Contacts who work
with that organization (easy, one-to-many) and all of the programs that the
Organization collaborates on. Sigh.

Is there a tutorial that I'm missing that will explain this? I am also
confused because the junction tables are empty and I'm not sure if they
should be filled somehow by me or if they will become filled as I use the
forms to describe each program/organization/contact. Or are junction tables
really just hidden tools that I should ignore?

Thanks so much for any direction you can give. I've seen a lot of posts
that include a lot of "VBA" and "SQL" and other abbreviations. I have no
clue what any of those mean so hopefully this can be a "open this, click
that, enter here" etc. sort of answer. Thanks again!!

 

[bhicks11 via AccessMonster.com]

Here's similar question that Allen Brown answered, similar and might help you:


http://bytes.com/forum/thread194566.html

Bonnie
http://www.dataplus-svc.com

 

[lexrpcv]

Hi Bonnie,

Thanks so much! I tried to apply the same logic...but I got lost in Allen's
and Marcy's back and forth. I am, if anything, more confused because they're
talking about "continuous forms" and "bound" and comboboxes--on a form or in
the table, and in which table? I think I need another example. Anyone else
have any suggestions? Thanks!

Lexi

 

[bhicks11 via AccessMonster.com]

Hi Lexi,

A continuous form is just how the form is viewed - it is a property you
select in the form properties.

A bound control on your form is just one that has a data source. A combobox
is just a type of control or textbox on your form that you apply a query to
so you can select an item from your table.

Bonnie

http://www.dataplus-svc.com

Hi Bonnie,

Thanks so much! I tried to apply the same logic...but I got lost in Allen's
and Marcy's back and forth. I am, if anything, more confused because they're
talking about "continuous forms" and "bound" and comboboxes--on a form or in
the table, and in which table? I think I need another example. Anyone else
have any suggestions? Thanks!

Lexi

Here's similar question that Allen Brown answered, similar and might help you:

[quoted text clipped - 41 lines]

 

[Fred]

I have the extra qualification of knowing less than the other people who
answer these columns and also not afraid to describe low tech baby steps.

Make sure that every table has a unique indentifier field, and that it has
been set as the Primary Key for that table. Particularly important for your
3 data tables

Start by loading the junction tables manually. A record in a junction table
records an instance of a relation between an organization and a program. If
you don't have any record in the junction tables you have no links and
nothing in you many-to-many relationship will work. A junction table record
would consist of the
PK value of the desired record in Companies and the PK value of the desired
record in Programs.

Practice / debug by creating a few multi-table queries that set your
desired conditions and show the data that you want.

Then start designing forms.

 

[bhicks11 via AccessMonster.com]

Hi Fred, I like your modesty. Nobody knows everything or has done everything.
I'm not a giant head either. Just looking to help.

Bonnie
http://www.dataplus-svc.com

I have the extra qualification of knowing less than the other people who
answer these columns and also not afraid to describe low tech baby steps.

Make sure that every table has a unique indentifier field, and that it has
been set as the Primary Key for that table. Particularly important for your
3 data tables

Start by loading the junction tables manually. A record in a junction table
records an instance of a relation between an organization and a program. If
you don't have any record in the junction tables you have no links and
nothing in you many-to-many relationship will work. A junction table record
would consist of the
PK value of the desired record in Companies and the PK value of the desired
record in Programs.

Practice / debug by creating a few multi-table queries that set your
desired conditions and show the data that you want.

Then start designing forms.

Hi Bonnie,

[quoted text clipped - 51 lines]

 

[Fred]

Bonnie,

Thanks for the post.

Just to be clear, when I wrote this I wasn't implying anything about your
first answer and had not yet even seen your second answer. I was just
speaking in general.

Fred

Hi Fred, I like your modesty. Nobody knows everything or has done everything.
I'm not a giant head either. Just looking to help.

Bonnie
http://www.dataplus-svc.com

I have the extra qualification of knowing less than the other people who
answer these columns and also not afraid to describe low tech baby steps.

Make sure that every table has a unique indentifier field, and that it has
been set as the Primary Key for that table. Particularly important for your
3 data tables

Start by loading the junction tables manually. A record in a junction table
records an instance of a relation between an organization and a program. If
you don't have any record in the junction tables you have no links and
nothing in you many-to-many relationship will work. A junction table record
would consist of the
PK value of the desired record in Companies and the PK value of the desired
record in Programs.

Practice / debug by creating a few multi-table queries that set your
desired conditions and show the data that you want.

Then start designing forms.

Hi Bonnie,

[quoted text clipped - 51 lines]

clue what any of those mean so hopefully this can be a "open this, click
that, enter here" etc. sort of answer. Thanks again!!

 

[bhicks11 via AccessMonster.com]

I didn't take it personally! That's the problem with the written word - you
have to guess at the intent. We're good.

Bonnie,

Thanks for the post.

Just to be clear, when I wrote this I wasn't implying anything about your
first answer and had not yet even seen your second answer. I was just
speaking in general.

Fred

Hi Fred, I like your modesty. Nobody knows everything or has done everything.
I'm not a giant head either. Just looking to help.

[quoted text clipped - 27 lines]

 

[John W. Vinson]

Hi Bonnie,

Thanks so much! I tried to apply the same logic...but I got lost in Allen's
and Marcy's back and forth. I am, if anything, more confused because they're
talking about "continuous forms" and "bound" and comboboxes--on a form or in
the table, and in which table? I think I need another example. Anyone else
have any suggestions? Thanks!

Just a comment to clarify...

You'll need to work with both Tables and Forms. They are different kinds of
objects and they have differerent purposes.

Tables are for storage of data. Despite Microsoft's blandishments with
misfeatures like Lookup Fields and Subdatasheets, they are NOT really designed
or appropriate for user interaction; in general, users of a finished Access
application should never even SEE a table.

Forms are tools, "windows" if you will, to let users interact with data in the
tables. Forms don't contain any data; they just let you work with data (which
is in the tables).

For your database design you need to first get the table structures and
relationships right - I'd say you need at least two more tables, one for each
many to many relationship; and THEN work on the forms.