Crosstab Query to return all columns

G

Guest

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.
 
G

Guest

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]));
 
J

John Spencer

To make this work you would have to be satisfied with relative dates

Day1, Day2, Day3

PIVOT "Day" & DateDiff("d",[Create-Date],Date()) in ("Day1", "Day2",...,
"Day8")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
G

Guest

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
G

Guest

John,
This works fine. It retrieves all the columns for me. But, is there a way to
get dates displayed as column headers.? It is actually [Create-date] that
that needs to be displayed as column headers.
As I am importing this to the spreadsheet via ADO, column headers would be
great.
Thanks,
Prasanna.


John Spencer said:
To make this work you would have to be satisfied with relative dates

Day1, Day2, Day3

PIVOT "Day" & DateDiff("d",[Create-Date],Date()) in ("Day1", "Day2",...,
"Day8")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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.
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

There probably is a way. It is complex and would require that you have
a table of dates.

Other than that you could use VBA to build the query when needed or
hard-code the date values into the query each time the date changed.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

John,
This works fine. It retrieves all the columns for me. But, is there a way to
get dates displayed as column headers.? It is actually [Create-date] that
that needs to be displayed as column headers.
As I am importing this to the spreadsheet via ADO, column headers would be
great.
Thanks,
Prasanna.


John Spencer said:
To make this work you would have to be satisfied with relative dates

Day1, Day2, Day3

PIVOT "Day" & DateDiff("d",[Create-Date],Date()) in ("Day1", "Day2",...,
"Day8")

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cross tab queries to return all columns including blanks 3
How to return multiple columns from a single column table? 2
Error 3205 - Too Many Crosstab Column Headers (300) 2
Count Crosstab Resultant Columns 1
Help with crosstab query 10
Parameter Queries in Crosstab Query 2
Adding column totals to crosstab queries in ACCESS 2003 1
Crosstab Problem 1

Top