Basing Report on Crosstab

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

One of the fields on a data entry form is a "Status" field, from which the
user can select from about 5 different states (such as Approved, Denied,
Pending, etc.) I made a report based on a crosstab query that nicely shows
each status type as a column heading, and shows a count of how many of each
Status type have been entered for various dates (dates being row headings).

I tested the report when there was at least one entry for each possible
Status, and it works great. But when I truy to run the report for periods
when some of the Status possibilities are blank, for example, no Denied
entries, I get an error message that the Report does not recognize 'Denied"
as a valid field name.

It makes sense, since the Report does specify that one of the fields is
Denied, but that field is not populated in the crosstab table if there are no
denied entries, and there is no longer a Denied column in that case.

Is there a way around this? I like being able to make a column heading for
each of the possible data for "Status ," and if there are no "Denied"s, to
still run the report but leave that column blank.

Thanks!
 
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-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Crosstab queries have a Column Heading property that you can enter:
Column Headings: "Approved", "Denied", "Pending", "etc"
 
OK-- I found the answer. I used IN in the Pivot SQL statement. Someone had
asked a similar question, but I didn't see it at first. (This site is a great
resource!)
 
Back
Top