RE: Crosstab Query to return all columns



Try this --
TRANSFORM Count([Closed Remedy PQR].[PQR Number]) AS [CountOfPQR Number]
SELECT [Closed Remedy PQR].[Report Category], Count([Closed Remedy PQR].[PQR
Number]) AS [Total Of PQR Number]
FROM [Closed Remedy PQR]
WHERE ((([Closed Remedy PQR].[Create-date])>Date()-8))
GROUP BY [Closed Remedy PQR].[Report Category]
PIVOT Format([Create-date],"yyyy/mm/dd") IN("2007/10/25", "2007/10/24",
"2007/10/23", "2007/10/22", "2007/10/21", "2007/10/20", "2007/10/19",
"2007/10/18");

One problem is that you will need to edit each day for the new dates. Also
if used for a report then the report will need to be edited for the different
field names.

--
KARL DEWEY
Build a little - Test a little


"Prasanna" wrote:

Hi Karl,
I am sorry if my question was confusing.
For the column headers date(),date()-1, etc, I actually meant, the column
headers as 2007/10/25(which is today in "yyyy/mm/dd" format), 2007/10/24 etc.
Also, if there is no data in a column, the query does not show the column at
all.

Thanks for your time.
Prasanna.


"KARL DEWEY" wrote:

Try this --
TRANSFORM Count([Closed Remedy PQR].[PQR Number]) AS [CountOfPQR Number]
SELECT [Closed Remedy PQR].[Report Category], Count([Closed Remedy PQR].[PQR
Number]) AS [Total Of PQR Number]
FROM [Closed Remedy PQR]
WHERE ((([Closed Remedy PQR].[Create-date])>Date()-8))
GROUP BY [Closed Remedy PQR].[Report Category]
PIVOT "Today " &
IIf(DateDiff("d",Date(),[Create-date])=0,"",DateDiff("d",Date(),[Create-date]));

--
KARL DEWEY
Build a little - Test a little


"Prasanna" wrote:

Hi,
I m newbie to sql. I use Access interface alot. I wanted to display last 8
days of data on crosstab query. However, the column headers are missing if
the data is not available. My query is here:
TRANSFORM Count([Closed Remedy PQR].[PQR Number]) AS [CountOfPQR Number]
SELECT [Closed Remedy PQR].[Report Category], Count([Closed Remedy PQR].[PQR
Number]) AS [Total Of PQR Number]
FROM [Closed Remedy PQR]
WHERE ((([Closed Remedy PQR].[Create-date])>Date()-8))
GROUP BY [Closed Remedy PQR].[Report Category]
PIVOT Format([Create-date],"yyyy/mm/dd");

I tried adding the column headers on right-click ---> properties. I typed in
date(),date()-1, etc. but no luck.
Thanks,
Prasanna.
.