sql 语句[求助]

blue1018access

下面这个语句在sql server里好使，可是在access不好使，谁能帮我变成accesss里好使的？关键是case

SELECT t.[Type]
        ,[泵体]=sum(case u.[unit] when '泵体' then i.[Counts] else 0 end)
        ,[后盖]=sum(case u.[unit] when '后盖' then i.[Counts] else 0 end)
        ,[支架]=sum(case u.[unit] when '支架' then i.[Counts] else 0 end)
        ,[叶轮]=sum(case u.[unit] when '叶轮' then i.[Counts] else 0 end)
FROM MaterialType t
        join MaterialInfo i on t.[ID]=i.[TypeID]
        join MaterialUnit u on u.[ID]=i.[UnitID]
where u.[unit] in('泵体','后盖','支架','叶轮')
GROUP BY t.[Type]
ORDER BY t.[Type]

SELECT t.[Type]
        ,[泵体]=sum(case u.[unit] when '泵体' then i.[Counts] else 0 end)
        ,[后盖]=sum(case u.[unit] when '后盖' then i.[Counts] else 0 end)
        ,[支架]=sum(case u.[unit] when '支架' then i.[Counts] else 0 end)
        ,[叶轮]=sum(case u.[unit] when '叶轮' then i.[Counts] else 0 end)
FROM MaterialType t
        join MaterialInfo i on t.[ID]=i.[TypeID]
        join MaterialUnit u on u.[ID]=i.[UnitID]
where u.[unit] in('泵体','后盖','支架','叶轮')
GROUP BY t.[Type]
ORDER BY t.[Type]

土豆

ACCESS不支持case的说

可以考虑用交叉表查询