In MS Access, how can I change a key # to the name?

Hello,

This question is probably really simple for someone who uses Access regularly, however, my experience level is pretty basic.  

I have created a query to pull all of our salespeople's production for a period of time.  The table I'm referencing contains:
- Key #
- Sales people's "Name" (as well as supervisor names)
- Supervisor key #

My question is:  In the query, how can I get the Supervisor's name to show rather than their key #?

For example:
Key   Name    Super #
1  Tom Jones  4
2  E Kline          4
3  J Doe             4
4  S Meyer       7

The query now would pull:   Tom Jones, 4, rather than Tom Jones, S Meyer.

Thank you,

Pat
FFNStaffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You need to join the table to itself.  This is known as a self-referencing relationship since a foreign key in the table refers to the table's own primary key.

Select tblPersons.*, tblPersons_1.LastName As SupervisorLastName, tblPersons_1.FirstName As SupervisorFirstName
From tblPersons Left Join tblPersons as tblPersons_1 On tblPersons.SupervisorID = tblPersons_1.PersonID;
0
FFNStaffAuthor Commented:
Pat, thanks for responding.

I don't understand what you telling me to do.  Am I doing this is the query grid under Field or Table?  Am I building this?

If it would help, I'm using the table with the salespeople's information, which is named Salespeople.
The columns are named:
S_ID,
S_LNAME,
S_SUPERVISOR_A_ID
0
Nick67Commented:
The table I'm referencing contains:
 - Key #
 - Sales people's "Name" (as well as supervisor names)
 - Supervisor key #


Now, you want the supervisor's name.  It isn't in this table, right?
So in the query designer, you need to add the table containing the names.
You then need at create a join by dragging the supervisor's key # in the table you originally referenced to the table you just added (Access may have been smart enough to create this auto-magically if both keys were named the same, conversely Access may have created an unwanted join if the key name in the added table matched a name in the originally referenced table that we presently do NOT want to join on)
Then add the supervisors name field from the newly added table to the grid.

Example attached
sales.mdb
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David Johnson, CD, MVPOwnerCommented:
you have to join with the supervisors table with the key supervisor_a_id
0
FFNStaffAuthor Commented:
The supervisors are in the same table as the salespeople.
0
FFNStaffAuthor Commented:
the Supervisors are treated as salespeople in the same tables.  They are not necessarily identified as supervisors.  Their ID's used in the salespeople's columns.
0
Nick67Commented:
Ok,

The sample is updated to account for that.
Have a look.

You'll add the people table to the query twice (yes! you can do that)
The second instance S_ID in the second people table to Super # in the table you originally referenced
sales-v1.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FFNStaffAuthor Commented:
Nick67, thank you!!  Exactly what I needed.

If you're in the U.S., hope you have a great Thanksgiving!  If not, hope you have a great regular week!
0
Nick67Commented:
Up in the Great White North.
Not as white as Buffalo NY, but there's a foot of snow on the ground and it's 0º F at the moment.
Of course, that's par for the course for late November north of 55º latitude.
Have great Thanksgiving.
Nick67
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.