N
nms
Thanks in advance for the assistance-the discussion groups have been very
helpful!!
I am trying to get the top 10 in a crosstab query to run a report. I have
tried this several times and keep getting errors about the transform
statement (such as Values Specified in a TOP clause are not allowed in delete
queries or reports). Also, in the crosstab query I am not allowed to change
the TOP predicate-it is grayed out but works great in a regular select query
but I need it to work in the crosstab query. I have attached my attempt at
the SLQ Code (and it didn't like the AS in ORDER BY so I took it out in
another attempt):
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT TOP 10 dbo_HPD_HelpDesk.Requester_Name_,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",IIf([arrival_time] Is
Null,0,[Arrival_Time]),#1/1/1970#)) Between CDate([Enter Beginning Date]) And
CDate([Enter Ending Date])))
GROUP BY dbo_HPD_HelpDesk.Requester_Name_
ORDER BY Count(dbo_HPD_HelpDesk.Status) AS CountofStatus1 DESC;
PIVOT dbo_HPD_HelpDesk.Status In (1,2,3,4,5);
helpful!!
I am trying to get the top 10 in a crosstab query to run a report. I have
tried this several times and keep getting errors about the transform
statement (such as Values Specified in a TOP clause are not allowed in delete
queries or reports). Also, in the crosstab query I am not allowed to change
the TOP predicate-it is grayed out but works great in a regular select query
but I need it to work in the crosstab query. I have attached my attempt at
the SLQ Code (and it didn't like the AS in ORDER BY so I took it out in
another attempt):
TRANSFORM Val(Nz(Count(dbo_HPD_HelpDesk.Status),0)) AS CountOfStatus
SELECT TOP 10 dbo_HPD_HelpDesk.Requester_Name_,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",IIf([arrival_time] Is
Null,0,[Arrival_Time]),#1/1/1970#)) Between CDate([Enter Beginning Date]) And
CDate([Enter Ending Date])))
GROUP BY dbo_HPD_HelpDesk.Requester_Name_
ORDER BY Count(dbo_HPD_HelpDesk.Status) AS CountofStatus1 DESC;
PIVOT dbo_HPD_HelpDesk.Status In (1,2,3,4,5);