Including fields that have a total count of zero

  • Thread starter Thread starter tope12
  • Start date Start date
T

tope12

Here is the query.

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE (((a.date) Between forms![date range]![Start Date] And forms![Date
Range]![End Date]))
GROUP BY a.com_type;

It returns

com_type Total

mail 10
email 20

I want the query to return this output:

com_type Total

mail 10
email 20
phone 0
Fax 0


Is this possible?
 
I assume you have fields named com_num and com_type.
Try this --
SELECT Count(Nz([a].[com_num],0)) AS Total, a.com_type
FROM a
WHERE (((a.Date) Between [forms]![date range]![Start Date] And [forms]![Date
Range]![End Date]))
GROUP BY a.com_type;

You should not use Date as a field name as it is a reserved word and might
cause problems.
 
it gave me the same result.

KARL DEWEY said:
I assume you have fields named com_num and com_type.
Try this --
SELECT Count(Nz([a].[com_num],0)) AS Total, a.com_type
FROM a
WHERE (((a.Date) Between [forms]![date range]![Start Date] And [forms]![Date
Range]![End Date]))
GROUP BY a.com_type;

You should not use Date as a field name as it is a reserved word and might
cause problems.
--
KARL DEWEY
Build a little - Test a little


tope12 said:
Here is the query.

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE (((a.date) Between forms![date range]![Start Date] And forms![Date
Range]![End Date]))
GROUP BY a.com_type;

It returns

com_type Total

mail 10
email 20

I want the query to return this output:

com_type Total

mail 10
email 20
phone 0
Fax 0


Is this possible?
Click to expand...
Click to expand...
 
Try this --
SELECT Count(Nz([a].[com_type],0)) AS Total, a.com_type
FROM a
WHERE (((a.Date) Between [forms]![date range]![Start Date] And [forms]![Date
Range]![End Date]))
GROUP BY a.com_type;

--
KARL DEWEY
Build a little - Test a little


tope12 said:
it gave me the same result.

KARL DEWEY said:
I assume you have fields named com_num and com_type.
Try this --
SELECT Count(Nz([a].[com_num],0)) AS Total, a.com_type
FROM a
WHERE (((a.Date) Between [forms]![date range]![Start Date] And [forms]![Date
Range]![End Date]))
GROUP BY a.com_type;

You should not use Date as a field name as it is a reserved word and might
cause problems.
--
KARL DEWEY
Build a little - Test a little


tope12 said:
Here is the query.

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE (((a.date) Between forms![date range]![Start Date] And forms![Date
Range]![End Date]))
GROUP BY a.com_type;

It returns

com_type Total

mail 10
email 20

I want the query to return this output:

com_type Total

mail 10
email 20
phone 0
Fax 0


Is this possible?
Click to expand...
Click to expand...
Click to expand...
 
Save your first query as Q2 (or whatever name you want)

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE a.date Between forms![date range]![Start Date]
And forms![Date Range]![End Date]))
GROUP BY a.com_type

Now combine the query with a unique list of values for Com_type.
SELECT Q1.Com_Type, NZ(Q2.Total,0) as TCount
FROM (SELECT Distinct Com_Type FROM A) as Q1
LEFT JOIN Q2 On Q1.Com_Type = Q2.Com_Type

If you have a table with the Com_types in it as reference table then you can
make that simpler.

SELECT Q1.Com_Type, NZ(Q2.Total,0) as TCount
FROM tableComTypes as Q1
LEFT JOIN Q2 On Q1.Com_Type = Q2.Com_Type



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
That worked. Thanks everybody!!

John Spencer said:
Save your first query as Q2 (or whatever name you want)

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE a.date Between forms![date range]![Start Date]
And forms![Date Range]![End Date]))
GROUP BY a.com_type

Now combine the query with a unique list of values for Com_type.
SELECT Q1.Com_Type, NZ(Q2.Total,0) as TCount
FROM (SELECT Distinct Com_Type FROM A) as Q1
LEFT JOIN Q2 On Q1.Com_Type = Q2.Com_Type

If you have a table with the Com_types in it as reference table then you can
make that simpler.

SELECT Q1.Com_Type, NZ(Q2.Total,0) as TCount
FROM tableComTypes as Q1
LEFT JOIN Q2 On Q1.Com_Type = Q2.Com_Type



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Here is the query.

SELECT Nz(Count(a.com_num),0) AS Total, a.com_type
FROM a
WHERE (((a.date) Between forms![date range]![Start Date] And forms![Date
Range]![End Date]))
GROUP BY a.com_type;

It returns

com_type Total

mail 10
email 20

I want the query to return this output:

com_type Total

mail 10
email 20
phone 0
Fax 0


Is this possible?
Click to expand...
Click to expand...
 
Back
Top