SELECT TOP N RECORDS FROM CROSSTAB QUERY

[Ann Simmons]

Hello everyone. How do you select TOP N RECORDS FROM CROSSTAB QUERY? I will be grateful for your assistance. Thanks.

 

[isladogs]

Welcome to the forum

Create a new SELECT TOP N query from the crosstab query

 

[theDBguy]

Hi. Welcome to AWF!

I agree. What have you tried?

 

[Ann Simmons]

Welcome to the forum

Create a new SELECT TOP N query from the crosstab query

Thanks

 

[Ann Simmons]

Welcome to the forum

Create a new SELECT TOP N query from the crosstab query

N RECORDS should come from the field lets call it FIELD A. How do you go about it?

 

[Pat Hartman]

The top predicate doesn't refer to fields. It is not criteria. It just returns the first n records based on the sort order of the query.

The directions were a little murky. Open the QBE to create a new query. Choose the crosstab query as the recordsource. Select all the columns. In the properties, select Top and enter a number. This number is not a variable. It is hard coded. If you want it to be variable, you must build the SQL using VBA. You won't be able to use a saved querydef.

 

[SHANEMAC51]

Hello everyone. How do you select TOP N RECORDS FROM CROSSTAB QUERY? I will be grateful for your assistance. Thanks.

cross queries are different
the following type is both sorted and selected

TRANSFORM Count(Students.[ID]) AS [Count-ID]
SELECT Students.[STREAM], Count(Students.[ID]) AS total
FROM Students
GROUP BY Students.[STREAM]
PIVOT year([Birth Date]) in (2000,2001,2004,2005,2006,2007,2008);

SELECT TOP 2 sg.STREAM, sg.total,
 sg.[2000], sg.[2001], sg.[2004], sg.[2005], sg.[2006], sg.[2007], sg.[2008]
FROM Students_grosstab AS sg
WHERE (((sg.STREAM) Like "[abc]*"))
ORDER BY sg.total DESC;