In my Access database I have three tables, 'mission', 'missionAssets', and 'equipment'. Each mission has a start time and an end time, and can have many assets. Each asset entry has correlating missionID from the mission table, an assetName that correlates to an entry in the equipment table, and a quantity being used on that particular mission.
Mission:
missionID (PK) start end destination description
1 10/17/2015 6:00:00 AM 10/17/2015 5:00:00 PM
2 10/18/2015 6:00:00 AM 10/18/2015 5:00:00 PM
3 10/19/2015 6:00:00 AM 10/19/2015 5:00:00 PM
MissionAsset:
assetID (PK) missionID (FK) asset (FK) quantity description
1 1 M1088 2
2 1 10K 3
3 2 M1088 1
4 3 M1120 1
Equipment:
AssetName (PK) OnHand Inoperable
M1088 10 2
10K 8 1
M1120 4 0
My table joins are correct for the query. I want to make a few sums that are filtered by time periods: in example, what quantity of each asset will be used 24 hours from now? I've tried using:
Sum(CASE WHEN mission.start <= (Now()+1) AND (Now()+1) <= mission.end THEN quantity ELSE 0 END)
to no avail; Access trips a 'syntax error (missing operator)'. I referenced SQL sum with condition to find documentation on the searched CASE expression (https://msdn.microsoft.com/en-us/library/ms181765.aspx). I also referenced Is it possible to specify condition in Count()? where a simple CASE expression is used.
