Cross Tab Query - Cross Wired

[NauticalGent]

Hello again Access Apostles,

I have finally sucked it up and decided to take the plunge into cross-tab queries. I have avoided them because of the Excel-like nature to them and I have never had a need for them...until now.

After reading some threads here and watching some YouTube videos, I rolled up my sleeves and got to it.

The SQL code below is what get generated from the Design View screen capture.


TRANSFORM First(tblCORAudit.AuditStatusDate) AS FirstOfAuditStatusDate
SELECT tblCORAudit.SchedID
FROM tblCORAudit
GROUP BY tblCORAudit.SchedID
PIVOT tblCORAudit.AuditStatus;

It provides me with a 99% solution, which is more than acceptable. However, the finishing touch would be to sort the column headings differently.

The data in the tblCORAudit.AuditStatus comes from a table called tblStatus. It has a Primary field called "Rank" which is numeric.

As written the Column headings are alphabetically sorted by the actual status entered. What I would like is to sort the status entries by the numerical rank.

Again, thanks in advance and if I an not been clear enough, please let me know and I will make it clearer.

 

[plog]

You have to hack it to to work. Column headings are always going to be treated as text and sorted as such. So, convert your numbers into text that will sort properly.

Usually that means formatting them with preceding 0's. If the largest value is 2433, then you need to format it so every value has 4 places (0001, 0002), then they will sort as you want.

Check out the Format function: http://www.techonthenet.com/access/functions/numeric/format.php

Also, 'First' should not be used in the 'Totals' row. It doesn't mean what you (or anyone) thinks it means. I would instead use Min or Max.

 

[NauticalGent]

I see that once again, I failed to be completely clear. The headings are indeed non-numeric...however, you did give me and idea.

I could connote the ranking number and the status with a query and then perform the cross tab query. Thoughts?

 

[NauticalGent]

Plog, I took your advice about formatting and it worked like a charm. I apologize for thinking you misunderstood my problem and thank you again for your excellent advice!