I have got a routine to store statistics of document status on a weekly basis to identify trends. There are several status points
of documents, for example, Not Written, Draft, Preview, In Work, Complete, Under Evaluation, Completed.

These are also spread across 4 departments. I use a straightforward select query to detemine status, then a crosstab based on that
query to count the number of documents at a certain status within each department. The routine then checks to see if an update
already has been made that week, if so deletes the week concerned and updates via an append query based on the crosstab, and if not
already saved (i.e. first occurence) just runs the append query.

The problem arises where for a given week, there are no documents in a particular category, i.e. draft, in which case the fields
are not created in the append query and there is an error generated.

I relaise that a more longwinded way of doing this is through Dcount, and Nz but that would be a lot more complicated and not very
elegant. Is there any way to make a crosstab work around base fields and have the contents as zero if they are?

Thanks

Bz


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

On Mar 23, 1:44*pm, bezz <b... (AT) hotmail (DOT) com> wrote:
I believe you just need edit the column headings property of the query
so you'd end up with:
"Not Written", "Draft", "Preview", "In Work", "Complete", "Under
Evaluation", "Completed"

The column headings won't work as the cross tab reads the status and department and then counts number of each category against
each department. If there are zero to count then it doesn't read the category?

Bz


--
--------------------------------- --- -- -
Posted with NewsLeecher v4.0 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

bezz wrote:
Another way that does not involve Dcount and Nz is an aggregate query (you
do not need to use TRANSFORM to do a crosstab ...):

select Week, Department,
Sum(Iif([Status="Not Written",1,0)) as [Not Written],
Sum(Iif([Status="Draft",1,0)) as [Draft],
etc.
From YourStatusQuery
Group By Week, Department

Since you know the various categories at design time, you don't need to use
the Crosstab Query wizard.

On Mar 23, 2:57*pm, bezz <b... (AT) hotmail (DOT) com> wrote:
I was pretty sure the column header property was linked to the 'pivot
column in ('value1','value2')' clause in the sql thats generated for a
crosstab, but if thats not the case then the format of the sql for the
crosstab needs to be:

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (value1[, value2[, ...]])]

http://office.microsoft.com/en-us/ac...001032277.aspx

that should create the column counts for the values named regardless
of the count of the values.

The column headings will work. The column headings should be the different
values that are in category.

Something like the following

TRANSFORM CLong(Nz(Count(Documents),0)) as TheValue
SELECT Department
FROM [YourTable]
WHERE StatusDate Between #1/1/2011# and #1/7/2011#
GROUP BY Department
PIVOT Category IN ("Not Written","Draft","Preview", "In
Work","Completed","Under Evaulation")

Just enter all the relevant values in the In clause of Pivot phrase. If there
is a status that is not relevant to the period, it will still show up in the
results with zero as the number of documents.

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

On 3/23/2011 3:57 PM, bezz wrote: