Crosstab Query Sort Order

[BillyB]

I have found a few things on this but not quite what I was looking for. What I need to do is display a name in the crosstab query ("Jon Smith", "Waldo", etc.) but I want them sorted on the ID number. If I include the number as the first part of the name then I get #1,10,100,1000, Etc instead of 1,2,3,4 because the values are grouped as strings.

Any help would be greatly appreciated.

Bill

 

[Jon K]

Rebuild the crosstab query, using both the ID and Name fields as row headings.

Or switch to SQL View of the crosstab query and add the ID field in both the SELECT and the GROUP BY clauses:-

TRANSFORM ......
SELECT ID, ......
FROM ......
GROUP BY ID, ......
PIVOT ......;

 

[BillyB]

Thank you for your reply Jon but I may not have been clear. The values I want sorted are the Column Headings not the Row Headings. If I add the id to the row heading I get multple rows.

Ex. of what I want

Dept. Jon Smith Jane Doe Rob Lear
Grocery X X x
Pharmacy X X

Where the names across the top are sorted by ID rather than name.

 

[BillyB]

Sorry; It appears that the post removes extra spaces so things did not align correctly

Dept................Jon Smith...Jane Doe.....Rob Lear
Grocery...................X.............X................X
Pharmacy................X...............................X

 

[Jon K]

In a crosstab query, Access sorts the column headings as text, unless you manually specify their order with an IN expression in the Pivot Clause, e.g.

TRANSFORM ......
SELECT ......
FROM ......
GROUP BY ......
PIVOT [NameField] IN ("Jon Smith","Jane Doe","Rob Lear")


Alternatively, you can add the numeric ID field in the PIVOT Clause with the Format() function:-

PIVOT Format([ID],"000 ") & [NameField]

This will sort and show the headings by ID & Name, but some of them will be with leading zeroes, e.g. 001 Jon Smith