Sorting Rows in a Crosstab Query (1 Viewer)

[CCIDBMNG]

I have a crosstab query and I was wondering if there was a way to sort the row headers besides in acending or decending order. I tried not sorting at all but it's still sorting it in acending order. I know you can sort the colomn headers in any order you want but I can't figure out how to sort row headers.

 

[CCIDBMNG]

Actually it's sorting them in my union query first. So is there a way to specify the order in the union query? I'm combining 3 queries and I want the one query to display the records first then I want the next query to display and the third to display last but it's sorting them in acending order.

 

[Pat Hartman]

You need to include a calculated field in the union query that identifies which set of data a row came from. You can then sort on that field.

Select fld1, fld2, "tab1" As SeqFld
From YourTable1
Union Select fld1, fld2, "tab3" as SeqFld
From YourTable2
....

 

[CCIDBMNG]

Pat I'm a little confused by what you mean. I'm not very familiar with union queries. Could you explain a little more please? Thanks.

 

[David R]

Union queries combine several fields/tables into one seamless whole. As long as each UNION SELECT statement has the same number of fields and you put them in the same order each time, your queries will be 'stacked' one on top of the other into a unified query that you can use to base your crosstab on.

If you've never built a query in SQL View it can seem a little daunting at first, but you'll get the hang of it. Pat's example gives a good basis to build off of.

"tab1" As SeqFld means make a calculated field [SeqFld] and give it the value "tab1" for the table 1 data, etc.

HTH,
David R

 

[CCIDBMNG]

Ok I got it to sort the way I wanted them in my union query but it's now sorting them in my crosstab query

 

[Pat Hartman]

It's hard for us to guess what exactly is wrong. Why don't you just post the union query and the crosstab query.

 

[CCIDBMNG]

I got it to work. I added an ID number to my queries and sorted by the ID number. Thanks anyway though.

 

[dcnick]

I used the same approach [ID number] to sort my crosstab query. Problem is, this sort order is not carrying over to my report. Since [ID number] is not a row or column in my crosstab query, it is not an option in "Sorting and Grouping." Is this possible?

Thanks in advance

 

[Pat Hartman]

You need to include [ID Number] in the CrossTab query. The wizard only allows three fields but if you open the query in design view, you can add additional columns manually.

 

[dcnick]

I guess I meant to say [ID number] is already a field in my crosstab query, however it is not a row or column HEADING. Therefore, when I try to create the report, [ID number] is not an option in the "Sorting and Grouping" box.

The crosstab query works great and the rows print in the correct order. Now, I'm trying to print the rows in the same order when I create the report. Currently the report prints the rows in alphabetical order, which is different from the [ID number] order.

Thanks

 

[Pat Hartman]

Since you added the ID column directly to the query, you also need to set the Crosstab cell for the ID column to RowHeading to have it included in the selected columns list.