Top 10 in Crosstab query

  • Thread starter Thread starter nms
  • Start date Start date
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);
 
If your objective is to print only the first 10 records then you can add a
text box to the detail section of the report:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then add code to the On Format event module:
Cancel = (Me.txtCount > 10)
 
SELECT TOP 10 *
FROM yourSavedCrosstabQuery
ORDER BY aFieldPumpOutByYourSavedCrosstab





Hoping it may help,
Vanderghast, Access MVP
 
Thank you Duane-works like a champ and you just made the enduser one happy
persons!!!

Duane Hookom said:
If your objective is to print only the first 10 records then you can add a
text box to the detail section of the report:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then add code to the On Format event module:
Cancel = (Me.txtCount > 10)

--
Duane Hookom
Microsoft Access MVP


nms said:
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);
Click to expand...
Click to expand...
 
Back
Top