Using an MS Access database with the following design:
Tables
Equipment, Employees, CreditCard
Fields
Equipment: ID, PrimaryEmployee, SecondaryEmployee
Employees: ID, CreditCard
CreditCard: ID, Number, Pin
So, a piece of equipment can have two different employees assigned to it. Each employee can have a CreditCard assigned to them, or not at all.
Based on the ID of a piece of Equipment:
- If the piece of equipment has a SecondaryEmployeeID set, and that corresponding Employee has a CreditCard set, then return the value of that CreditCard.
- Else If the piece of equipment has a PrimaryEmployeeID set, and that corresponding Employee has a CreditCard set, then return the value of that CreditCard.
I also have to pull the employee's name from the Employees table in the same query, and thus, I was using a LEFT JOIN before, which seemed to make it impossible to do this in my knowledge.
EDITED
My current query attempt:
SELECT
Equipment.ID,
Equipment.PrimaryEmployee,
Equipment.SecondaryEmployee,
Employees.CreditCard,
CreditCard.Pin
FROM
(Equipment
LEFT JOIN Employees ON Equipment.PrimaryEmployee = Employees.ID
)
LEFT JOIN CreditCard ON Employees.CreditCard = CreditCard.ID
WHERE (((Equipment.EquipmentType)=1))
I removed some unrelated fields from the query to hopefully improve readability, and simplify the area I'm having troubles.
This is currently generating a result like:
ID PrimaryEmployee SecondaryEmployee CreditCard Pin
--------------------------------------------------------------------
1 John Doe Jack Smith 1234567890 1234
2 Bubba Smith Ryan Howard 2345678901 2345
The problem is that it only matches on the PrimaryEmployee. I need it to check the SecondaryEmployee first, and if that doesn't have a match or credit card set, then match on the PrimaryEmployee. If neither match, then return Null or '-'.
Let me know if additional information is still required.