Crosstab Query Sort Order? 1

[ShellyL]

Can you designate the sort-order for a cross tab query based on a field you don't want displayed in the query? The values of the column headings will vary each time, so I don't think setting the column-headings property will work.

Basically, I have a base query that selects:
- Customer ID (from customer table)
- Product ID (numeric from product table - preferred sort order)
- Product Name (from product table)
- Units Sold (from sales table)
The query prompts for the specified Customer ID.

I want my crosstab to look like this:
Oranges Bananas Apples
Customer A 5 10 3

The crosstab sorts the column alphabetically. I'd prefer the columns in Product_ID order. Also, not every customer buys every product, and only the relevant products should appear. If I set the column-headings properties as "Oranges", "Bananas", "Apples", all products appear regardless of activity. If I add Product_ID to the crosstab as "Not Shown", ascending order, then it groups based on the Product_ID value and I end up with multiple rows for the single customer.

Any suggestions?

 

[dhookom]

Consider using the solution found at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ShellyL]

In the crosstab report example, it looks like you are using the column-heading property to establish the order. I'm hoping to use the Product_ID value to determine the column order. Is there something else I should look for?

 

[dhookom]

Can you concatenate the id and name togethere for the column heading?


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ShellyL]

Tried that, but then my report sorts 1-Oranges, 10-Mangos, 2-Apples, etc. Anything else?

 

[dhookom]

Sure, format your id.
Format(ID,"00") & "-" & TheNameField

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ShellyL]

Formatting the ID worked great. Thanks for the simple solution!