Custom Order Rows in Crosstab Query

[dterranova]

Hello, I have created a crosstab query where I specify the order of Column Headings, however, I can't figure out how to specify the order of Row Headings. I only see that Microsoft Access offers the option to sort ascending vs. descending. I'd like to customize my sort order such as "Under 18" row first followed by "18-29" row and then "30-39" etc.

Any help you may provide would be great.
Diana

 

[MStef]

Hello Diana!

Look at "DemoCustomSort.mdb" (attachment).
You have to make a "Select query" (Query1), in this query make a column
"MySort", and make a Module "GetCustSort". Link your crosstab query on this select query. I suggest make a report on your crosstab query.
Look at Table1, Query1, Query_Crosstab, Report1_Crosstab, Module1.

 

[gbab]

Hello,

I need as well to customize the row headings of my crosstab reports but not by category like the Diana post. My idea was to add a letter before the name of each row, ie aDeposits, bOther Securities, cFunds,...and to withdraw it in the report with Mid(data name,2,len(data name)) but I have a "#Error" message and I can't figure out why.

Any help you may provide would be kind.

Thanks a lot
Guillaume

 

[MStef]

Hello gbab!
Send a short example of your mdb, (access 2000, or 2002).

 

[gbab]

Hi MStef !

Please find enclosed a short example in Access 2000 (my version).

The overall objective is to generate a report of data by month and I need a specific order for both category and data. As you can see in the report "Renta_Crosstab", I have the order I want. The next step is then to withdraw the first letter of the Category and Data Name and I have an error message as you can see on the "Renta_Crosstab_Error message".

Thanks a lot in advance,
Guillaume

 

[MStef]

Hello BNP Paribas!
Here it is, look at "Renta_Crostab" report.
I think it is what you want.

 

[gbab]

This is magic ! It's exactly what I want ! Very smart tip !

Thks a lot
Guillaume

 

[MStef]

You can do it on this way too:
Right([Text16];Len([Text16]) - 2)
Right([DN];Len([DN]) - 2)