I have a problem in MS Access. I wanted have a table, which contains all the maintenance of my fleet. The relevant columns are id_plate, type_of_maintenance, and date. And here is the tricky part. There are different types of prev.maintenances. Let's say we have 5types. There is a type1 for every 6months, type2 12months, type3 24months, type4 48months, type5 60months.
So you can see type1 is followed by type2 than type1 again than type3.
I want a query which can tell for each individual vehicle, the upcoming preventive maintenances (next preventive, and also the next typ1-typ2 ect.)
Any advise how should I do it? Due to company regulations only MS office softwares are allowed to work with.
Thanks!
I have tried to count the number of overall maintenances and to make a big IIF, If the sum of all maintenances is 1than the upcoming is a type2 etc. But it's too manual and very limited
So basically with the help of BartMcEndree I managed to find the solution.
Basically I just adjusted his code, so there would only 1 type of upcoming maintenance can come out true. And than I added a new column, where you can track the DATE of the upcoming scheduled maintenance.
Special thanks for you Bart. I was stucked with this problem for a while now, and now its working. Thanks a lot! See the final code below!
SELECT GroupedMaint.*,
NZ(LastType1.MonthsSince,
NZ(MonthsSinceFirst,-1)) AS MonthsSinceType1, IIf( MonthsSinceType1> 6,
Iif(Type2Due="YES", "NO", "YES")
, "NO") AS Type1Due,
NZ(LastType2.MonthsSince,NZ(MonthsSinceFirst,-1)) AS MonthsSinceType2, IIf( MonthsSinceType2> 12,
Iif(Type3Due="YES", "NO", "YES"),
"NO") AS Type2Due,
NZ(LastType3.MonthsSince,NZ(MonthsSinceFirst,-1)) AS MonthsSinceType3, IIf( MonthsSinceType3> 24,
Iif(Type4Due="YES", "NO", "YES")
, "NO") AS Type3Due,
NZ(LastType4.MonthsSince,NZ(MonthsSinceFirst,-1)) AS MonthsSinceType4, IIf( MonthsSinceType4> 48,
Iif(Type5Due="YES", "NO", "YES")
, "NO") AS Type4Due,
NZ(LastType5.MonthsSince,NZ(MonthsSinceFirst,-1)) AS MonthsSinceType5, IIf( MonthsSinceType5> 60, "YES", "NO")
AS Type5Due
FROM
(((((SELECT id_plate, Min(date_of_maintenance) AS FirstMaintDate, DateDiff("m",[FirstMaintDate],Now()) AS MonthsSinceFirst, Max(date_of_maintenance) AS LastMaintDate, DateDiff("m" ,[LastmaintDate],Now()) AS MonthsSinceLast, DateAdd("m", 6, LastMaintDate) AS NextScheduledMaintenance
FROM Maint
GROUP BY id_plate
) AS GroupedMaint
LEFT JOIN (SELECT Maint.id_plate, Maint.type_of_maintenance, Max(Maint.date_of_maintenance) AS LastMaintDate, DateDiff("m",[LastmaintDate],Now()) AS MonthsSince
FROM Maint WHERE Maint.type_of_maintenance=1
GROUP BY Maint.id_plate, Maint.type_of_maintenance) AS LastType1 ON LastType1.id_plate=GroupedMaint.id_plate)
LEFT JOIN (SELECT Maint.id_plate, Maint.type_of_maintenance, Max(Maint.date_of_maintenance) AS LastMaintDate, DateDiff("m",[LastmaintDate],Now()) AS MonthsSince
FROM Maint WHERE Maint.type_of_maintenance=2
GROUP BY Maint.id_plate, Maint.type_of_maintenance) AS LastType2 ON LastType2.id_plate=GroupedMaint.id_plate)
LEFT JOIN (SELECT Maint.id_plate, Maint.type_of_maintenance, Max(Maint.date_of_maintenance) AS LastMaintDate, DateDiff("m",[LastmaintDate],Now()) AS MonthsSince
FROM Maint WHERE Maint.type_of_maintenance=3
GROUP BY Maint.id_plate, Maint.type_of_maintenance) AS LastType3 ON LastType3.id_plate=GroupedMaint.id_plate)
LEFT JOIN (SELECT Maint.id_plate, Maint.type_of_maintenance, Max(Maint.date_of_maintenance) AS LastMaintDate, DateDiff("m",[LastmaintDate],Now()) AS MonthsSince
FROM Maint WHERE Maint.type_of_maintenance=4
GROUP BY Maint.id_plate, Maint.type_of_maintenance) AS LastType4 ON LastType4.id_plate=GroupedMaint.id_plate)
LEFT JOIN (SELECT Maint.id_plate, Maint.type_of_maintenance, Max(Maint.date_of_maintenance) AS LastMaintDate, DateDiff("m",[LastmaintDate],Now()) AS MonthsSince
FROM Maint WHERE Maint.type_of_maintenance=5
GROUP BY Maint.id_plate, Maint.type_of_maintenance) AS LastType5 ON LastType5.id_plate=GroupedMaint.id_plate;