Cross Tab Query

[bezz]

I have a query which determines the status of documents. This can be one of about 8 different states, Under Evaluation, Draft, Not
Written etc. I am using a crosstab query to correlate the data as so many documents belong to group R, Group S, Group P etc.

I have already written a routine to check a table, if the status has already been saved, delete and append new stats, or if not yet
saved just append, so I can develop a glide path from the stats by group.

The problem is, there may be some weeks when no documents are at a status, for example none of them may be Under Evaluation, so
when appending to the table I will get an error as the fieldname will not exist?

Is there a simple way to generate all the required table names such that the crosstab will insert the contents as zero if there are
none, or do I need to use a combination of count and insert into named fields in the storage table?

Thanks
JBN

 

[John Spencer]

In a crosstab query you can specify the column names that are generated by the
field.

If you are using query design view, there is a property for entering the
column names. Just type the correct names in separated by commas.

In the sql view
TRANSFORM NZ(Count(SomeField,0)) as TheValue
SELECT FieldA, FieldB
FROM SomeTable
GROUP BY FieldA, FieldB
Pivot StatusField in ("Under Evaluation","Never Written", ...)

Be careful, the field names in the IN clause must be accurately entered. The
only fields that will show up are the ones that are in the IN clause and they
will all show up - even if mistyped. Mistyped columns will show null values
(or zero if you've used the NZ to force a zero value in place of nulls.)


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