Results 1 to 3 of 3
  1. #1
    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. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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. #3
    Join Date
    Jan 2006
    Posts
    3

    Wink

    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!




    Quote 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
  •