2 fields relating to a same table...

[Boorgy]

Hi folks, hope somebody can help...

I got a table, nights, in which 2 fields, shift1 and shift2, both correspond to an entry in another table, employees. Shift1 and shift2 contain an employeeID which is used to relate to an employee in the employees table.
Question is, how do I write an SQL query that returns both employees' names? I need to link the tables using a join (inner or left or whatever else...).
I can write a statement like:

Select nights.date, employees.name From nights Inner Join employees On nights.shift1=employees.ID Or nights.shift2=employees.ID

But that only gets me the name of the first employee

Any Idea, Anyone?

bourgui

 

[JoeMiller]

Your current design won't work because the system does not know which name to show the way you are choosing it. How does it know whether to show the name for shift 1 or shift 2. If you make your Shift1 and Shift2 fields into combo boxes so they look up their value in employees table you'll be ok. Go into design view of your table, in the data type of each click lookup wizard and have the wizard look at the employee table, store the id but show Name. Then you'll get what you're after.

I would suggest that you change the structure of this database though to make it better for you to maintain. You should have a table that stores the same data for shift1 and shift2 in a separate line for each and an identifier to tie them to the main record (a day most likely). In this table you'll have a column to specify whether your entering data for Shift1 or Shift2, an employee name, and maybe hours, or the like. This would make a more correct database and make reporting much easier for you.

MY $0.02
Joe Miller
joe.miller@flotech.net

 

[Boorgy]

Thanks Joe, that should do the trick...
The thing is, I'm not allowed to change the database, the whole system relies on it... I've just been asked to add this new little feature.
Anyway, thanks again

bourgui