CrossTab查询中的标准

本文介绍了CrossTab查询中的标准的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 你好 我正在寻找解决我问题的方法 我有交叉表查询 TRANSFORM计数（qrySickCount。 TYPE ） AS CountOfTYPE SELECT qrySickCount.FullName，Count（qrySickCount。 TYPE ） AS 总计 FROM qrySickCount WHERE （（（qrySickCount.Exception）= False）） GROUP BY qrySickCount.FullName，qrySickCount.Exception PIVOT格式（[StartDate]， mmm）在（ Jan， Feb， Mar， Apr， May ， Jun， Jul， Aug ， Sep， Oct， Nov， Dec）; 基于其他查询 SELECT [FirstName]& & [LastName] AS FullName，tblLeave.StartDate，tblLeave.EndDate，DateDiff（ d，[StartDate]，[EndDate]） AS DaysOff，IIf（[ReasonType] = 4， HOL，IIf（[ReasonType] = 5， HOL，IIf（[ReasonType] = 1， SICK，IIf（[ReasonType] = 2， SICK，IIf（[ ReasonType] = 3， SICK））））） AS TYPE ，tblLeave.Exception FROM tblEmployee INNER JOIN （tblReasonType INNER JOIN tblLeave ON tblReasonType.ID = tblLeave.ReasonType） ON tblEmployee.EmployeeNR = tblLeave.EmployeeNr GROUP BY [FirstName]& & [LastName]，tblLeave.StartDate，tblLeave.EndDate，DateDiff（ d，[StartDate]， [EndDate]），IIf（[ReasonType] = 4， HOL，IIf（[ReasonType] = 5， HOL，IIf（[ReasonType] = 1， SICK，IIf（[ReasonType] = 2， SICK，IIf（[ReasonType] = 3， SICK ））））），tblLeave.Exception HAVING （（（IIf（[ReasonType] = 4， HOL，IIf（[ReasonType] = 5， HOL，IIf（[ReasonType] = 1， SICK ，IIf（[ReasonType] = 2， SICK，IIf（[ReasonType] = 3， SICK））））））= SICK） AND （（tblLeave.Exception）= False））; 而且我期待只看到总共超过3个SICK的人才 当我将Criteria放入我的时候TOTAL列我发现错误 无法在where子句中使用聚合函数 以任何方式通过那个?? 或者如何建立一个查询什么会告诉我一年中有超过3个SICK的人在我的secund一个???? 先谢谢解决方案 试试这个： SELECT qrySickCount.FullName，COUNT（qrySickCount。 TYPE ） AS 总计 FROM qrySickCount WHERE （（（qrySickCount.Exception）= False）） GROUP BY qrySickCount.FullName HAVING COUNT（qrySickCount。 TYPE ）> 3 如需了解更多信息，请参阅： HAVING Clause [ ^ ] HelloI'm Looking for some solution to my ProblemI have Crosstab Query TRANSFORM Count(qrySickCount.TYPE) AS CountOfTYPESELECT qrySickCount.FullName, Count(qrySickCount.TYPE) AS TOTALFROM qrySickCountWHERE (((qrySickCount.Exception)=False))GROUP BY qrySickCount.FullName, qrySickCount.ExceptionPIVOT Format([StartDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");Base on other QuerySELECT [FirstName] & " " & [LastName] AS FullName, tblLeave.StartDate, tblLeave.EndDate, DateDiff("d",[StartDate],[EndDate]) AS DaysOff, IIf([ReasonType]=4,"HOL",IIf([ReasonType]=5,"HOL",IIf([ReasonType]=1,"SICK",IIf([ReasonType]=2,"SICK",IIf([ReasonType]=3,"SICK"))))) AS TYPE, tblLeave.ExceptionFROM tblEmployee INNER JOIN (tblReasonType INNER JOIN tblLeave ON tblReasonType.ID = tblLeave.ReasonType) ON tblEmployee.EmployeeNR = tblLeave.EmployeeNrGROUP BY [FirstName] & " " & [LastName], tblLeave.StartDate, tblLeave.EndDate, DateDiff("d",[StartDate],[EndDate]), IIf([ReasonType]=4,"HOL",IIf([ReasonType]=5,"HOL",IIf([ReasonType]=1,"SICK",IIf([ReasonType]=2,"SICK",IIf([ReasonType]=3,"SICK"))))), tblLeave.ExceptionHAVING (((IIf([ReasonType]=4,"HOL",IIf([ReasonType]=5,"HOL",IIf([ReasonType]=1,"SICK",IIf([ReasonType]=2,"SICK",IIf([ReasonType]=3,"SICK"))))))="SICK") AND ((tblLeave.Exception)=False));And I'm Looking to see only people who have more then 3 SICK in Total only When i place Criteria to my TOTAL column I reciving an error "cannot have aggregate function in where clause"its any way to pass that ??or how to build a query what will show me peaple who have more then 3 SICK in a Year Base on my secund one ????Thanks in Advance 解决方案 Try this:SELECT qrySickCount.FullName, COUNT(qrySickCount.TYPE) AS TOTALFROM qrySickCountWHERE (((qrySickCount.Exception)=False))GROUP BY qrySickCount.FullNameHAVING COUNT(qrySickCount.TYPE)>3For further information, please, see: HAVING Clause[^] 这篇关于CrossTab查询中的标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！