RE: Need help with several many to many relationships

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages