Crosstab Query Header Fields (?)

[croy]

Two similar, crosstab queries that act a little differently:

The first one, hand-built, repeats "Pcent" for all column
header fields, whether or not I enter anything in the Column
Header property. SQL follows.

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes = qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT qryEDMTabWkDay02.ChartHour;


The second, built with the "wizard", shows proper (expected)
times, like 05:00:00 for column headers, whether or not I
enter anything in the Column Header property:

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT [000_Test_000].qryEDMTabWkDay02.DayTypes
FROM 000_Test_000
GROUP BY [000_Test_000].qryEDMTabWkDay02.DayTypes
PIVOT [000_Test_000].ChartHour;

By the way, the "ChartHour" field is from a table with
specific hours entered in the ChartHour field, like 05:00,
06:00, etc., and the data type is Date/Time.

Can anyone here identify why these two queries would display
the column headers differently?

 

[Duane Hookom]

As per a recent reply to you in another thread, i would force the format of
the ChartHour and specify the column headings:

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") IN
("5:00","6:00","7:00","8:00",..more here..,"21:00","22:00");

 

[croy]

As per a recent reply to you in another thread, i would force the format of
the ChartHour and specify the column headings:

TRANSFORM Sum([APH]/[TtlAPH])*100 AS Pcent
SELECT qryEDMTabWkDay03.DayTypes
FROM qryEDMTabWkDay02 INNER JOIN qryEDMTabWkDay03 ON
qryEDMTabWkDay02.DayTypes =
qryEDMTabWkDay03.DayTypes
GROUP BY qryEDMTabWkDay03.DayTypes
PIVOT Format(qryEDMTabWkDay02.ChartHour,"H:nn") IN
("5:00","6:00","7:00","8:00",..more here..,"21:00","22:00");

Now that's interesting. I used the last couple of lines in
your example above as a guideline in modifying my query, and
fed it to the report, which ran fine. When I had tried
putting in the Column Heading property while working in the
QBD grid, the report would always prompt me for each of the
Hour fields.

With the Format statement (function?) in there, running the
query in data view shows the Column Headers like "05:00"
(expected). But if I open the report in design view, and
open the field list, the fields are listed like "05:00:00"
(surprise to me!). Nevertheless, the report runs ok. So it
must be that the report is getting the unformatted
[ChartHour]? I really don't understand the details here.

Anyway, I haven't been able to see if your input has this
query and report to the point where it will run on the
"other" machine, and won't be able to until Monday, when the
other user gets back from the field.

It turns out that the answer to the question I was asking
here, is that in one of the queries I had set a Caption
("Pcent") on the Value field, and removing that gave me the
ChartHour headers.

 

[croy]

It turns out that the answer to the question I was asking
here, is that in one of the queries I had set a Caption
("Pcent") on the Value field, and removing that gave me the
ChartHour headers.

For anyone interested in the "other thread", see
"Report Runs on One Machine, but Not the Other"
over in microsoft.public.access.reports.