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
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
ASKER
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
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
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
- 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
you have to join with the supervisors table with the key supervisor_a_id
ASKER
The supervisors are in the same table as the salespeople.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
If you're in the U.S., hope you have a great Thanksgiving! If not, hope you have a great regular week!
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
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
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;