Relationship problems (within Access)

[Piers Clinton-Tarestad]

I have two tables at the moment one of which has the following fields

TABLE 1
Appraisee
Appraiser

TABLE 2
Staff Name
Staff Grade

Basically I want to establish a one to many relationship between the Staff
Name and Appraisee and also the Staff Name and Appraiser fields so I can run
a query which looks up the the Staff Grade for the Appraisee and Appraiser

I tried to establish two relationships (enforcing data integrity) and
designing a query which basically lists

Appraisee Staff Grade(for Appraisee) Appraiser Staff Grade (for Appraiser)

but all the query did was show the record where the Appraisee was the same
person as the appraisee

I have absolutely no idea how to resolve this-does anyone else?

 

[John Vinson]

I have two tables at the moment one of which has the following fields

TABLE 1
Appraisee
Appraiser

TABLE 2
Staff Name
Staff Grade

Basically I want to establish a one to many relationship between the Staff
Name and Appraisee and also the Staff Name and Appraiser fields so I can run
a query which looks up the the Staff Grade for the Appraisee and Appraiser

I tried to establish two relationships (enforcing data integrity) and
designing a query which basically lists

In both cases you'll need to add Table2 to the window TWICE. In one,
join the unique StaffID (which you don't show but which is NOT
optional; names are not unique or stable and therefore do not make
good unique keys) to the Appraisee field; join the other table's ID to
the Appraiser.

Appraisee Staff Grade(for Appraisee) Appraiser Staff Grade (for Appraiser)

but all the query did was show the record where the Appraisee was the same
person as the appraisee

Again, add Table2 *twice*. You'll get an alias name - tblStaff (do
change the table name, Table1 and Table2 are meaningless) and
tblStaff_1. If you reference tblStaff.Grade you'll get one person, or
tblStaff_1.Grade for the other.