Crosstab Column Headings Ordering and "yyyy mm"

[hoggbottom59]

There have been many postings on this subject.

The problem I am interested in solving is the ordering of the column headings. The column headings can be fixed but problems arise if the user wishes to select a different range of dates.

The only way that dated columns will appear in order is if the format "yyyy mm" is used which then produces dates similar to 2001 06 and 2002 01. This is both confusing and not very pretty.

Is there a way to order these columns properly but also title them more informatively? Perhaps using some kind of look-up for the pivot?

Leon.

 

[MichaelRed]

Cross tab queries have the "Columns" property. You can set te results to Forced / fixed using this. I dont use / recommed this as the qurey results (columns) then become static. Another approach is to form a second recordset from the source with the column ordering info and then use that as the outter join to the original recordset as the column headers fieldIn this process, you can create different formats for the date, and use one as the order by while the other is the 'caption'.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[hoggbottom59]

I can see what you mean but I am a bit confused as how to do it.

Do you mean to have a second query which includes the first and adds another table to bring in the column headings?

The SQL for the crosstab is:

TRANSFORM Count(tblNEWHelpdeskMain.LogNumber) AS [The Value]
SELECT tblNEWHelpdeskMain.CallType, Count(tblNEWHelpdeskMain.LogNumber) AS [Call Type Total]
FROM tblNEWHelpdeskMain
WHERE (((tblNEWHelpdeskMain.Opened) Between #5/1/2001# And #1/31/2002#))
GROUP BY tblNEWHelpdeskMain.CallType
PIVOT Format([Opened],"yyyy mm"

The dates will obviously be set to parameters when complete.

Leon.

 

[johpje]

You should be able to sort by a field which is not included in the select.
Ex:

SELECT *
FROM table
ORDER BY format(tabledate, "yyyymm"

Johan

 

[MichaelRed]

Hmmmmmmmmm,

Sort of. Looking at your sql stmt, I would have a single query, which generated the col hdr in the propper format.

Join the query to the TABLE and use its field as the piviot item.

Unfortunatly, I was attempting to show this, and found that I cannnot make it work for your set-up.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over