hi
i am trying to count records based on a date and display the count in week format ie, Week1, Week2, Week3, Week4
i had originally done this with a series of queries into a crosstab query, which worked when there was data to count in all four weeks, but if there was no dates in week 3 or week 4, there was no outcome in the crosstab query, so i need a way to fix the column headings as Week1, Week2, Week3, Week4
here is what i had done, maybe it can be fettled with to work
tables
so i used 4 queries to do this
Query 1
Query 2
Query 3
Query 4 (CrossTab)
i am trying to count records based on a date and display the count in week format ie, Week1, Week2, Week3, Week4
i had originally done this with a series of queries into a crosstab query, which worked when there was data to count in all four weeks, but if there was no dates in week 3 or week 4, there was no outcome in the crosstab query, so i need a way to fix the column headings as Week1, Week2, Week3, Week4
here is what i had done, maybe it can be fettled with to work
tables
Code:
1. Table_ENG_FP_WorkingPeriods
WorkingPeriod_ID
DateCreated
PeriodType
PeriodStartDate
PeriodEndDat
PeriodNumber
TimeStamp
2. Table_ENG_MCU_VehicleWash_M2M
VehicleWash_ID
Vehicle_IDFK
WashDate
EndWash_IDFK
WorkingPeriod_IDFK
TimeStamp
3. Table_Vehicle
Vehicle_ID
VehicleNumber
VehicleTypeso i used 4 queries to do this
Query 1
Code:
SELECT Table_ENG_MCU_VehicleWash_M2M.VehicleWash_ID, "Week " & ((DateDiff("d",[PeriodStartDate],[WashDate])-1)\7+1) AS Expr1, Table_Vehicles.VehicleNumber, Table_ENG_FP_WorkingPeriods.WorkingPeriod_ID
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
GROUP BY Table_ENG_MCU_VehicleWash_M2M.VehicleWash_ID, "Week " & ((DateDiff("d",[PeriodStartDate],[WashDate])-1)\7+1), Table_Vehicles.VehicleNumber, Table_ENG_FP_WorkingPeriods.WorkingPeriod_ID;Query 2
Code:
SELECT [1SubQuery_ENG_FP_RemainingVehicleWashes].Expr1, Count(Table_ENG_MCU_VehicleWash_M2M.WashDate) AS CountOfWashDate, [1SubQuery_ENG_FP_RemainingVehicleWashes].WorkingPeriod_ID
FROM 1SubQuery_ENG_FP_RemainingVehicleWashes INNER JOIN Table_ENG_MCU_VehicleWash_M2M ON [1SubQuery_ENG_FP_RemainingVehicleWashes].VehicleWash_ID = Table_ENG_MCU_VehicleWash_M2M.VehicleWash_ID
GROUP BY [1SubQuery_ENG_FP_RemainingVehicleWashes].Expr1, [1SubQuery_ENG_FP_RemainingVehicleWashes].WorkingPeriod_ID;Query 3
Code:
SELECT SubQuery_ENG_FP_RemainingVehicleWashes.Expr1, 149-[CountOfWashdate] AS totalSideWashes, SubQuery_ENG_FP_RemainingVehicleWashes.WorkingPeriod_ID
FROM SubQuery_ENG_FP_RemainingVehicleWashes
GROUP BY SubQuery_ENG_FP_RemainingVehicleWashes.Expr1, 149-[CountOfWashdate], SubQuery_ENG_FP_RemainingVehicleWashes.WorkingPeriod_ID;Query 4 (CrossTab)
Code:
TRANSFORM Sum(MainQuery_ENG_FP_RemainingVehicleWashes.totalSideWashes) AS SumOftotal
SELECT "Remaining" AS [Week No]
FROM MainQuery_ENG_FP_RemainingVehicleWashes
GROUP BY "Remaining", MainQuery_ENG_FP_RemainingVehicleWashes.WorkingPeriod_ID
PIVOT MainQuery_ENG_FP_RemainingVehicleWashes.Expr1;
