Need help with several many to many relationships

  • Thread starter Thread starter shoup
  • Start date Start date
S

shoup

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!
 
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.
 
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.
 
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?
 
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.
 
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.
 
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.
 
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.

- - -
 
You can reinvent the wheel ... or you can take a look at a Survey database
Duane H. put together ...

http://www.rogersaccesslibrary.com/OtherLibraries.asp#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.
 
Back
Top