Unable to get a query to count as zero if record is blank

[Menhir]

Hello,

I am pretty new to Access and mostly self-taught by trial and error in the
last couple of weeks. You can imagine the number of faux pas I am probably
committing in terms of Access best user practices. Anyway, to set things up...

Database Details:

1. I have one big table called 'CIM Customer Table' that has a lot of fields
and records including fields named 'Group', 'Region', and 'Status' among
other fields.
2. I have 3 Groups with 4 Regions under each
3. I am looking to get a count of all the records with a Status of
'Scheduled' for each of these 12 regions.
4. My problem is that there are some regions that do not have this status at
all
5. Access of course does not show a zero count if there is no record with
'Scheduled'.

Attempted Resolutions:

1. I have looked through a lot of posts and tried the Nz function, IsNull
function, and the outer join function (which I realize requires two tables)
but to no avail.
2. Here is the SQL code that I started with (which is clearly wrong)

SELECT [cim customer table].[Group], [cim customer table].[Region],
Count([cim customer table].Status) AS ['Pharmacies Scheduled']
FROM [cim customer table]
WHERE [cim customer table].Status='Scheduled'
GROUP BY [cim customer table].Group, [cim customer table].Region;

3. The results comes out to be like this:

Group Region Pharmacies Scheduled
Central Great Lakes 3
Central Pacific North 4
East New England 2
East Northeast 1
East Ohio Valley 3

4. I need it to show:

Central Great Lakes 3
Central Mid-America 0
Central Pacific North 4
Central Pacific Southern 0
East New England 2
East Northeast 1
East Ohio Valley 3
East Atlantic 0
Southeast Gulf 0
Southeast Southern 0
Southeast Southwest 0

Please Help! I am sure this is pretty easy, but it's tough to figure it out
if you have absolutely no clue where to go once you're at a deadend.

Thanks so much in advance!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not tested:

SELECT [Group], Region,
SUM(IIf([Status]='Scheduled',1,0)) AS [Pharmacies Scheduled]
FROM [cim customer table]
GROUP BY [Group], Region;

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDWZzIechKqOuFEgEQJKxwCghQiQkd5aoP5ufoDHbQ0iBPTmm6QAnils
v+ABlOMbm4OFVLYf+6arFaKL
=/q4h
-----END PGP SIGNATURE-----

Hello,

I am pretty new to Access and mostly self-taught by trial and error in the
last couple of weeks. You can imagine the number of faux pas I am probably
committing in terms of Access best user practices. Anyway, to set things up...

Database Details:

1. I have one big table called 'CIM Customer Table' that has a lot of fields
and records including fields named 'Group', 'Region', and 'Status' among
other fields.
2. I have 3 Groups with 4 Regions under each
3. I am looking to get a count of all the records with a Status of
'Scheduled' for each of these 12 regions.
4. My problem is that there are some regions that do not have this status at
all
5. Access of course does not show a zero count if there is no record with
'Scheduled'.

Attempted Resolutions:

1. I have looked through a lot of posts and tried the Nz function, IsNull
function, and the outer join function (which I realize requires two tables)
but to no avail.
2. Here is the SQL code that I started with (which is clearly wrong)

SELECT [cim customer table].[Group], [cim customer table].[Region],
Count([cim customer table].Status) AS ['Pharmacies Scheduled']
FROM [cim customer table]
WHERE [cim customer table].Status='Scheduled'
GROUP BY [cim customer table].Group, [cim customer table].Region;

3. The results comes out to be like this:

Group Region Pharmacies Scheduled
Central Great Lakes 3
Central Pacific North 4
East New England 2
East Northeast 1
East Ohio Valley 3

4. I need it to show:

Central Great Lakes 3
Central Mid-America 0
Central Pacific North 4
Central Pacific Southern 0
East New England 2
East Northeast 1
East Ohio Valley 3
East Atlantic 0
Southeast Gulf 0
Southeast Southern 0
Southeast Southwest 0

Please Help! I am sure this is pretty easy, but it's tough to figure it out
if you have absolutely no clue where to go once you're at a deadend.

Thanks so much in advance!

 

[Menhir]

MGFoster,

Thanks SO much for your help! That worked like a charm! I really appreciate
it coz I've struggled over several days to figure this out! Such an elegant
and simple solution!

Thanks again!




:

-----BEGIN PGP SIGNED MESSAGE-----

Hash: SHA1

Not tested:

SELECT [Group], Region,
SUM(IIf([Status]='Scheduled',1,0)) AS [Pharmacies Scheduled]
FROM [cim customer table]
GROUP BY [Group], Region;

 

[Menhir]

A stupid follow-up question...

In the same context, how would I use two conditions? As in, If Condition 1
AND Condition 2, then 1, else 0.

A quick note on what I want to accomplish with this statement - Basically, I
want to check if an agreement has been mailed, but has not been received yet,
count that for each region, and include zeros if this criteria is not met for
a region.

Currently, I have this SQL statement and running into the same zero count
issues as before, and I would like to use the simple IIF statement with the
necessary changes..

SELECT [cim customer table].[Group], [cim customer table].Region, Count([cim
customer table].[Signed Agreement Sent]) AS ['Outstanding Agreements']
FROM [cim customer table]
WHERE ((([cim customer table].[Signed Agreement Received]) Is Null) AND
(([cim customer table].[Signed Agreement Sent]) Is Not Null))
GROUP BY [cim customer table].[Group], [cim customer table].Region;

Thanks in advance!





:

-----BEGIN PGP SIGNED MESSAGE-----

Not tested:

SELECT [Group], Region,
SUM(IIf([Status]='Scheduled',1,0)) AS [Pharmacies Scheduled]
FROM [cim customer table]
GROUP BY [Group], Region;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using my previous example:

SELECT [Group], Region,
SUM(IIf(Not IsNull([Signed Agreement Sent]) AND
IsNull([Signed Agreement Received]),1,0))
As [Outstanding Agreements]
FROM [cim customer table]
GROUP BY [Group], Region;

The following one finds only those that sent an agreement and have not
yet received the signed agreement.

SELECT [Group], Region, Count(*) As [Outstanding Agreements]
FROM [cim customer table]
WHERE [Signed Agreement Sent] IS NOT NULL
AND [Signed Agreement Received] IS NULL
GROUP BY [Group], Region;

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDcCXoechKqOuFEgEQJwRwCfUcJhSoSatirMHZrxiec7cR9cr4oAn0H6
6+LgO3vydUWDQR2UbnhHSHXG
=g1S6
-----END PGP SIGNATURE-----

 

[Menhir]

MGFoster,

Thanks so much for your help! The first method works perfectly! The second
brings back the numbers with a populated field but running into the same
issue of the inability to count and display the zeros if field is blank. I'll
use the first one as that seems to solve my issue!

Thanks again!


:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Using my previous example:

SELECT [Group], Region,
SUM(IIf(Not IsNull([Signed Agreement Sent]) AND IsNull([Signed Agreement
Received]),1,0)) As [Outstanding Agreements]
FROM [cim customer table]
GROUP BY [Group], Region;

The following one finds only those that sent an agreement and have not yet
received the signed agreement.

SELECT [Group], Region, Count(*) As [Outstanding Agreements]
FROM [cim customer table]
WHERE [Signed Agreement Sent] IS NOT NULL
AND [Signed Agreement Received] IS NULL
GROUP BY [Group], Region;