Crosstab Query Results

[RR1976]

I am trying to set up a crosstab query where it will return all the records
in my column heading, even if they were not used in a particular time frame.
For example I have a table of different issues we work in a week. There was
one record in the table we didn't use all week. When I run this crosstab
query currently I do not get that record in my column headings. How can I
change this where it will return all of my records even if they were not used
in that particular time frame?

 

[Jerry Whittle]

Did you define the columns in the Column Heading property of the crosstab
query?

Post the SQL statement here for the crosstab query.

 

[John Spencer]

In the crosstab query you can specify the field name(s) using an In
clause in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this:
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Input your column heading values in Column Headings separated by
commas (or semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY
those crosstab columns will be visible. If you mistype a value, you
will get a column with that name and no data (all nulls) in that column.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[RR1976]

Exactly what I needed thank you!!
--
Ray Rivera

In the crosstab query you can specify the field name(s) using an In
clause in the PIVOT statement.

TRANSFORM ...
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
PIVOT MonthFieldNames In ("In Progress","On Time", "Late","Very Late")

In the query grid, you do this:
-- Select View properties
-- Click on the grey area above the grid, so you are looking at the
query's properties
-- Input your column heading values in Column Headings separated by
commas (or semicolons if your separator is semi-colons)

When you do this the specified cross-tab columns will show up and ONLY
those crosstab columns will be visible. If you mistype a value, you
will get a column with that name and no data (all nulls) in that column.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================