If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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!! |
#2
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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 wrote: 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!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#3
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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" wrote: 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 wrote: 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!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#4
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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 lexrpcv wrote: 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] clue what any of those mean so hopefully this can be a "open this, click that, enter here" etc. sort of answer. Thanks again!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#5
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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. "lexrpcv" wrote: 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" wrote: 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 wrote: 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!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#6
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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 Fred wrote: 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!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#7
|
|||
|
|||
Working with many-to-many relationships in Access 2007
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 "bhicks11 via AccessMonster.com" wrote: 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 Fred wrote: 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!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#8
|
|||
|
|||
Working with many-to-many relationships in Access 2007
I didn't take it personally! That's the problem with the written word - you
have to guess at the intent. We're good. Fred wrote: 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] clue what any of those mean so hopefully this can be a "open this, click that, enter here" etc. sort of answer. Thanks again!! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200806/1 |
#9
|
|||
|
|||
Working with many-to-many relationships in Access 2007
On Mon, 30 Jun 2008 05:44:00 -0700, lexrpcv
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|