CrossTab查询中的标准 [英] Criteria In CrossTab Query

问题描述

你好



我正在寻找解决我问题的方法





我有交叉表查询

 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一个????





先谢谢

解决方案

< blockquote>试试这个：

  SELECT  qrySickCount.FullName，COUNT（qrySickCount。 TYPE ） AS 总计
  FROM  qrySickCount 
  WHERE （（（qrySickCount.Exception）= False））
  GROUP  < span class =code-keyword> 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[^]

这篇关于CrossTab查询中的标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！