COUNT not returning zero

[blfrd76]

Lets say I have an Access 2000 DB.
It has one table called MasterFile and one table called Model

Model contains one field that lists 18 different models of cars.

MasterFile contains the fields:

DatePurchased - the date the car was purchased
TModel - any ONE model from the Model table

I need a query that:

Lists all 18 models of cars and the COUNT of each Model purchased in one particular month.

It might look like this:


SELECT MasterFile.TModel, COUNT(MasterFile.Model) AS CountMod
FROM MasterFile
WHERE DatePart('m',MasterFile.DatePurchased) = ?
GROUP BY MasterFile.TModel


This query does not return all 18 different Models. It returns only those purchased in that particular Month.

Even if the COUNT is zero, I still need to see it.

 

[cmmrfrds]

Do you need to see each model and the count for that model by month? Does masterfile hold each car that was sold with the date? Do you have a table with all the models that are possible to be sold? More info. needed.

 

[blfrd76]

I need to see each model and the count for that model by month, including counts of zero.

MasterFile holds records for the entire year. Each record includes at least one type of car and one date. For example:

MasterTable:

Model DatePurch
Tempo 02/04/04

Another table exists in the same database that contains ALL possible car models to choose from. 18 total models.
Basically I want to view stats for one particualar month. Even if the number of cars sold in January is zero, I still need to see it.

 

[cmmrfrds]

If you left join from model all the models will be listed, then convert to a 1 for a value and 0 when null and then sum. This is not tested but should be very close.

SELECT Model.Model,
sum(iif(MasterFile.Model is null,0,1)) AS CountMod
FROM Model
Left Join MasterFile
On Model.Model = MasterFile.Model
WHERE DatePart('m',MasterFile.DatePurchased) = ?
GROUP BY Model.Model

 

[blfrd76]

I tried this but to no avail. It gave an error message:

Error in list of arguments: 'IS' not recognized

What about this:

SELECT MasterModel.Model, COUNT(iif(Master.TModel IS NULL,0)) AS CountMod
FROM (MasterModel LEFT OUTER JOIN
Master ON MasterModel.Model = Master.TModel)
WHERE (DatePart('m', Master.DatePurch) = ?)
GROUP BY MasterModel.Model

The previous doesnt list ALL possible models either.

 

[cmmrfrds]

On the not including all models, once there is a where clause (which I overlooked) then it is necessary to add to the where clause an or condition. This is to check for nulls on the right side which will be null if there is no activity on a model.
OR Master.Model IS NULL

SELECT MasterModel.Model, SUM(iif(Master.TModel IS NULL,0,1)) AS CountMod
FROM (MasterModel LEFT OUTER JOIN
Master ON MasterModel.Model = Master.TModel)
WHERE (DatePart('m', Master.DatePurch) = ?)
OR Master.Model IS NULL
GROUP BY MasterModel.Model

 

[blfrd76]

No Dice.

This query still only returns Cars sold in one month. Not all possible cars.

 

[cmmrfrds]

Yeah, that is because the join condition of the equal gets resolved first and finds a model in another month, so the "is null" has no effect. Better to break into 2 queries the first of which gets the models for only 1 month and then it can be left joined.

SELECT Master.Model FROM Master
WHERE (DatePart('m', Master.DatePurch) = ?)
save this query with a name e.g. Q1

SELECT MasterModel.Model, SUM(iif(Q1.TModel IS NULL,0,1)) AS CountMod
FROM MasterModel LEFT OUTER JOIN
Q1 ON MasterModel.Model = Q1.TModel
OR Q1.Model IS NULL
GROUP BY MasterModel.Model

 

[blfrd76]

Mark this one resolved for now. SOrry for the delay.