Formatting a Crosstab Query

[raskew]

The following crosstab query returns columns in this order:

Category | Total | Avg | Dec 08 | Jan 09 | Feb 09 | Mar 09 | Apr 09 | May 09

...however, if in query view, I manually move the Total and Avg columns as shown below,
then save the query, it will reopen in the modified format. There is no change to the SQL
so obviously something is going on behind the scenes.
NOTE: If I copy/paste the SQL to a new query, it opens in the format shown above.

Category | Dec 08 | Jan 09 | Feb 09 | Mar 09 | Apr 09 | May 09 | Total | Avg

Any ideas how a thing like this works and, if possible, link into it?

Your thoughts appreciated.

Bob

TRANSFORM Format(nz(Sum([tblTransfer].[PayAmt]),0),"Currency") AS SumOfPayAmt
SELECT tblCat.Category, Sum(tblTransfer.PayAmt) AS Total, Format(Sum([tblTransfer].[PayAmt])/6,"$#.00") AS Avg
FROM tblTransfer LEFT JOIN tblCat ON tblTransfer.CatID = tblCat.CatID
WHERE (((CDate(Format([expdte],"mm/yyyy"))) Between #12/1/2008# And #5/31/2009#))
GROUP BY tblCat.Category
ORDER BY tblCat.Category, Format([expdte],"mmm yy")
PIVOT Format([expdte],"mmm yy") In ('Dec 08','Jan 09','Feb 09','Mar 09','Apr 09','May 09');

 

[Banana]

My suspicion is that the SQL was never changed; we only changed the datasheet formatting, just as much like how you can put your controls on a form in an arbitrary order not corresponding to the query's column ordering.

One of my sample uploaded here about a classroom attendance basically re-ordered the datasheet columns using ColumnOrder (if I remember the name) property.

To do it in SQL directly, I can only see doing it with a INNER JOIN between three queries, one pivoting on months, one averaging and last totalling.

 

[raskew]

Hi Banana -

Thanks for the quick response. I'm going to have to play with your suggestion and see if I can produce something usable.

Complicating the issue (I didn't mention this in the original post) is this is in fact a dynamic query where the SQL is produced by a pair of functions that, when the user enters mm/yyyy tailors the SQL (including the Pivot By statement) to the input mm/yyyy and the preceding five months (total of six months).

Oh well, if I can get it to work for a specific set of dates, rewriting the functions is probably the easy part.

Thanks again - Bob