table / query design problem

[rob]

All

To easier explain my problem I'm making up the following example:

Suppose you have a Table of Referees (PKID, LName, FName)
- Each game you play requires two referees (Head Referee and Assistant
Referee); any ref can hold either position, but it is important to know who
held what position.

Suppose you have a Game table (PKID, Head Ref, Assistant Ref, Game, date,
etc)

I add the PKID from table Referees into the Game Table (Head Ref, Assistant
Ref), but when I try to view the info in a query, it does not work.

In a nutshell, the game table needs two records from the referee table....

I got around this problem by creating two referee tables - but that means
duplicating records (and updating two tables).

What am I doing wrong? I cannot find any examples online or in any of my
old programming/database books.

Any help would be appreciated.

Rob

 

[Tim Ferguson]

In a nutshell, the game table needs two records from the referee
table....

In the relationships window, you Add the Referees table twice. The second
table will appear as Referees_1 but don't take any notice of that, it's
just for identification purposes. It's still the same table.

Now drag the Referees.IDField onto the Games.HeadRef field, and then the
Referees_1.IDField onto the Games.AssistantRef field to make the second
relationship.

Easy-peasy!

Hope it helps


Tim F

 

[Duane Hookom]

Tim's answer meets your needs if you will always only ever need two
referees. Another solution is to create a related table of game officials.
This would allow you to store Head Referee, Assistant Referee, Scorer, Line
Judge, or whatever.

 

[rob]

Thanks folks,

I tried it and it works perfect!

Now I just have to reaccomplich queries/forms and a lot of VBA.

I didn't want the person who maintains the database after I create it to
think I'm a moron!
- I'm just getting reaquainted with databases/programming/VBA after a long
absence

I appreciate the assistance and will lurk on this newsgroup for further tips

Rob

 

[Tim Ferguson]

I didn't want the person who maintains the database after I create it to
think I'm a moron!

See the thread up there somewhere about naming conventions ("proper way to
name objects") for what is developing into a discussion about
documentation. The only way to avoid being called a plonker by your
successors[1] is copious and clear documentation. For every object explain
what it is, how it works, why you did it that way, what it does not do or
what it does wrong, how you plan to develop it in the future, etc.

The great thing is that it's not only other people who will benefit from
this, but you yourself in six months' time when you have forgotten all the
drama of the original build!

[1] funny thing that: your successors never look at your successes...

All the best


Tim F