returning zero for a null

[TheReallyWhiteRunner]

I am performing a count function in a query to give me total accounts
grouped by state, region, district. Problem is some districts do not
have account #s in them and they are not on the results of the query.
I've been trying to get the results to return a zero for the null
districts, but I cannot structure it correctly. Can anyone give me
more insight as to the best way to do this and how/where I should
insert the function.

 

[TheReallyWhiteRunner]

I am performing a count function in a query to give me total accounts
grouped by state, region, district.  Problem is some districts do not
have account #s in them and they are not on the results of the query.
I've been trying to get the results to return a zero for thenull
districts, but I cannot structure it correctly.  Can anyone give me
more insight as to the best way to do this and how/where I should
insert the function.

Here is the actual SQL:

SELECT [Regions- 2008].State, [NFB Products- Liquid].Arena, [NFB
Products- Liquid].Category, Count([New- 20080208].[Account #]) AS
[CountOfAccount #]
FROM ([New- 20080208] LEFT JOIN [Regions- 2008] ON [New- 20080208].
[Branch #] = [Regions- 2008].[Original Branch #]) LEFT JOIN [NFB
Products- Liquid] ON [New- 20080208].Product = [NFB Products-
Liquid].Product
WHERE ((([NFB Products- Liquid].Category)="dda" Or ([NFB Products-
Liquid].Category)="Now") AND (([NFB Products- Liquid].Arena) Not Like
"public"))
GROUP BY [Regions- 2008].State, [NFB Products- Liquid].Arena, [NFB
Products- Liquid].Category;

 

[Allen Browne]

The criteria you applied on the fields from the table on the outer side of
the join have the effect of eliminiating the nulls. Consequently the query
behaves as if it had an INNER JOIN.

One way around this would be to pre-filter:

1. Create a query using your [NFB Products- Liquid] table.
Include the critera here for the Category and Arena fields.
Save the query.

2. In your main query, replace the [NFB Products- Liquid] table with the
query you just saved, and leave the criteria out.

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

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

I am performing a count function in a query to give me total accounts
grouped by state, region, district. Problem is some districts do not
have account #s in them and they are not on the results of the query.
I've been trying to get the results to return a zero for thenull
districts, but I cannot structure it correctly. Can anyone give me
more insight as to the best way to do this and how/where I should
insert the function.

Here is the actual SQL:

SELECT [Regions- 2008].State, [NFB Products- Liquid].Arena, [NFB
Products- Liquid].Category, Count([New- 20080208].[Account #]) AS
[CountOfAccount #]
FROM ([New- 20080208] LEFT JOIN [Regions- 2008] ON [New- 20080208].
[Branch #] = [Regions- 2008].[Original Branch #]) LEFT JOIN [NFB
Products- Liquid] ON [New- 20080208].Product = [NFB Products-
Liquid].Product
WHERE ((([NFB Products- Liquid].Category)="dda" Or ([NFB Products-
Liquid].Category)="Now") AND (([NFB Products- Liquid].Arena) Not Like
"public"))
GROUP BY [Regions- 2008].State, [NFB Products- Liquid].Arena, [NFB
Products- Liquid].Category;