CODEWITHSUNDEEP
sqlms-accessms-access-query
sgtmarci
sgtmarci

Reputation: 1

Preventive Maintenance Access SQL Query

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

Upvotes: 0

Views: 201

Answers (2)

sgtmarci
sgtmarci

Reputation: 1

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;

Upvotes: 0

Bart McEndree
Bart McEndree

Reputation: 3293

I think this example might be a good place to start. It exposes the month calculation columns so you can check the math. If you add a new vehicle it should work well. For older vehicles you might need to enter the last time each service type was performed.

SELECT GroupedMaint.*,  
NZ(LastType1.MonthsSince,NZ(MonthsSinceFirst,-1))  as MonthsSinceType1,  IIf( MonthsSinceType1> 6, "YES", "NO") AS Type1Due, 
NZ(LastType2.MonthsSince,NZ(MonthsSinceFirst,-1))  as MonthsSinceType2,  IIf( MonthsSinceType2> 12, "YES", "NO") AS Type2Due, 
NZ(LastType3.MonthsSince,NZ(MonthsSinceFirst,-1))  as MonthsSinceType3,  IIf( MonthsSinceType3> 24, "YES", "NO") AS Type3Due, 
NZ(LastType4.MonthsSince,NZ(MonthsSinceFirst,-1))  as MonthsSinceType4,  IIf( MonthsSinceType4> 48, "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
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)

Upvotes: 0

Related Questions