Count in Query to equal 0 instead of null

G

Guest

Hi,

I am trying to get my Count field to show 0 even if there is no data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]
 
C

Chris2

wpshop said:
Hi,

I am trying to get my Count field to show 0 even if there is no data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]
Click to expand...

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
 
G

Guest

Is there any easier solution? I have 8 append queries that I am working on
creating and I would have to apply this logic to all 8. Every post I have
searched says Nz works and the user gets back the right results but mine will
not work and I'm not sure why. I am on 2003.



Chris2 said:
wpshop said:
Hi,

I am trying to get my Count field to show 0 even if there is no data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]
Click to expand...

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
Click to expand...
 
J

John Spencer

It would help if you posted the entire query you are trying to run instead
of a snippet.

If NO record is returned then NZ will have no effect on the outcome. For
instance this sample will return no record

SELECT FieldA, Nz(Count(*),0)
FROM SomeDatabase
WHERE FieldA= "no such value exists"
Group By FieldA

That will return no records and therefore NZ has no effect since it has no
field to act on.

On the other hand, this will return one record and it will have a value in
the one field
SELECT Count(*)
FROM SomeDatabase
WHERE FieldA= "no such value exists"


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

wpshop said:
Is there any easier solution? I have 8 append queries that I am working
on
creating and I would have to apply this logic to all 8. Every post I have
searched says Nz works and the user gets back the right results but mine
will
not work and I'm not sure why. I am on 2003.



Chris2 said:
wpshop said:
Hi,

I am trying to get my Count field to show 0 even if there is no data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]
Click to expand...

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
Click to expand...
Click to expand...
 
C

Chris2

wpshop said:
Chris2 said:
wpshop said:
Hi,

I am trying to get my Count field to show 0 even if there is no data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]
Click to expand...

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
Click to expand...

Is there any easier solution? I have 8 append queries that I am working on
creating and I would have to apply this logic to all 8. Every post I have
searched says Nz works and the user gets back the right results but mine will
not work and I'm not sure why. I am on 2003.
Click to expand...

wpshop,

I was not suggesting a way to do something.

I was pointing out, by example, that there was nothing to do.

Your COUNT() expression will return 0 in both cases, when there is no
data, and when all data is null.

If your COUNT() expression is returning something other than 0, please
let me know what it is returning. Also, please provide your table
structure and sample data.


Sincerely,

Chris O.
 
G

Guest

I tried the Count(*) and it didn't work. Below is the full query I am
building. Perhaps I am trying to do too much with one query.

INSERT INTO tblMARAccuracyExternal120Day529 ( [Date], [Count] )
SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam ON
tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM) Between [Enter MAR Month Begin
Date] And [Enter MAR Month End Date]) AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date]) AND
((tblExternalErrorReport.DEPT_001)="iq") AND
((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR") AND
((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];


John Spencer said:
It would help if you posted the entire query you are trying to run instead
of a snippet.

If NO record is returned then NZ will have no effect on the outcome. For
instance this sample will return no record

SELECT FieldA, Nz(Count(*),0)
FROM SomeDatabase
WHERE FieldA= "no such value exists"
Group By FieldA

That will return no records and therefore NZ has no effect since it has no
field to act on.

On the other hand, this will return one record and it will have a value in
the one field
SELECT Count(*)
FROM SomeDatabase
WHERE FieldA= "no such value exists"


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

wpshop said:
Is there any easier solution? I have 8 append queries that I am working
on
creating and I would have to apply this logic to all 8. Every post I have
searched says Nz works and the user gets back the right results but mine
will
not work and I'm not sure why. I am on 2003.



Chris2 said:
Hi,

I am trying to get my Count field to show 0 even if there is no
data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Let's trouble shoot.

If you drop the Insert statement, does your query return a row of data?

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam
ON tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM)
Between [Enter MAR Month Begin Date] And [Enter MAR Month End Date])
AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date])
AND ((tblExternalErrorReport.DEPT_001)="iq")
AND ((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR")
AND ((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];

If not, then we need to work on the Select until it does return a row.

That might be accomplished by using something like the following. It looks
strange, but it seems to work in my simplified test setup

SELECT Nz([Enter 120 Day Month Begin Date], [Enter 120 Day Month Begin
Date]),
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam
ON tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM)
Between [Enter MAR Month Begin Date] And [Enter MAR Month End Date])
AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date])
AND ((tblExternalErrorReport.DEPT_001)="iq")
AND ((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR")
AND ((tblExternalErrorReport.FUND_000) Between 1400 And 1899))

I am assuming that all the [Enter ... Date] "fields" are parameter prompts.
If they are actual fields in the tables please tell us.

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

wpshop said:
I tried the Count(*) and it didn't work. Below is the full query I am
building. Perhaps I am trying to do too much with one query.

INSERT INTO tblMARAccuracyExternal120Day529 ( [Date], [Count] )
SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam ON
tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM) Between [Enter MAR Month
Begin
Date] And [Enter MAR Month End Date]) AND
((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date]) AND
((tblExternalErrorReport.DEPT_001)="iq") AND
((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR") AND
((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];


John Spencer said:
It would help if you posted the entire query you are trying to run
instead
of a snippet.

If NO record is returned then NZ will have no effect on the outcome. For
instance this sample will return no record

SELECT FieldA, Nz(Count(*),0)
FROM SomeDatabase
WHERE FieldA= "no such value exists"
Group By FieldA

That will return no records and therefore NZ has no effect since it has
no
field to act on.

On the other hand, this will return one record and it will have a value
in
the one field
SELECT Count(*)
FROM SomeDatabase
WHERE FieldA= "no such value exists"


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

wpshop said:
Is there any easier solution? I have 8 append queries that I am
working
on
creating and I would have to apply this logic to all 8. Every post I
have
searched says Nz works and the user gets back the right results but
mine
will
not work and I'm not sure why. I am on 2003.



:


Hi,

I am trying to get my Count field to show 0 even if there is no
data.

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]

I tried the following and it did not work

SELECT [Enter 120 Day Month Begin Date] AS [Date],
Nz(Count(tblExternalErrorReport.ACCT_000),0) AS [Count]

wpshop,

Using a new blank MS Access database.

Four queries:

Create_ShowZero

CREATE TABLE ShowZero
(NullColumn INTEGER
)


INSERT_NULL_ShowZero

INSERT INTO ShowZero ( NullColumn )
VALUES (NULL);

Run that query 3 times.


Example_Count_ShowZero

SELECT COUNT(S1.NullColumn) AS NullColumn
FROM ShowZero AS S1;

Run this query. The result is a 0.


Delete_ShowZero

DELETE *
FROM ShowZero

Run this query, and the table is emptied.

Now run Example_Count_ShowZero again.

The results are still 0.

So, whether empty or all Null, the COUNT() in the SELECT query
produces a 0.


Sincerely,

Chris O.
Click to expand...
Click to expand...
Click to expand...
 
C

Chris2

wpshop said:
I tried the Count(*) and it didn't work. Below is the full query I am
building. Perhaps I am trying to do too much with one query.

INSERT INTO tblMARAccuracyExternal120Day529 ( [Date], [Count] )
SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam ON
tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM) Between [Enter MAR Month Begin
Date] And [Enter MAR Month End Date]) AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date]) AND
((tblExternalErrorReport.DEPT_001)="iq") AND
((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR") AND
((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];
Click to expand...

wpshop,

Here is your query, straightened out.

INSERT INTO tblMARAccuracyExternal120Day529
([Date]
,[Count]
)
SELECT [Enter 120 Day Month Begin Date] AS [Date]
,Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport
INNER JOIN
tblCorrectionResearchTeam
ON tblExternalErrorReport.REP_001 =
tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM)
BETWEEN [Enter MAR Month Begin Date]
AND [Enter MAR Month End Date])
AND ((tblExternalErrorReport.PCDT_001)
BETWEEN [Enter 120 Day Month Begin Date]
AND [Enter 120 Day End Date])
AND ((tblExternalErrorReport.DEPT_001) = "iq")
AND ((tblCorrectionResearchTeam.[CURRENT TEAM]) = "CORR")
AND ((tblExternalErrorReport.FUND_000)
BETWEEN 1400
AND 1899))
GROUP BY [Enter 120 Day Month Begin Date];


Well, there are no table structures or sample data, so I'll invent
some to use as an example.


Tables:

Create a blank MS Access database. You can copy and paste these DDL
SQL queries each into an MS Access Query, executing each one in order
to create the tables and the relationships.


CREATE TABLE Items
(ItemID INTEGER
,ItemName TEXT(255)
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)


CREATE TABLE Prices
(ItemID INTEGER NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,Price CURRENCY
,CONSTRAINT pk_Prices
PRIMARY KEY (ItemID
,StartDate
,EndDate)
,CONSTRAINT fk_Prices
FOREIGN KEY (ItemID)
REFERENCES Items (ItemID)
)


Items

ItemID ItemName
1, Nails
2, Rivets
3, Washers
4, Lock Washers
5, Bolts
6, Long Nails
7, Short Nails
8, Tiny Nails
9, Ten Penny Nail
10, Fasteners

Prices

ItemID StartDate EndDate, Price
1, 01/01/2002, 12/31/2002, $5.00
2, 01/01/2002, 12/31/2002, $6.00
3, 01/01/2002, 12/31/2002, $1.00
4, 01/01/2002, 12/31/2002, $1.50
6, 01/01/2005, 12/31/2005, $0.50
7, 01/01/2005, 06/30/2005, $0.30
7, 02/02/2005, 03/04/2005, $0.45
8, 03/01/2005, 04/01/2005, $0.10
9, 01/01/2005, 11/30/2005, $0.20


Query:

You want COUNT() to report 0 when there is no data.


SELECT I1.ItemID
,COUNT(P1.ItemID) AS ItemCount
FROM Items AS I1
LEFT JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
GROUP BY I1.ItemID

Results:

ItemID ItemCount
1, 1
2, 1
3, 1
4, 1
5, 0
6, 1
7, 2
8, 1
9, 1
10, 0

As can be seen, zeroes are showing up even though the COUNT() was
operating on no rows in Prices for ItemID values of 5 and 10 (the LEFT
JOIN kept rows with NULLs around for non-matches).


What I think you need to do is switch INNER JOIN to RIGHT JOIN (if
not, try LEFT JOIN) in order to preserve rows in
tblExternalErrorReport where no matches were found so that COUNT() has
something to report as 0.


Sincerely,

Chris O.
 
G

Guest

I appreciate you both taking the time to answer my question. I ended up
making a table that would hold the dates I would always need to see. I cross
referenced the tables I am appending the data to and indicated I always
wanted to see my main dates even if there was no data in the appended table.
I then used the Nz function so that my query would produce a "0" instead of
being blank. I don't know if this makes sense to you because it's not in
code but I am relieved to find a solution to my problem.

Chris2 said:
wpshop said:
I tried the Count(*) and it didn't work. Below is the full query I am
building. Perhaps I am trying to do too much with one query.

INSERT INTO tblMARAccuracyExternal120Day529 ( [Date], [Count] )
SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam ON
tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM) Between [Enter MAR Month Begin
Date] And [Enter MAR Month End Date]) AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date]) AND
((tblExternalErrorReport.DEPT_001)="iq") AND
((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR") AND
((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];
Click to expand...

wpshop,

Here is your query, straightened out.

INSERT INTO tblMARAccuracyExternal120Day529
([Date]
,[Count]
)
SELECT [Enter 120 Day Month Begin Date] AS [Date]
,Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport
INNER JOIN
tblCorrectionResearchTeam
ON tblExternalErrorReport.REP_001 =
tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM)
BETWEEN [Enter MAR Month Begin Date]
AND [Enter MAR Month End Date])
AND ((tblExternalErrorReport.PCDT_001)
BETWEEN [Enter 120 Day Month Begin Date]
AND [Enter 120 Day End Date])
AND ((tblExternalErrorReport.DEPT_001) = "iq")
AND ((tblCorrectionResearchTeam.[CURRENT TEAM]) = "CORR")
AND ((tblExternalErrorReport.FUND_000)
BETWEEN 1400
AND 1899))
GROUP BY [Enter 120 Day Month Begin Date];


Well, there are no table structures or sample data, so I'll invent
some to use as an example.


Tables:

Create a blank MS Access database. You can copy and paste these DDL
SQL queries each into an MS Access Query, executing each one in order
to create the tables and the relationships.


CREATE TABLE Items
(ItemID INTEGER
,ItemName TEXT(255)
,CONSTRAINT pk_Items
PRIMARY KEY (ItemID)
)


CREATE TABLE Prices
(ItemID INTEGER NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME
,Price CURRENCY
,CONSTRAINT pk_Prices
PRIMARY KEY (ItemID
,StartDate
,EndDate)
,CONSTRAINT fk_Prices
FOREIGN KEY (ItemID)
REFERENCES Items (ItemID)
)


Items

ItemID ItemName
1, Nails
2, Rivets
3, Washers
4, Lock Washers
5, Bolts
6, Long Nails
7, Short Nails
8, Tiny Nails
9, Ten Penny Nail
10, Fasteners

Prices

ItemID StartDate EndDate, Price
1, 01/01/2002, 12/31/2002, $5.00
2, 01/01/2002, 12/31/2002, $6.00
3, 01/01/2002, 12/31/2002, $1.00
4, 01/01/2002, 12/31/2002, $1.50
6, 01/01/2005, 12/31/2005, $0.50
7, 01/01/2005, 06/30/2005, $0.30
7, 02/02/2005, 03/04/2005, $0.45
8, 03/01/2005, 04/01/2005, $0.10
9, 01/01/2005, 11/30/2005, $0.20


Query:

You want COUNT() to report 0 when there is no data.


SELECT I1.ItemID
,COUNT(P1.ItemID) AS ItemCount
FROM Items AS I1
LEFT JOIN
Prices AS P1
ON I1.ItemID = P1.ItemID
GROUP BY I1.ItemID

Results:

ItemID ItemCount
1, 1
2, 1
3, 1
4, 1
5, 0
6, 1
7, 2
8, 1
9, 1
10, 0

As can be seen, zeroes are showing up even though the COUNT() was
operating on no rows in Prices for ItemID values of 5 and 10 (the LEFT
JOIN kept rows with NULLs around for non-matches).


What I think you need to do is switch INNER JOIN to RIGHT JOIN (if
not, try LEFT JOIN) in order to preserve rows in
tblExternalErrorReport where no matches were found so that COUNT() has
something to report as 0.


Sincerely,

Chris O.
Click to expand...
 
C

Chris2

wpshop said:
Chris2 said:
wpshop said:
I tried the Count(*) and it didn't work. Below is the full
Click to expand...
query I
am
building. Perhaps I am trying to do too much with one query.

INSERT INTO tblMARAccuracyExternal120Day529 ( [Date], [Count] )
SELECT [Enter 120 Day Month Begin Date] AS [Date],
Count(tblExternalErrorReport.ACCT_000) AS [Count]
FROM tblExternalErrorReport INNER JOIN tblCorrectionResearchTeam ON
tblExternalErrorReport.REP_001 = tblCorrectionResearchTeam.[USER ID]
WHERE (((tblExternalErrorReport.EVENT_DATTIM) Between [Enter MAR Month Begin
Date] And [Enter MAR Month End Date]) AND ((tblExternalErrorReport.PCDT_001)
Between [Enter 120 Day Month Begin Date] And [Enter 120 Day End Date]) AND
((tblExternalErrorReport.DEPT_001)="iq") AND
((tblCorrectionResearchTeam.[CURRENT TEAM])="CORR") AND
((tblExternalErrorReport.FUND_000) Between 1400 And 1899))
GROUP BY [Enter 120 Day Month Begin Date];


wpshop,
Click to expand...
Click to expand...

What I think you need to do is switch INNER JOIN to RIGHT JOIN (if
not, try LEFT JOIN) in order to preserve rows in
tblExternalErrorReport where no matches were found so that COUNT() has
something to report as 0.


Sincerely,

Chris O.
Click to expand...

wpshop,

I appreciate you both taking the time to answer my question. I ended up
making a table that would hold the dates I would always need to see. I cross
referenced the tables I am appending the data to and indicated I always
wanted to see my main dates even if there was no data in the appended table.
I then used the Nz function so that my query would produce a "0" instead of
being blank. I don't know if this makes sense to you because it's not in
code but I am relieved to find a solution to my problem.
Click to expand...

wpshop,

Sounds a lot more complicated than switching INNER JOIN to RIGHT JOIN
and avoiding Nz() .


Sincerely,

Chris O.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query with multiple fields 2
count qry 2
Unable To Count Null Date Field 2
crosstab with conditional SUM() 1
Count of in Range of Dates 17
Null Return on Count Function 4
counts NULL values in query 4
Invalid use of null in query expression 3

Top