hi
i am trying to count the number of empty fields in a column and return a value per week
so i have three tables called Table_ ENG_MCU_VehicleWash_M2M, Table_ENG_FP_WorkingPeriod and Tbale_Vehicles
1. Table_ ENG_MCU_VehicleWash_M2M table consists of
VehicleWash_IDK
Vehicle_IDFK
WahDate
workingPeriod_IDFK
timeStamp
2. Table_ENG_FP_WorkingPeriod table consists of
WorkingPeriod_ID
DateCreated
PeriodType_IDFK
PeriodStartDate
PeriodEndDate
PeriodNumber
TimeStamp
3. Table_Vehicles Table consists of
Vehicle_ID
VehicleNumber
Vehicletype
tables 1 and 2 are linked from WorkingPeriod_ID to WorkingPeriod_IDFK
tables 1 and 3 are linked from Vehicles_ID to Vehicles_IDFK
so my query currently looks like
every vehicle is washed 4 times a month
what im trying to do is return the count of empty cells in weeks (ie Week 1, Week2, Week3, Week4) where the wash date falls in between PeriodStartDate and PeriodEndDate.
This would return me the total number of vehicles that have not been washed for the respective weeks.
any help would be very much appreciated
i am trying to count the number of empty fields in a column and return a value per week
so i have three tables called Table_ ENG_MCU_VehicleWash_M2M, Table_ENG_FP_WorkingPeriod and Tbale_Vehicles
1. Table_ ENG_MCU_VehicleWash_M2M table consists of
VehicleWash_IDK
Vehicle_IDFK
WahDate
workingPeriod_IDFK
timeStamp
2. Table_ENG_FP_WorkingPeriod table consists of
WorkingPeriod_ID
DateCreated
PeriodType_IDFK
PeriodStartDate
PeriodEndDate
PeriodNumber
TimeStamp
3. Table_Vehicles Table consists of
Vehicle_ID
VehicleNumber
Vehicletype
tables 1 and 2 are linked from WorkingPeriod_ID to WorkingPeriod_IDFK
tables 1 and 3 are linked from Vehicles_ID to Vehicles_IDFK
so my query currently looks like
Code:
SELECT Table_ENG_MCU_VehicleWash_M2M.VehicleWash_ID, "Week " & ((DateDiff("d",[PeriodStartDate],[WashDate])-1)\7+1) AS Expr1, Count(*) AS CountOfWashDate
FROM Table_Vehicles LEFT JOIN (Table_ENG_FP_WorkingPeriods RIGHT JOIN Table_ENG_MCU_VehicleWash_M2M ON Table_ENG_FP_WorkingPeriods.WorkingPeriod_ID = Table_ENG_MCU_VehicleWash_M2M.WorkingPeriod_IDFK) ON Table_Vehicles.Vehicle_ID = Table_ENG_MCU_VehicleWash_M2M.Vehicle_IDFK
WHERE (((Table_ENG_MCU_VehicleWash_M2M.WashDate) Is Null))
GROUP BY Table_ENG_MCU_VehicleWash_M2M.VehicleWash_ID, "Week " & ((DateDiff("d",[PeriodStartDate],[WashDate])-1)\7+1);
every vehicle is washed 4 times a month
what im trying to do is return the count of empty cells in weeks (ie Week 1, Week2, Week3, Week4) where the wash date falls in between PeriodStartDate and PeriodEndDate.
This would return me the total number of vehicles that have not been washed for the respective weeks.
any help would be very much appreciated
Last edited: