# Thread: Populate zero instead of blank rows

1. Registered User
Join Date
Jan 2006
Posts
3

## Unanswered: Populate zero instead of blank rows

I'm doing a sum of records to get a total, but if sum is zero instead of a blank row i would like to force access to populate a zero instead.

This is my query
SELECt B.region, Sum(IIf([Gender]="M",1,0)) AS [Male Count], Sum(IIf([Gender]="F",1,0)) AS [Female Count], Sum(IIf([Race]in (1,2),1,0)) AS [nonmin], Sum(IIf([Race]in (0,3,4,5,6,7,8,9),1,0)) AS [min], "under 28" AS Age, 1 AS Counter
FROM ee_lookup A, pu_region_info B
where A.P = B.P_code and (2006-Year([a.dob]))<28 and B.region = "FD"
GROUP BY B.region

Is this possible???

thanks,
vanissa

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
As this is really a Jet (the database engine that MS-Access uses by default) question rather than a generic SQL question, I've moved this thread to the MS-Access forum.

Your age computation scares me. While it gives a "good guess" at age, it isn't really accurate enough that I'm comfortable with it.

Using just SQL (even within Jet), this query shouldn't produce any result for regions that have no rows materialized in the JOIN operation. There are ways to "fake it out", but they play "outside the rules" so I try to avoid them.

If you truly want to generate zeros for region values that exist in pu_region_info but have no matching rows in ee_lookup, then I'd use a more "brute force" approach, something like:
Code:
```SELECT B.region
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Gender]="M") AS [Male Count]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Gender]="F") AS [Female Count]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Race] IN (1,2)) AS [nonmin]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Race] NOT IN (1,2)) AS [min]
,  "under 28" AS Age, 1 AS Counter
FROM pu_region_info B
WHERE  B.region = "FD"
GROUP BY B.region```
-PatP

3. Registered User
Join Date
Jan 2006
Posts
3
I've double check the age calculation manually with sorting several population groups out and it is summing as needed.

Your logic worked!!!! Thank you so much for all your help!

Originally Posted by Pat Phelan
As this is really a Jet (the database engine that MS-Access uses by default) question rather than a generic SQL question, I've moved this thread to the MS-Access forum.

Your age computation scares me. While it gives a "good guess" at age, it isn't really accurate enough that I'm comfortable with it.

Using just SQL (even within Jet), this query shouldn't produce any result for regions that have no rows materialized in the JOIN operation. There are ways to "fake it out", but they play "outside the rules" so I try to avoid them.

If you truly want to generate zeros for region values that exist in pu_region_info but have no matching rows in ee_lookup, then I'd use a more "brute force" approach, something like:
Code:
```SELECT B.region
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Gender]="M") AS [Male Count]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Gender]="F") AS [Female Count]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Race] IN (1,2)) AS [nonmin]
,  (SELECT Count(*) FROM ee_lookup AS a WHERE a.p = b.p_code AND (2006-Year([a.dob]))<28 AND [Race] NOT IN (1,2)) AS [min]
,  "under 28" AS Age, 1 AS Counter
FROM pu_region_info B
WHERE  B.region = "FD"
GROUP BY B.region```
-PatP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•