Displaying name instead of associated ID on report 1

[KerryL]

I have a table (tblStaff) in my db that contains the following fields:

StaffID (key)
StaffFName
StaffLName
StaffEmail
Staff Unit
Supv (Y/N)
SupvNumber

The Y/N field indicates whether the person is also a supervisor. If "Y" he is a supv for other staff members in the table, in which case their record would show his StaffID number in their SupvNumber field. For instance:

Bob Smith = StaffID #101, and is a supervisor
Jim Leeds = StaffID #175, and his supervisor is Bob Smith, so the SupvNumber field for Leeds = 101.


The problem I'm having is when I create a report that lists all staff members, I can't figure out how to show the NAME of each person's supervisor instead of the number that exists in the field. I want the number to relate to the correct staff member so the supervisor name shows on the report, not the supvervisor number. (Hope this makes sense.)

So if I create a report called rptStaff with tblStaff as the record source, and the report contains all the data for each record in the table, how can I show each staff member's supervisor by name instead of by StaffID number?

Or do I need to create a query to use as the report's record source? I so, how do I translate each person's SupvNumber into the corresponding staff member's name?

Thanks in advance for your help.

kerry

 

[LarryDeLaruelle]

Kerry:

In the query behind the report add a second iteration of the tblStaff. Link the SupvNumber from the first iteration to StaffID in the second iteration. Add staff name fields from the second iteration.

If you are not using a query behind the report, create one as described above.

This should give you what you need.


Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[KerryL]

Worked like a charm.
Thank you Larry!

 

[LarryDeLaruelle]

Glad I was able to help.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org