Hi,
I have two tables as the below:
Equipment: EquipmentID (Auto Number), Equipment Name, Purchased Date.
Shared : Category(primary, Secondary), StaffName, Department, Assigned Date, and EquipmentID(as foreign key)
One Equipment may have couple of staffs shared with it as the below I explained:
EquipmentID; EquipmentName; PurchasedDate
1; Main Printer ; 1/1/2010.
Category; staffName; Department; AssignedDate; EquipmentID.
Primary; Jeff Word; IT; 1/1/2010; 1
Secondary; Mary Little; Marketing; 2/1/2010; 1
Secondary; Lisa Holt; Data Entry; 1/1/2010; 1
Primary; Bob Big; Tech; 2/3/2010; 1
Staff with Primary means this staff is responsible for maintaining this equipment.
Staff with Secondary means this staff shared equipment, but don’t need to do any maintenance job. The report will search or sorted by a staff name. if I search Mary Little, the data under the report will show her as a Secondary staff. In that case, I also want to see who is/are primary staff(s) shared equipment with Mary.
Can I do that in a query, something like below:
SELECT tbl_Equipment.EquipmentName, tbl_Equipment.PurchasedDate, Shared.Staffname, Shared.Department, Shared.Category,
Case
When (Shared.Category) = 'Secondary' then (select Shared.Sname
from Shared
where Shared.Category = 'Primary')
else ' '
End as SharedStaff
FROM tbl_Equipment INNER JOIN Shared ON tbl_Equipment.ID = Shared.ID
ORDER BY tbl_Equipment EquipmentName;
Anyone can help, and tell me how to do that?
Thank you very much for your time and help.
I have two tables as the below:
Equipment: EquipmentID (Auto Number), Equipment Name, Purchased Date.
Shared : Category(primary, Secondary), StaffName, Department, Assigned Date, and EquipmentID(as foreign key)
One Equipment may have couple of staffs shared with it as the below I explained:
EquipmentID; EquipmentName; PurchasedDate
1; Main Printer ; 1/1/2010.
Category; staffName; Department; AssignedDate; EquipmentID.
Primary; Jeff Word; IT; 1/1/2010; 1
Secondary; Mary Little; Marketing; 2/1/2010; 1
Secondary; Lisa Holt; Data Entry; 1/1/2010; 1
Primary; Bob Big; Tech; 2/3/2010; 1
Staff with Primary means this staff is responsible for maintaining this equipment.
Staff with Secondary means this staff shared equipment, but don’t need to do any maintenance job. The report will search or sorted by a staff name. if I search Mary Little, the data under the report will show her as a Secondary staff. In that case, I also want to see who is/are primary staff(s) shared equipment with Mary.
Can I do that in a query, something like below:
SELECT tbl_Equipment.EquipmentName, tbl_Equipment.PurchasedDate, Shared.Staffname, Shared.Department, Shared.Category,
Case
When (Shared.Category) = 'Secondary' then (select Shared.Sname
from Shared
where Shared.Category = 'Primary')
else ' '
End as SharedStaff
FROM tbl_Equipment INNER JOIN Shared ON tbl_Equipment.ID = Shared.ID
ORDER BY tbl_Equipment EquipmentName;
Anyone can help, and tell me how to do that?
Thank you very much for your time and help.
Last edited:
