Select fields (with Join) based on field values
Posted on 2014-10-02
Hopefully I can explain this properly.
I have a service scheduling form that we want to try to use a "units" field on to (hopefully) more efficiently schedule crews.
Table 1- Work Orders - Fields:
W.WorkType (Install, Upgrade, Repair)
Table 2- Locations
L.InstallUnits (Value could be different at different locations)
L.UpgradeUnits (Value could be different at different locations)
L.RepairUnits (Value could be different at different locations)
The Form / Report needs to display
Location, WorkType, Units (ONCE, based on work type), Crew
The logic would be something like:
SELECT W.LocationID, W.WorkType, (If W.WorkType = Upgrade Then SELECT L.UpgradeUnits ELSEIF W.WorkType = Install Then SELECT L.InstallUnits ELSEIF W.WorkType=Repair THEN SELECT L.RepairUnits) AS Units FROM WorkOrders W INNER JOIN Locations L ON L.LocationID = W.LocationID
Problem is, I'm not sure of the Access compatible SQL statements to use (or if its even possible). Specifically, in the stuff in bold - hopefully that's clear enough what I need, but the exact syntax that will produce the result in Access is what I need (alternatively, I MAY be able to convert this into a SQL view - the backend is SQL, but the data currently (without Units) is an Access Query using the backend SQL tables.