Lookups and Assignments

[Robert]

First, a thanks to those who have pointed out some problems with the Lookup
Wizard. I will now be creating my table relationships manually and employing
list or combo boxes in the forms. Interestingly, in most textbooks for 1st,
2nd, 3rd level ... there seems to be no mention of the "evils" and rather
implies encouragement for use of the "Wizard".

Secondly, I would appreciate suggestions on how to handle "assignments".
I have
tblGames;tblEmployees;tblLocations;tblSupervisors;tblSiteCoordinators;tblInvoices;and
tblRoles. Supervisors and site coordinators can be assigned to more than one
location. Employees can be assigned to more than one game. Employees can
fullfill more than one role in each game.

I hope this is enough information for someone to give me a suggestion on how
to efficiently approach this. If not, I can supply more information.
Thanks, in advance.

 

[Klatuu]

What you need is a copy of Database Design for Mear Mortals. Amazon has it
as well as other book retailers. It is a good common sense approach to
relational database design that would help you understand all this.

As to your question. Here are the basics. There are two legitimate
relationships.
One to Many and Many to One.

One to Many:
For example, One mother may have many children, but one child may not have
more than one mother.

Many to One:
Many children will have one mother. They will not all have the same mother,
but each will have one mother.

But, we can have a situation where there is a Many to Many relationship.
Let's take your example:
Employees can be assigned to more than one game.

That mean you have multipe employees and there are multile games. We can't
put a whole bunch of fields in an employee record to show what games they are
assigned to nor can we put fields in a games record to show which employees
are assigned.

(well, we could, but that is a spreadsheet, not a relational database) It
would be very ineffiecient, difficult to locate information, and impose
limits because of the number of fields defined.

So the solution is a Junction table. It resolves the Many to Many by
creating a many to one and a one to many relationship. You would have a
table named something like tblEmployeeGames. It would need a minimum of two
fields. One to carry the primary key field value of the employee assigned to
the game and the other to carry the value of the primary key field of the
game the employee is assigned to. this table, then will tell you all
employees assigned to a specific game and it will tell you all the games an
employee is assigned to.

So for example we have Employees:

3 Fred
7 Ethel
9 Jack

And for games we have

15 Base Javelin
23 Downhill Wrestling
47 Footpool

Ethel and Jack are assigned to Base Javelin
Fred and Jack are assigned to Downhill Wrestling
Fred and Ether are assigned to Footpool

The table would look like this:

EmpID GameID
3 23
3 47
7 15
7 47
9 15
9 23