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
|
|||
|
|||
Need help with several many to many relationships
Hi, and thanks for your help.
I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#2
|
|||
|
|||
Need help with several many to many relationships
Your first step is structure. Your two "many to many" relationships need a
junction table to implement the links. You didn't mention what you tried regarding structure nor what problem you ran into. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#3
|
|||
|
|||
Need help with several many to many relationships
I am not sure you stated the relationships correctly (The chicken and the egg).
Each client can have multiple ranks. Can the client have many ranks at the same time or at different time? Each rank can have multiple clients. What gets who - Do you mean a given rank can apply to many clients? If so, that is covered above. This is like right arms and people - a person can not have multiple right arms but people as a whole do have many right arms. But a person (single eintity) can not have many right arms. The same for rank and items. Each item can have ONE client. I do not see how an item can have people. For a given item you can show all the people that have them. -- Build a little, test a little. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#4
|
|||
|
|||
Need help with several many to many relationships
I apologize. I have created two junction tables, one between clients and
ranks, and one between ranks and items. I also, since my post, tried putting "clientID" into the items table so that I could create the one to many relationship stated below. I have a main form that shows the client, and a subform that allows me to write the item that goes with that client. I am trying to do a subform within the subform to pick the appropriate ranks for the item, but I can't get it to show me only the ranks that are appropriate for that client. It shows all ranks in the table. Is that clear? "Fred" wrote: Your first step is structure. Your two "many to many" relationships need a junction table to implement the links. You didn't mention what you tried regarding structure nor what problem you ran into. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#5
|
|||
|
|||
Need help with several many to many relationships
Yes, I think you are right about the relationships; I was just trying to show
all possibilities for each table which means several are repeats. If I am thinking about this correctly, which might be my problem, the smaller list would be: Each client can have multiple ranks. Each client can have multiple items. Each item can have multiple ranks. Each item can have ONE client. Let me try a different example. Imagine a database that is designed for one teacher. The teacher has X number of classes that she teaches. The teacher creates test items for each class, however, some of her classes are similar so the test item might apply to multiple classes. Instead of writing the same item multiple times and assigning it to each class, she just wants to write the same item once and state which classes she could use it for. (Queries will be used to determine what items are available when she goes to create the actual test for the specific class). Then, instead of having one database for each teacher, I want to have one database for all teachers at the same school because they are all doing the same thing. It just happens, that across teachers, some of the classes now have the same name even though they are different classes. (client = teacher; rank = class; item = item) So, I want to have a form where I can pick the client, write the items for that client, and then say what rank(s) the test item is valid for. My problem is this: I have the form that shows the client, and a subform to write that client's test items. I can’t figure out how to assign each item to its corresponding rank. I assumed I needed a “rank” subform inside of the “item” subform, but when I do that it shows me every rank for all clients not just the ranks for the particular client I am working on. I haven’t been able to figure out how to make this work, so I thought my original table and relationship design might be wrong. "KARL DEWEY" wrote: I am not sure you stated the relationships correctly (The chicken and the egg). Each client can have multiple ranks. Can the client have many ranks at the same time or at different time? Each rank can have multiple clients. What gets who - Do you mean a given rank can apply to many clients? If so, that is covered above. This is like right arms and people - a person can not have multiple right arms but people as a whole do have many right arms. But a person (single eintity) can not have many right arms. The same for rank and items. Each item can have ONE client. I do not see how an item can have people. For a given item you can show all the people that have them. -- Build a little, test a little. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#6
|
|||
|
|||
Need help with several many to many relationships
Here is a start --
Teachers – TeachID – Autonumber – primary key LName FName Etc. Classes -- ClassID - Autonumber – primary key Grade_Level Subject Items -- ItemID - Autonumber – primary key Topic Teach_Class – TeachClassID - Autonumber – primary key TeachID – number – long integer – foreign key ClassID – number – long integer – foreign key Period Class_Item – ClassItemID - Autonumber – primary key ClassID – number – long integer – foreign key ItemID - number – long integer – foreign key Form/Subform – Teacher/ Teach_Class with combo in subform to select Class. Master/Child lick set using TeachID. Form/Subform – Classes/ Class_Item with combo in subform to select Item. Master/Child lick set using ClassID. -- Build a little, test a little. "shoup" wrote: Yes, I think you are right about the relationships; I was just trying to show all possibilities for each table which means several are repeats. If I am thinking about this correctly, which might be my problem, the smaller list would be: Each client can have multiple ranks. Each client can have multiple items. Each item can have multiple ranks. Each item can have ONE client. Let me try a different example. Imagine a database that is designed for one teacher. The teacher has X number of classes that she teaches. The teacher creates test items for each class, however, some of her classes are similar so the test item might apply to multiple classes. Instead of writing the same item multiple times and assigning it to each class, she just wants to write the same item once and state which classes she could use it for. (Queries will be used to determine what items are available when she goes to create the actual test for the specific class). Then, instead of having one database for each teacher, I want to have one database for all teachers at the same school because they are all doing the same thing. It just happens, that across teachers, some of the classes now have the same name even though they are different classes. (client = teacher; rank = class; item = item) So, I want to have a form where I can pick the client, write the items for that client, and then say what rank(s) the test item is valid for. My problem is this: I have the form that shows the client, and a subform to write that client's test items. I can’t figure out how to assign each item to its corresponding rank. I assumed I needed a “rank” subform inside of the “item” subform, but when I do that it shows me every rank for all clients not just the ranks for the particular client I am working on. I haven’t been able to figure out how to make this work, so I thought my original table and relationship design might be wrong. "KARL DEWEY" wrote: I am not sure you stated the relationships correctly (The chicken and the egg). Each client can have multiple ranks. Can the client have many ranks at the same time or at different time? Each rank can have multiple clients. What gets who - Do you mean a given rank can apply to many clients? If so, that is covered above. This is like right arms and people - a person can not have multiple right arms but people as a whole do have many right arms. But a person (single eintity) can not have many right arms. The same for rank and items. Each item can have ONE client. I do not see how an item can have people. For a given item you can show all the people that have them. -- Build a little, test a little. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#7
|
|||
|
|||
Need help with several many to many relationships
Thank you for the suggestion. I had basically already done everything you
listed except the second form/subform with the classes/class item. I have tried out your suggestion for the second form/subform, and it works, but it is not exactly what I am looking for. Mostly because there will be a very large numbers of items to deal with at any given time (say 500). So I am imagining having a set of 500 newly created items. Per your form, I pick class #1, and go through and pick which of the 500 items apply to class #1(say it is all 500). Then I pick class #2 and pick the corresponding items (say 300), etc. I think this approach is too cumbersome. I have created a different form/subform - teachers/items. This is where i see the teacher, and then create the new item. Can i somehow incorporate the class into this subform? That way, I can pick the classes each item applies to as I create each new item. This is the part where I said I was having problems, because I have already done this, but my combo box lets me choose from all classes in the table, not just those that correspond to the teacher in the main form. I think that means I need to somehow link that nested subform to the teacher_class table (instead of the class table), but I'm having trouble getting everything to work together correctly. Thanks for your help, I appreciate it. "KARL DEWEY" wrote: Here is a start -- Teachers – TeachID – Autonumber – primary key LName FName Etc. Classes -- ClassID - Autonumber – primary key Grade_Level Subject Items -- ItemID - Autonumber – primary key Topic Teach_Class – TeachClassID - Autonumber – primary key TeachID – number – long integer – foreign key ClassID – number – long integer – foreign key Period Class_Item – ClassItemID - Autonumber – primary key ClassID – number – long integer – foreign key ItemID - number – long integer – foreign key Form/Subform – Teacher/ Teach_Class with combo in subform to select Class. Master/Child lick set using TeachID. Form/Subform – Classes/ Class_Item with combo in subform to select Item. Master/Child lick set using ClassID. -- Build a little, test a little. "shoup" wrote: Yes, I think you are right about the relationships; I was just trying to show all possibilities for each table which means several are repeats. If I am thinking about this correctly, which might be my problem, the smaller list would be: Each client can have multiple ranks. Each client can have multiple items. Each item can have multiple ranks. Each item can have ONE client. Let me try a different example. Imagine a database that is designed for one teacher. The teacher has X number of classes that she teaches. The teacher creates test items for each class, however, some of her classes are similar so the test item might apply to multiple classes. Instead of writing the same item multiple times and assigning it to each class, she just wants to write the same item once and state which classes she could use it for. (Queries will be used to determine what items are available when she goes to create the actual test for the specific class). Then, instead of having one database for each teacher, I want to have one database for all teachers at the same school because they are all doing the same thing. It just happens, that across teachers, some of the classes now have the same name even though they are different classes. (client = teacher; rank = class; item = item) So, I want to have a form where I can pick the client, write the items for that client, and then say what rank(s) the test item is valid for. My problem is this: I have the form that shows the client, and a subform to write that client's test items. I can’t figure out how to assign each item to its corresponding rank. I assumed I needed a “rank” subform inside of the “item” subform, but when I do that it shows me every rank for all clients not just the ranks for the particular client I am working on. I haven’t been able to figure out how to make this work, so I thought my original table and relationship design might be wrong. "KARL DEWEY" wrote: I am not sure you stated the relationships correctly (The chicken and the egg). Each client can have multiple ranks. Can the client have many ranks at the same time or at different time? Each rank can have multiple clients. What gets who - Do you mean a given rank can apply to many clients? If so, that is covered above. This is like right arms and people - a person can not have multiple right arms but people as a whole do have many right arms. But a person (single eintity) can not have many right arms. The same for rank and items. Each item can have ONE client. I do not see how an item can have people. For a given item you can show all the people that have them. -- Build a little, test a little. "shoup" wrote: Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
#8
|
|||
|
|||
Need help with several many to many relationships
Please excust the directness which is an attempt to be helpful. You should start by methodically defining your entities and the relationships between them, and create the structure (tables and relationships) that implements that. Befoore you do anything else. Without that I think that you will end up going in circles, and have bee hopping all over the place in your posts. While you did not do that for your actual application, you did sort of do it for your teacher / class analogy. Then Albert defined a structure (which is a FOUNDATION for everything else) for what you described. The you changed it (by adding an additional relationship (direct link from teachers to items) not previously defined which would invalidate that structure. And then you are putting the cart before the horse in defining forms and subforms that require knowledge of (teacher-item) realtionships that don't even exist in the structure, more in your entity/relationship definition that Alber designed that structure for. - - - If your DB is design to record relationships between individual items and classes, then it is inevitable that somebody is going to have to enter those relationships. So, if you want to pick 200 particular items to go onto a test, someone is goin got have to record those 200 relationships, the computer/applicaiton can't read their mind. Unless there is some other generalization (e.g. blocks of items) that you haven't told us about. - - - |
#9
|
|||
|
|||
Need help with several many to many relationships
You can reinvent the wheel ... or you can take a look at a Survey database
Duane H. put together ... http://www.rogersaccesslibrary.com/O...p#Hookom,Duane Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or psuedocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "shoup" wrote in message ... Hi, and thanks for your help. I am creating a database for creating multiple choice tests. I keep getting stumped because there are several many to many relationships. Right now my basic design includes three tables: clients (clientID, clientname), ranks (rankID, rankname), and items (itemID, itemstem). Each client can have multiple ranks. Each client can have multiple items. Each rank can have multiple clients. Each rank can have multiple items. Each item can have multiple ranks. Each item can have ONE client. I can do this if i take out one of the variables. For instance, I can create my tables and everything works when I just do just clients and items, but I can't seem to get ranks in there correctly too. My goal was to have a form where you pick the client, then create the multiple choice question, and then use a drop down to select which ranks the item applies to. However, I can't get that to work so I am going back to the drawing board to figure out if there is a better way to do this. How can I make this work? I assume it is something obvious and I am just not seeing the solution. Thank you! |
Thread Tools | |
Display Modes | |
|
|