VLLOKUP displaying multiple cells

[hildab]

Hello,

I am creating a staffing database and currently have two tables:
Table 1 - Staff - which contains staff information ie. First Name, Last Name, Other Names

Table 2 - Staff Work History - which displays the shifts worked by each staff member.

In Table 2 - I have "Staff Name" which is populated using VLOOKUP and searching for the Staff Name from Table 1 - First Name, Last Name, Other Name.

I want Table 2 column "Staff Name" to display all parts of the name - not just display then when on the dropdown when you select the staff person. Currently only the First Name is displayed once you have chosen the staff person.

Can you help!

Thanks

Hilda

 

[pbaldy]

You can either use this:

http://www.baldyweb.com/Autofill.htm

or use a query as the rowsource of the combo and have a field with the concatenated name as the displayed field.

 

[Privateer]

Hilda, your issue is with table design. The staff table should have StaffID, FirstName, LastName. The Shift table should have ShiftID, ShiftName. Then in a third table called Work, you have WorkID, StaffID, ShiftID. This way work 1 done by person 1 worked shift 2 and work 2 done by person 1 worked shift 3. Then in a query, you use all three tables and include the names to get work 1 by John worked afternoon and work 2 by John worked evening. Hope that helps