Returning 0 for fields with no records

[RussCRM]

I have a table called tblServices with the fields ServicesID,
ServicesType, ServicesQuantity, ServicesDate
It contains entries for services we distribute, etc.

I have another table called tblServicesLists with the fields
ServiceTypeID, ServiceType, ServiceSort

I use the tblServicesLists for combo boxes to allow users to select
from the ServiceType options. The type selected is inserted into
tblServices as ServicesType.

I would like to get a query of the quantities of each service in the
tblServicesLists table.

If I use the following query (using a date range based on a form), I
get quantities for only those services which have records in
tblServices.

SELECT tblServices.ServicesType,
Count(DCount("ServicesLink","tblServices","")) AS ServiceCount
FROM tblServicesLists INNER JOIN tblServices ON
tblServicesLists.ServiceSelect = tblServices.ServicesType
WHERE (((tblServices.ServicesDate) Between [Forms]!
[frm_Statistics_Range]![txtStartDate] And [Forms]!
[frm_Statistics_Range]![txtEndDate]))
GROUP BY tblServices.ServicesType;

However, what I want is totals for all service types, with a 0 for all
types that have no records in tblServices.

Thanks!

[Salad]

RussCRM wrote:

I have a table called tblServices with the fields ServicesID,
ServicesType, ServicesQuantity, ServicesDate
It contains entries for services we distribute, etc.

I have another table called tblServicesLists with the fields
ServiceTypeID, ServiceType, ServiceSort

I use the tblServicesLists for combo boxes to allow users to select
from the ServiceType options. The type selected is inserted into
tblServices as ServicesType.

I would like to get a query of the quantities of each service in the
tblServicesLists table.

If I use the following query (using a date range based on a form), I
get quantities for only those services which have records in
tblServices.

SELECT tblServices.ServicesType,
Count(DCount("ServicesLink","tblServices","")) AS ServiceCount
FROM tblServicesLists INNER JOIN tblServices ON
tblServicesLists.ServiceSelect = tblServices.ServicesType
WHERE (((tblServices.ServicesDate) Between [Forms]!
[frm_Statistics_Range]![txtStartDate] And [Forms]!
[frm_Statistics_Range]![txtEndDate]))
GROUP BY tblServices.ServicesType;

However, what I want is totals for all service types, with a 0 for all
types that have no records in tblServices.

Thanks!

Change the word Inner to Left.

[RussCRM]

Didn't work :(

[Salad]

Salad wrote:

RussCRM wrote:

>I have a table called tblServices with the fields ServicesID,
ServicesType, ServicesQuantity, ServicesDate
It contains entries for services we distribute, etc.

I have another table called tblServicesLists with the fields
ServiceTypeID, ServiceType, ServiceSort

I use the tblServicesLists for combo boxes to allow users to select
from the ServiceType options. The type selected is inserted into
tblServices as ServicesType.

I would like to get a query of the quantities of each service in the
tblServicesLists table.

If I use the following query (using a date range based on a form), I
get quantities for only those services which have records in
tblServices.

SELECT tblServices.ServicesType,
Count(DCount("ServicesLink","tblServices","")) AS ServiceCount
FROM tblServicesLists INNER JOIN tblServices ON
tblServicesLists.ServiceSelect = tblServices.ServicesType
WHERE (((tblServices.ServicesDate) Between [Forms]!
[frm_Statistics_Range]![txtStartDate] And [Forms]!
[frm_Statistics_Range]![txtEndDate]))
GROUP BY tblServices.ServicesType;

However, what I want is totals for all service types, with a 0 for all
types that have no records in tblServices.

Thanks!

Change the word Inner to Left.

Not sure what Count(DCount()) does.

Try this. Open up your query in design mode. Dbl-Click on the line and
change it to All in ServicesList and Matching in ServicesType. I might
have your table names messed up, adjust as required.

Create a calculated column called ServicesCount in the query
ServicesCount : IIF(Not IsNull(ServicesType!ServicesSelect,1,0)
Now in the Totals band use Sum to get the ServicesCount.

[RussCRM]

Here's what I came up with. Seems to work:

SELECT tblServicesLists.ServiceSelect, Sum(IIf(Not
IsNull([tblServices]![ServicesType]),1,0)) AS ServicesCount
FROM tblServicesLists LEFT JOIN tblServices ON
tblServicesLists.ServiceSelect = tblServices.ServicesType
GROUP BY tblServicesLists.ServiceSelect;

[RussCRM]

Except when I limit the date range...

[RussCRM]

I used another query to filter by date and then used that query in
place of my tblServices. Seems to work.