I have a table with the following entries

Totals salesoffice Type
319655.46 200 Cash Contribution
2607.94 200 Cash Distribution
1078475.47 200 Security Contribution
1369438.83 300 Cash Contribution
43347.03 300 Cash Distribution
1080221.3 300 Security Contribution
47362.84 300 Security Distribution

I'm using a Cross-Tab query as the source for a report. The query is

TRANSFORM Sum([Contributions & Distributions Totals].Totals) AS SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type;

which results in

Salesoffice Cash Distribution Cash Contribution Security Distribution
Security Contribution
200 2607.94 319655.46
1078475.47
300 43347.03 1369438.83 47362.84
1080221.3

When I include a textbox for Security Distribution on the report, it comes
up blank for salesoffice 200 because there isn't an entry in the original
table for it. I'm trying to figure out the best way to get a 0 to display on
the report. Can this be done at the report level or can it be done in the
cross-tab query? If not, what is the best way to handle this? I've tried NZ
but it's not NULL, it just doesn't exist period.

TIA

Matt

PERHAPS you can use the following modification to your query.

TRANSFORM CDbl(Nz(Sum([Contributions & Distributions Totals].Totals),0)) AS
SumOfTotals
SELECT [Contributions & Distributions Totals].salesoffice
FROM [Contributions & Distributions Totals]
GROUP BY [Contributions & Distributions Totals].salesoffice
PIVOT [Contributions & Distributions Totals].type
IN ("Cash Contribution"
,"Cash Distribution"
,"Security Contribution"
,"Security Distribution")

The transform line will ensure that zero is returned the sum is null.

The In clause will ensure that the four columns are always returned even if
you limit the query to a sales office that does not have one of the four types
of data.

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

Matt Williamson wrote:
> I have a table with the following entries
>
> Totals salesoffice Type
> 319655.46 200 Cash Contribution
> 2607.94 200 Cash Distribution
> 1078475.47 200 Security Contribution
> 1369438.83 300 Cash Contribution
> 43347.03 300 Cash Distribution
> 1080221.3 300 Security Contribution
> 47362.84 300 Security Distribution
>
> I'm using a Cross-Tab query as the source for a report. The query is
>
> TRANSFORM Sum([Contributions & Distributions Totals].Totals) AS SumOfTotals
> SELECT [Contributions & Distributions Totals].salesoffice
> FROM [Contributions & Distributions Totals]
> GROUP BY [Contributions & Distributions Totals].salesoffice
> PIVOT [Contributions & Distributions Totals].type;
>
> which results in
>
> Salesoffice Cash Distribution Cash Contribution Security Distribution
> Security Contribution
> 200 2607.94 319655.46
> 1078475.47
> 300 43347.03 1369438.83 47362.84
> 1080221.3
>
> When I include a textbox for Security Distribution on the report, it comes
> up blank for salesoffice 200 because there isn't an entry in the original
> table for it. I'm trying to figure out the best way to get a 0 to display on
> the report. Can this be done at the report level or can it be done in the
> cross-tab query? If not, what is the best way to handle this? I've tried NZ
> but it's not NULL, it just doesn't exist period.
>
> TIA
>
> Matt
>
>
>

Matt Williamson wrote:

>I have a table with the following entries
>
>Totals salesoffice Type
>319655.46 200 Cash Contribution
>2607.94 200 Cash Distribution
>1078475.47 200 Security Contribution
>1369438.83 300 Cash Contribution
>43347.03 300 Cash Distribution
>1080221.3 300 Security Contribution
>47362.84 300 Security Distribution
>
>I'm using a Cross-Tab query as the source for a report. The query is
>
>TRANSFORM Sum([Contributions & Distributions Totals].Totals) AS SumOfTotals
>SELECT [Contributions & Distributions Totals].salesoffice
>FROM [Contributions & Distributions Totals]
>GROUP BY [Contributions & Distributions Totals].salesoffice
>PIVOT [Contributions & Distributions Totals].type;
>
>which results in
>
>Salesoffice Cash Distribution Cash Contribution Security Distribution
>Security Contribution
>200 2607.94 319655.46
>1078475.47
>300 43347.03 1369438.83 47362.84
>1080221.3
>
>When I include a textbox for Security Distribution on the report, it comes
>up blank for salesoffice 200 because there isn't an entry in the original
>table for it. I'm trying to figure out the best way to get a 0 to display on
>the report. Can this be done at the report level or can it be done in the
>cross-tab query? If not, what is the best way to handle this? I've tried NZ
>but it's not NULL, it just doesn't exist period.
>

You can use NZ in the query or the text box, if you
guarantee that the entry is included in the query.

This can be done by using an outer join from a table that
has a record for each sales office. Maybe something like:

TRANSFORM Sum([Contributions & Distributions Totals].Totals)
AS SumOfTotals
SELECT [Sales Offices].salesoffice
FROM [Sales Offices] LEFT JOIN [Contributions &
Distributions Totals]
ON [Sales Offices].salesoffice = [Contributions &
Distributions Totals].salesoffice
GROUP BY [Sales Offices].salesoffice
PIVOT [Contributions & Distributions Totals].type

--
Marsh
MVP [MS Access]

"John Spencer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> PERHAPS you can use the following modification to your query.
>
> TRANSFORM CDbl(Nz(Sum([Contributions & Distributions Totals].Totals),0))
> AS SumOfTotals
> SELECT [Contributions & Distributions Totals].salesoffice
> FROM [Contributions & Distributions Totals]
> GROUP BY [Contributions & Distributions Totals].salesoffice
> PIVOT [Contributions & Distributions Totals].type
> IN ("Cash Contribution"
> ,"Cash Distribution"
> ,"Security Contribution"
> ,"Security Distribution")
>
> The transform line will ensure that zero is returned the sum is null.
>
> The In clause will ensure that the four columns are always returned even
> if you limit the query to a sales office that does not have one of the
> four types of data.

That did the trick. Thanks again John!

Matt