Hi,

Does anyone have any ideas on how I can select the top 10 highest entries in a crosstab query? I know that the sql command is 'top ....' but I cant seem to get this working in a crosstab query. Below is the sql from the query as it stands:

TRANSFORM Count([Change Register].CR_ID) AS CountOfCR_ID
SELECT [Change Register].PCRNo, [Change Register].[Financial Value], BPM.[BPM Name]
FROM BPM INNER JOIN [Change Register] ON BPM.[BPM ID] = [Change Register].BPM_Name
WHERE ((([Change Register].PCRNo) Not Like "blank") AND (([Change Register].[Financial Value]) Not Like "blank"))
GROUP BY [Change Register].PCRNo, [Change Register].[Financial Value], BPM.[BPM Name]
PIVOT [Change Register].Requestor;

Any help would be appreciated.

Hi
Your only option may be to base your xtab on a query that's already done the filtering of the top 10..
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

I would take the cross tab.

Add this into a query and sort descending on the field you want and then select the top 10 in the properties.

I think trying to do this all in one might be asking a but too much.

Jim

Hi,

Thanks will give it a go. I take it that there is no way to do this via a crosstab query then?

Thanks

Hi
Dynamically maybe..!!!
(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

thanks both, help much appreciated....

Hi,

Managed to get it working via a select query:

SELECT TOP 10 [Change Register].PCRNo, [Change Register].[Financial Value], BPM.[BPM Name], [Change Register].Requestor, Count([Change Register].CR_ID) AS CountOfCR_ID
FROM BPM INNER JOIN [Change Register] ON BPM.[BPM ID] = [Change Register].BPM_Name
WHERE ((([Change Register].PCRNo) Not Like "blank") AND (([Change Register].[Financial Value]) Not Like "blank"))
GROUP BY [Change Register].PCRNo, [Change Register].[Financial Value], BPM.[BPM Name], [Change Register].Requestor
ORDER BY [Change Register].[Financial Value] DESC;

Thanks for your help.

Yes, that is the way I was shown and do.

Glad you got it to work okay.

Jim