Null Values

[Neilbees]

Hope someone can help with this. I'm sure it's dead simple but it's driving me insane!

The scenario is a number of departments who are processing something (sales for example). So, for October

Dept 1 - 20 (Sales)
Dept 2 - 10
Dept 3 - 2
Dept 4 - 7

Now I want to count the number of sales completed from these totals. However, as Dept 3 has no sales completed for October it does not show in the query. So I get

Dept 1 - 7
Dept 2 - 2
Dept 4 - 6

Any idea how to get Dept 3 - 0 to show up in the query?

Any help greatly appreciated!

 

[Matt Greatorex]

Treat the null as a zero, for the purposes of your calculation

e.g. Total = IIf(isnull(Field1,0,Field1)) + IIf(isnull(Field2,0,Field2))

In other words, if Field1 or Field 2 contain anything at all, use that value, otherwise treat each as if it contains zero.

Hope it helps.

 

[Neilbees]

Hi

Thanks for your reply. I think I explained what I was trying to do very badly (or maybe I didn't and I'm just stupid!)

Anyway, I still can't work it out. I've attached a screenshot of the 2 queries and the results I'm getting. You can see that the count is based on a Where criteria - Yes on one query and No on the other.

In the bottom query you can see that there are no results for the sector HLQ. This is because they literally have no results that match the criteria of the query. So can I make the query return HLQ - 0 when there are no results to show?

I think I've explained this badly again, but oh well! Hope someone can help.

 

[Matt Greatorex]

There may well be an easier way, but you could do it in two stages.

1) Select all sectors from the table - no Yes/No where clause here, just the date range - into a new table.
2) Select all sectors from the new table LEFT JOIN to the original table, on sector and date range. Also select the other fields you need from the original table, at this point.

I think this should give you a list of all sectors and all of their corresponding values. Where there is no value, it should be displayed as a zero.