Custom Row Heading Sorting in Crosstab Query! (1 Viewer)

C

Cold Zero

New member

Local time

Today, 21:05

Joined

May 12, 2018

Messages

9

• Jun 5, 2018

• #1

[SOLVED] Custom Row Heading Sorting in Crosstab Query!

Hi,

Been searching this and tried everything I know with no luck

I have a table name tblOrder with field OrderID , OrderType and OrderStatus

I created a crosstab query to group by OrderType and count the total of orders, opened orders and closed orders


As you can see, the row heading is sorting ascending by OrderType (Parts, Services, Shipment)

How can I custom sort the row heading to show service in top, parts in middle and shipment at bottom

database is attached.

 

Attachments

• Crosstab_Sorting.zip

  17.7 KB · Views: 125

Last edited: Jun 7, 2018

June7

AWF VIP

Local time

Today, 10:05

Joined

Mar 9, 2014

Messages

5,466

• Jun 5, 2018

• #2

Only 3 types? Specify the column headings in the query ColumnHeadings property. The display order will be dictated by the order you list them. Review http://allenbrowne.com/ser-67.html#ColHead

 

isladogs

MVP / VIP

Local time

Today, 19:05

Joined

Jan 14, 2017

Messages

18,209

• Jun 5, 2018

• #3

June's idea won't work as these aren't column headers

Create an extra table tblOrderTypes with 2 fields OrderTypeID (PK, autonumber) & OrderType (text). Populate this in the order required: Service, Parts, Shipment.

Add this table to the crosstab query joining by OrderType & add the field OrderTypeID. You don't need to show the field unless you want to.
Sort by this in ascending order:

See attached

NOTE: Better still, use the OrderTypeID in tblOrders instead, link the 2 tables and enforce referential integrity. Modify your crosstab accordingl. I'll leave that to you

 

Attachments

• 
  Capture.PNG

  6.9 KB · Views: 739
• Crosstab_Sorting.zip

  17.7 KB · Views: 121

arnelgp

..forever waiting... waiting for jellybean!

Local time

Tomorrow, 02:05

Joined

May 7, 2009

Messages

19,229

• Jun 6, 2018

• #4

Take a look at the query again. It is now sorting correctly without extra table involved.

 

Attachments

• Crosstab_Sorting.zip

  17.8 KB · Views: 183

June7

AWF VIP

Local time

Today, 10:05

Joined

Mar 9, 2014

Messages

5,466

• Jun 6, 2018

• #5

Ooops, yes, I didn't read entire question. Glad someone else jumped in.

 

isladogs

MVP / VIP

Local time

Today, 19:05

Joined

Jan 14, 2017

Messages

18,209

• Jun 6, 2018

• #6

June7
No problem
Also, based on your user name, happy birthday for tomorrow if that's the case.

Arnel
Neat use of Switch there

 

C

Cold Zero

New member

Local time

Today, 21:05

Joined

May 12, 2018

Messages

9

• Jun 7, 2018

• #7

Thanks guys, you gave me more options to do it. Perfection

 

June7

AWF VIP

Local time

Today, 10:05

Joined

Mar 9, 2014

Messages

5,466

• Jun 7, 2018

• #8

Nope, not my birthday, but thanks for the sentiment!