MS Access车队预防性维护查询需求及现有方案改进咨询

一、基础查询：提取每辆车各类型最后维护日期 #

先创建查询qry_Last_Maintenance，获取每辆车每种维护类型的最近执行记录：

SELECT 
    id_plate,
    type_of_maintenance,
    Max(date) AS Last_Maintenance_Date
FROM 
    车队维护表
GROUP BY 
    id_plate, type_of_maintenance;

二、计算各类型即将到来的维护日期 #

创建查询qry_Upcoming_Types，基于上述结果计算每种维护类型的下一次执行日期：

SELECT 
    lm.id_plate,
    lm.type_of_maintenance,
    lm.Last_Maintenance_Date,
    Switch(
        lm.type_of_maintenance = 'type1', DateAdd('m', 6, lm.Last_Maintenance_Date),
        lm.type_of_maintenance = 'type2', DateAdd('m', 12, lm.Last_Maintenance_Date),
        lm.type_of_maintenance = 'type3', DateAdd('m', 24, lm.Last_Maintenance_Date),
        lm.type_of_maintenance = 'type4', DateAdd('m', 48, lm.Last_Maintenance_Date),
        lm.type_of_maintenance = 'type5', DateAdd('m', 60, lm.Last_Maintenance_Date)
    ) AS Next_Type_Date
FROM 
    qry_Last_Maintenance lm
UNION ALL
-- 补充未做过该类型维护的车辆记录（需替换车辆初始日期来源）
SELECT 
    v.id_plate,
    t.type_code AS type_of_maintenance,
    Null AS Last_Maintenance_Date,
    Switch(
        t.type_code = 'type1', DateAdd('m', 6, v.First_Registration_Date),
        t.type_code = 'type2', DateAdd('m', 12, v.First_Registration_Date),
        t.type_code = 'type3', DateAdd('m', 24, v.First_Registration_Date),
        t.type_code = 'type4', DateAdd('m', 48, v.First_Registration_Date),
        t.type_code = 'type5', DateAdd('m', 60, v.First_Registration_Date)
    ) AS Next_Type_Date
FROM 
    车辆表 v,
    (SELECT 'type1' AS type_code UNION SELECT 'type2' UNION SELECT 'type3' UNION SELECT 'type4' UNION SELECT 'type5') t
LEFT JOIN 
    qry_Last_Maintenance lm ON v.id_plate = lm.id_plate AND t.type_code = lm.type_of_maintenance
WHERE 
    lm.id_plate IS NULL;

注：若没有独立车辆表，可将v.First_Registration_Date替换为车辆首次启用日期或当前日期Date()。

三、计算下一次按顺序的预防性维护 #

1. 统计每辆车总维护次数 #

创建查询qry_Total_Maintenance_Count：

SELECT 
    id_plate,
    Count(*) AS Total_Maintenances
FROM 
    车队维护表
GROUP BY 
    id_plate;

2. 映射维护次数到任务类型并计算日期 #

根据type1→type2→type1→type3…的执行顺序，通过模运算实现自动映射，创建查询qry_Next_Preventive_Maintenance：

SELECT 
    tc.id_plate,
    tc.Total_Maintenances,
    Switch(
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 0, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 1, 'type2',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 2, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 3, 'type3',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 4, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 5, 'type2',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 6, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 7, 'type4',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 8, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 9, 'type2',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 10, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 11, 'type3',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 12, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 13, 'type2',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 14, 'type1',
        ((tc.Total_Maintenances + 1 - 1) Mod 16) = 15, 'type5'
    ) AS Next_Maintenance_Type,
    -- 计算下一次任务日期
    Nz(
        DateAdd(
            'm',
            Switch(
                Next_Maintenance_Type = 'type1',6,
                Next_Maintenance_Type = 'type2',12,
                Next_Maintenance_Type = 'type3',24,
                Next_Maintenance_Type = 'type4',48,
                Next_Maintenance_Type = 'type5',60
            ),
            DLookup("Max(date)", "车队维护表", "id_plate='" & tc.id_plate & "' AND type_of_maintenance='" & Next_Maintenance_Type & "'")
        ),
        DateAdd(
            'm',
            Switch(
                Next_Maintenance_Type = 'type1',6,
                Next_Maintenance_Type = 'type2',12,
                Next_Maintenance_Type = 'type3',24,
                Next_Maintenance_Type = 'type4',48,
                Next_Maintenance_Type = 'type5',60
            ),
            (SELECT First_Registration_Date FROM 车辆表 WHERE id_plate=tc.id_plate)
        )
    ) AS Next_Maintenance_Date
FROM 
    qry_Total_Maintenance_Count tc
UNION ALL
-- 补充未做过任何维护的车辆
SELECT 
    v.id_plate,
    0 AS Total_Maintenances,
    'type1' AS Next_Maintenance_Type,
    DateAdd('m',6, v.First_Registration_Date) AS Next_Maintenance_Date
FROM 
    车辆表 v
LEFT JOIN 
    qry_Total_Maintenance_Count tc ON v.id_plate = tc.id_plate
WHERE 
    tc.id_plate IS NULL;

注：若id_plate为数字类型，需去掉DLookup条件中的单引号。

四、合并结果（可选） #

若需将下一次顺序任务与各类型即将任务合并展示，创建联合查询：

SELECT 
    nm.id_plate,
    nm.Next_Maintenance_Type AS 下一次顺序维护类型,
    nm.Next_Maintenance_Date AS 下一次顺序维护日期,
    ut.type_of_maintenance AS 维护类型,
    ut.Next_Type_Date AS 该类型下一次日期
FROM 
    qry_Next_Preventive_Maintenance nm
LEFT JOIN 
    qry_Upcoming_Types ut ON nm.id_plate = ut.id_plate;

内容的提问来源于stack exchange，提问作者sgtmarci