count empty fields and arrange in week format (1 Viewer)

kobiashi · Sep 23, 2018

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

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

Ranman256 · Sep 23, 2018

Q1, get all cars washed for the weeks:
chevy12, week1
camry, week2

Q2, create a list of ALL cars and ALL weeks. (in yr range)
the query has the car table and the weeks query DO NOT JOIN THEM.
this will create a list of every car for every week.

Q3, create an OUTER join to find all the missing cars.
join Q1 to Q2 , dbl-click the join line, set to:
ALL records in Q2, SOME recs in Q1

bring in Car# and Week# from both 'tables', View query
if you want to filter more: under criteria for Q1.Car#, set criteria = null

this will show you all cars not listed as washed for that week

kobiashi · Sep 23, 2018

Ranman256 said:
Q1, get all cars washed for the weeks:
chevy12, week1
camry, week2

Q2, create a list of ALL cars and ALL weeks. (in yr range)
the query has the car table and the weeks query DO NOT JOIN THEM.
this will create a list of every car for every week.

Q3, create an OUTER join to find all the missing cars.
join Q1 to Q2 , dbl-click the join line, set to:
ALL records in Q2, SOME recs in Q1

bring in Car# and Week# from both 'tables', View query
if you want to filter more: under criteria for Q1.Car#, set criteria = null

this will show you all cars not listed as washed for that week

Thanks ranmam, I had just finished when you posted, I had pretty.mich done the second thing.

Sent from my ONEPLUS A6003 using Tapatalk

count empty fields and arrange in week format (1 Viewer)

kobiashi

Registered User.

Ranman256

Well-known member

kobiashi

Registered User.

Similar threads

Users who are viewing this thread