RE: Need help with several many to many relationships

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

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