Help with NZ(?) placement in SQL

[MrMitchell]

Good morning all,

So I'm trying to find a solution to my query not populating fields with zeros for not found records. The problem I would like to resolve is if there is 0 records in the "Total Booked" field, I would like it to populate with 0, rather than ommitting the record. I've been snooping around some of my desk references and the internet, but can't seem to figure this one out, beyond that I need to utilize the Nz operand. Here's my SQL:

SELECT Sum(IIf(([calendar]="New Customer") Or ([calendar]="Service Customer") Or ([calendar]="Address Change"),1,0)) AS [Total Booked]
FROM matchingdocument INNER JOIN June ON matchingdocument.zipcode = [June].zipcode;

I'm just not sure where in the total booked field it should be placed, given that I'm also using a Sum(IFF(, any help would be greatly appreciated!

 

[Guus2005]

If the calendar field can be NULL you have to put the Nz function around every [Calendar] occurence thus eliminating the records containing NULL value in the Total Booked field.

SELECT Sum(IIf((nz([calendar],"")="New Customer") Or (nz([calendar],"")="Service Customer") Or (nz([calendar],"")="Address Change"),1,0)) AS [Total Booked]
FROM matchingdocument INNER JOIN June ON matchingdocument.zipcode = [June].zipcode;

HTH

 

[Pat Hartman]

You have a schema design issue. In a relational database there should never be a table named June since that implies the existance of 11 other identical tables. It also means that you need 12 times the number of queries you would need with a properly designed schema and probably year end processing to clear out the old data. Put all the data ina single table and use criteria to select what you need.

Select ... From ... Where MyYearMonth = [Enter year and month];

 

[Guus2005]

You have a schema design issue. In a relational database there should never be a table named June since that implies the existance of 11 other identical tables. It also means that you need 12 times the number of queries you would need with a properly designed schema and probably year end processing to clear out the old data. Put all the data ina single table and use criteria to select what you need.

Select ... From ... Where MyYearMonth = [Enter year and month];

Now that you mention it, i missed that one!

Try Wikipedia or this link on the subject of database normalization
HTH