How would I join a value of zero when a left join doesn't match?

Question

I'm looking to join the field Count from the query qCallCount to the results of the query qCustomers. I think I want to use a left join because there are customers in the qCustomers dataset that won't have a match in the qCallCount dataset, but I don't want to drop them from the results.

My current query...

SELECT a.CustomerID, b.Count, a.Customer_Segmentation
FROM qCustomers AS a LEFT JOIN qCallCount AS b ON a.CustomerID=b.CustomerID;

My question is, is there a way to make the value for the Count field in the result of this query have a value of 0 if there's no match in qCallCount? If this was Excel I'd write an =IFERROR(VLOOKUP(CustomerID, qCallCount, Count, False),0)

Why do I need this? I'm ultimately going to run an average of count avg(Count) as Average and it's important for those failed matches to be treated as zero, because that's what they actually are.

Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

Check null value in MS Access Query

You can check for IsNull(b.Count, 0)

Is Count the name of a column, or are you using the function Count?

edited May 23 '17 at 12:13

Community

1
1

answered Aug 22 '14 at 18:09

nshah

340
1
5

Count is the name of the column. – ChrisG Aug 22 '14 at 18:23
@nshah You should post this as an answer. Nice finding. – peter.petrov Aug 22 '14 at 18:57
Thank you Nshah. I created ErikE's answer (which was correct) before he did because of you answer and follow up comment. – ChrisG Aug 22 '14 at 19:21

score 0 · Accepted Answer · answered Aug 22 '14 at 19:08

The VB/VBA/MS Access null-coalescing function is Nz. (In VB6, IsNull is not the same as in SQL Server--it's a different function that returns a Boolean indicating whether the value is Null.) So it would look like this:

SELECT
   a.CustomerID,
   Nz(b.Count, 0) AS CallCount,
   a.Customer_Segmentation
FROM
   qCustomers AS a
   LEFT JOIN qCallCount AS b ON a.CustomerID=b.CustomerID;

How would I join a value of zero when a left join doesn't match?

2 Answers2