Table Structure / Relationship

[MediaDoc]

Hello,

I have a sticky problem.

Project deals with staff evaluations, and I have a system working, albeit in a frustrating / inefficient way.

Problem:

10 staff members each have to fill out an evaluation (online) for the all other staff members.

I have 2 tables currently.

tblUsers
UserID
FirstName
LastName

tblAnswers
ForID
ByID
Q1
Q2
...etc...

Lets put some names to this.

Max, Charlie and Vern

I would like to run a query that tells me who has and HAS NOT filled out an evaluation for everyone else, looking like this.

Staff Member Evaluation ON Status
Max Charlie Complete
Vern Incomplete
Charlie Max Incomplete
Vern Incomplete
Vern Max Complete
Charlie Complete

Right now I get only those who have filled out evals (i.e. has a record in the tblAnswers). I have tried LEFT OUTER JOIN, still only get those that have a record in tblAnswers.

Im thinking my table structure might be screwed up. Do I need 3 tables?

tblUsers
UserID
FirstName
LastName

tblAnswers
AnswerID
ForID (evaluation on what user, in this case ForID = UserID)
Q1
Q2
... etc...

tblLINK
UserID (UserID of person DOING the evaluation)
AnswerID

Relationships would be tblUser 1 --- MANY tblLINK MANY ----1 tblAnswers.

Ughhh this gives me a headache!

Thanks for any suggestions.

Matts

 

[RichMorrison]

Something like this will probably work:

1) Make a query with *two* copies of tblUser, Access will prefix the second copy with "1" or something. DO NOT join the 2 copies.
2) Select UserID from tblUser As ForID, select UserID from tblUser1 As ToID.
3) Add a Where clause ForID <> ToID

This will return the product of the two copies of tblUser.
Save this query as "Q1"

Make a second query that includes Q1 and tblAnswer.
Left Join Q1.ForID to tblAnswer.ForID
Left Join Q1.ToID to tblAnswer.ByID

Put in 2 Where clauses on different lines so you get an "or".
Under ForID in Criteria, enter Is Null
Under ByID in Criteria, enter Is Null

Save this as "Q2"

When you run Q2, you should get a result set of any To/For pair that does not exist in tblAnswer.

Should work.

RichM