Crosstab Null value display as a "0"

[Leah]

My crosstab is built on one feild from a table and the corresponding feild
list table as a left-outer join. The crosstab returns the total by month for
each of the items in the field and if there were no records returns a zero
for a grand total. When I try to add the date field from the first table and
query for a specific time period, I lose all of the items with null values.
I have tried to use the NZ function, but with no luck. Either I am building
the query incorrectly of I have built my crosstab incorrectly. Would someone
please advise.

 

[Allen Browne]

Presumably the date field is in the table that's on the outer side of the
join. Then the criteria eliminates the nulls, and so the net result is the
same as if you had used an inner join.

If that's what's going on, it might be simplest to split this into 2
queries. Create an ordinary SELECT query that returns the records you want
from the table on the outer side of the join, with the date criteria. Save
this query.

Now create a crosstab that uses this query as an input "table", instead of
the table you currently have. In this new query, use the outer join between
your table and this lower-level query. Since the criteria already operated
before the lower query returned its records, the outer join will work
correctly.

If you need to use parameters for the lower-level query, be sure to declare
them. For example, if you are using a form that has text boxes for StartDate
and EndDate, the criteria might be:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
Then in query design (in this lower-level query), choose Parameters on the
Query menu, and enter 2 rows:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

For other crosstab tricks:
http://allenbrowne.com/ser-67.html

 

[Leah]

Thank you, I have spent hours trying to figure this out.
Leah

Presumably the date field is in the table that's on the outer side of the
join. Then the criteria eliminates the nulls, and so the net result is the
same as if you had used an inner join.

If that's what's going on, it might be simplest to split this into 2
queries. Create an ordinary SELECT query that returns the records you want
from the table on the outer side of the join, with the date criteria. Save
this query.

Now create a crosstab that uses this query as an input "table", instead of
the table you currently have. In this new query, use the outer join between
your table and this lower-level query. Since the criteria already operated
before the lower query returned its records, the outer join will work
correctly.

If you need to use parameters for the lower-level query, be sure to declare
them. For example, if you are using a form that has text boxes for StartDate
and EndDate, the criteria might be:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
Then in query design (in this lower-level query), choose Parameters on the
Query menu, and enter 2 rows:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

For other crosstab tricks:
http://allenbrowne.com/ser-67.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

My crosstab is built on one feild from a table and the corresponding feild
list table as a left-outer join. The crosstab returns the total by month
for
each of the items in the field and if there were no records returns a zero
for a grand total. When I try to add the date field from the first table
and
query for a specific time period, I lose all of the items with null
values.
I have tried to use the NZ function, but with no luck. Either I am
building
the query incorrectly of I have built my crosstab incorrectly. Would
someone
please advise.