Building a rating scheme query

[jandr272]

I currently have 2 tables in a database that I'm trying to build a query off of. The first table lists personnel & their position #s, and the second table has a column for "rated person", "rater", "senior rater" and "reviewer" which are all part of our annual evaluations. I'm trying to program the query to display the names of the person attached to a position #, but can only get the query to return the actual position #s.

I'm using the table to assign each position # appropriately, i.e. position # 10202 is rated by 10103 and senior rated by 10101; I do this with the lookup data type. Once each position # is assigned, I'd like the query to display the name of the personnel instead of the position #, i.e. when queried, it would return under the rater column JOHN SMITH instead of 10202, and in the senior rater column JOHN JONES instead of 10103, etc.

 

[plog]

You provided neither sample data, nor field and table names; so let me make some up. From here on "People" will be the table with all the name data and "Ratings" will be the table with all the ratings data. For fields, I'll just use brackets to signify them.

Start a query with Ratings, then bring in an instance of People and link it to the [rated person] field in Ratings. Bring down the [Name] field from People and then change it to this:

RatedPerson: [Name]

Then bring in another instance of People and link it to the [rater] field in Ratings. This instance of People will show up as People_1. From it, bring down the [Name] field and then change it to this:

Rater: [Name]

Continue this process of bringing in an instance of [People] for every name you need.