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 [ ^ ]

Hello I'm Looking for some solution to my Problem I have Crosstab Query

TRANSFORM Count(qrySickCount.TYPE) AS CountOfTYPE SELECT qrySickCount.FullName, Count(qrySickCount.TYPE) AS TOTAL FROM qrySickCount WHERE (((qrySickCount.Exception)=False)) GROUP BY qrySickCount.FullName, qrySickCount.Exception PIVOT Format([StartDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Base on other Query

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));

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 TOTAL FROM qrySickCount WHERE (((qrySickCount.Exception)=False)) GROUP BY qrySickCount.FullName HAVING COUNT(qrySickCount.TYPE)>3

For further information, please, see: HAVING Clause[^]