Cross tab queries to return all columns including blanks

[prasanna.janardhana]

Hi,
I came up with this crosstab query which gives 8 days worth of
data.However, if
my database has only 6 days of data and 2 days in between are blank,
this query
just ignores those two days and displays only 6 days.
I have attached my code and output in here.
How can I change this to display blank fields as well?
Please help,
Thanks,
Prasanna.

sSQL = " TRANSFORM Count([Open Remedy PQR].[PQR Number]) AS
[CountofPQR] " & _
" SELECT [Open Remedy PQR].[Report Category]," & _
" Count([Open Remedy PQR].[PQR Number]) AS [Total of PQR Number]" & _
" FROM [Open Remedy PQR] " & _
" WHERE ([Report Category] <> '[Not Reported]'" & _
" AND ([Create-date] >= DATEADD('d', -8,Date() )))" & _
" GROUP BY [Report Category] " & _
" PIVOT Format([Create- date],'yyyy/ mm/dd');"

 

[Guest]

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be. If not, there are ways to dynamically create the column headings and
controls on the form/report. Ask again if you need that solution.

 

[kumarpras2000]

One way to fix this problem is to open thecrosstabquery in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want as the months above would normally do APR,
AUG, etc. You can also make data not show up by taking out a column. For
example, remove JAN and January data won't show. And for your needs, it will
create an empty field for the form or report even if there is no matching
data.

Of course this only works if you always know what your column heading need
to be. If not, there are ways to dynamically create the column headings and
controls on the form/report. Ask again if you need that solution.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi,
I came up with thiscrosstabquery which gives 8 days worth of
data.However, if
my database has only 6 days of data and 2 days in between are blank,
this query
just ignores those two days and displays only 6 days.
I have attached my code and output in here.
How can I change this to display blank fields as well?
Please help,
Thanks,
Prasanna.

sSQL = " TRANSFORM Count([Open Remedy PQR].[PQR Number]) AS
[CountofPQR] " & _
" SELECT [Open Remedy PQR].[Report Category]," & _
" Count([Open Remedy PQR].[PQR Number]) AS [Total of PQR Number]" & _
" FROM [Open Remedy PQR] " & _
" WHERE ([Report Category] <> '[Not Reported]'" & _
" AND ([Create-date] >= DATEADD('d', -8,Date() )))" & _
" GROUP BY [Report Category] " & _
" PIVOT Format([Create- date],'yyyy/ mm/dd');"- Hide quoted text -

- Show quoted text -

Jerry,
I wanted the second solution. My code should have dates as column
headers, like 2007/07/26,2007/07/27........to the current date.
Thanks,
prasanna.

 

[Kenichi.Sumi]

Hi Jerry,

We are looking for a similar behavior, which is to create columns
dynamically based on the cross_tab table. How can we achieve this?

Cheers,
Kenichi