Link to home
Start Free TrialLog in
Avatar of Ryan
RyanFlag for United States of America

asked on

Cross tab sorting columns (not by column)

I created a crosstab query, which will expand each week. Theres 4 static fields and then a bunch of dynamic ones. I would like the dynamic ones to be sorted left to right by the date they occured. Currently, I don't see how they're sorted, it looks random to me.


TRANSFORM Sum(qryRacerPtsFinal.Points) AS SumOfPts
SELECT tblRider.IsMale, qryRacerPtsFinal.Category, tblRider.LastName, tblRider.FirstName, tblRider.TeamAbbrev
FROM tblEvent INNER JOIN (
  tblRace INNER JOIN (
    tblRider INNER JOIN qryRacerPtsFinal ON tblRider.RiderID = qryRacerPtsFinal.RiderID)
    ON tblRace.RaceID = qryRacerPtsFinal.RaceID)
  ON tblEvent.EventID = tblRace.EventID
GROUP BY tblRider.IsMale, qryRacerPtsFinal.Category, tblRider.LastName, tblRider.FirstName, qryRacerPtsFinal.RiderID, tblRider.TeamAbbrev
ORDER BY tblRider.IsMale, qryRacerPtsFinal.Category
PIVOT IIf([championship],"~","") & [EventAbbrev];
 
currently gets results
IsMale	Category	LastName	FirstName	TeamAbbrev	NCSU Crit	NCSU RR	WM ITT	NAVY Crit	NAVY ITT	WM RR
-1	A	Barrie	Sean	GU				67		
-1	A	Barston	Matt	UMD					3	1
-1	A	Beloni	Reid	VT		30	40			
-1	A	Bloom	Ryan	JHU			35	10	
 
But the last 6 columns should be in a different order, based on thier date (tblEvent.racedate)

Open in new window

Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

I take it you are talking about the order that the columns that are created by the Value part of the cross tab appear. By default these are in alphabtical order by the column name.The only way to alter this is to edit the column property of the query (in QBE grid right click in the "column Heading" column and enter the column names in the Column Heading property).

In code  you need to dynamically set the PIVOT part to be
PIVOT PIVOT IIf([championship],"~","") & [EventAbbrev] IN( enter column names here);

Kelvin
Avatar of Ryan

ASKER

I don't know the column names when I'm writing the SQL. They're dynamic.  And they aren't sorted in Alphabetical order. "WM ITT" should be next to "WM RR" if they're alphabetical.
True, but they're generated from the values in the column headings field and you could run a sorted grouping query against that data just prior & create a string of them in the order you want.

Not sure why order is as it is at present, but using the IN will force the order.


Kelvin
Avatar of Ryan

ASKER

PIVOT IIf([championship],"~","") & [EventAbbrev] IN (SELECT tblEvent.EventAbbrev FROM tblEvent ORDER BY tblEvent.RaceDate)

gives me "Cross tab query contains 1 or more invalid fixed column headings."
I think you'd need to run that subquery as a query intom a recordset, then loop through it and create a string the looks like 'Crit      NCSU','NCSU', ......

then use IN(" & strname & ")'

I'd do that just prior to your code that suns the crosstab

you will need a function to do that and modifying your crosstab querydef dynamically

i did  this before in this thread

https://www.experts-exchange.com/questions/21638202/Crosstab-Query-Pivot-Values.html?anchorAnswerId=15373703#a15373703
Avatar of Ryan

ASKER

Ok, so it really is pretty much as kelvin has said, I need to build a string inside the IN clause.  A function would be slightly better than just editing the query. Was hoping the select query would do it.

I searched and searched and couldn't find a solution to what I was looking for. I even tried to do it myself, if you can believe that.
basically, yes but the trick is how you build the *IN* string using the recordset.
Avatar of Ryan

ASKER

I used
PIVOT IIf([championship],"~","") & [EventAbbrev] IN ("WM ITT","NAVY ITT","NCSU Crit","NAVY Crit","WM RR","NCSU RR")

But the order didn't change in the query no matter what order I put in the IN(). Nor did it change within an adodb.recordset
What is the list separator in your Regonal Settings (Windows). If this is not a comma, you will need to change that comma in this string to whatever this is.

Control Panel> Regional & Language Options>Regional Options>Click Customize & look under the Numbers Tab

There is something strange (although I can't put my finger on it that is stopping the query from producing the columns in alphanumeric order without the IN statement and not sorting with it. I have a hunch that is to to with the IIf statement and the ~ or "" prior to the EventAbbrev


Kelvin
if you can zip the db, replace the extension .zip with .txt, you can attach it here
by checking the attach file
Avatar of Ryan

ASKER

Its a comma. If I remove a column from the list, it doesn't show up in the query. Adding one puts an extra column, so its working, just not for sorting.

I can remove the ~ appending if there is some other way to denote which races are championships.

Query is called "qryRacerPts"
It is called by clicking Results>Export Season in the custom toolbar.

Scoring.txt
Curious.

Sorting by EventAbbrev doen't sort and the query has a mind of it's own. I changed the query to use RaceDate as the pivot and it immediately came out in date order (no need to use the Column Headings property at all) which what I expected.

I'll delve further a bit later on. Clients waiting at the moment.


Kelvin


Avatar of Ryan

ASKER

Thanks. I'm going to try some other things when I'm free tonight.
Only other thing I've noted - if I change the Exent Abbreviation to Race Description from the same Events table (for the PIVOT line), it does sort correctly (alphabetical). There's just something weird with the abbrev field
Avatar of Ryan

ASKER

I tried making a select query first, then using the crosstab on that, but it still won't sort by date or alphabetically.

Removing the iif() before the EventAbbrev doesn't work either.
The only difference between EventAbbrev and EventDesc is that Abbrev is the table key.
Yes have tried EventAbbrev many ways without success - even tried it inside a TRIM function in case there were leading or trailing invisible characters/spaces etc.

Using RaceDate or EventDesc on their own worked fine for me


Kelvin
Avatar of Ryan

ASKER

So does anyone else have any ideas? I had a bunch of other things I've been working on with this project, but I still need to sort somehow. My desperate attempt it to prefix the headers with the date, then sorta the columns in Excel, but this will be much slower that doing it in the SQL.
ASKER CERTIFIED SOLUTION
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial