Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Including nulls in a query? (97 SR-2)

    I know this question gets asked alot but I can't seem to find the solution anywhere. I always have big problems with nulls. Here is what I want.
    I want to return a count of all male patients that each doctor has helped. But I want to display the doctors with no male patients too. Would like it to show zero when the query is run. Here is what my sql looks like.

    SELECT tblASMain.PhysicianID, Count(*) AS Gender
    FROM tblASMain
    GROUP BY tblASMain.PhysicianID, tblASMain.Gender
    HAVING (((tblASMain.Gender)="Male"));

    Help would be greatly appreciated.
    Thanks
    Jols

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Including nulls in a query? (97 SR-2)

    How about this. Assign a value of 1 to each record with a Male patient, otherwise a 0:

    SELECT tblASMain.PhysicianID, Count(IIf("Male",1,0)) as MalePatients
    FROM tblASMain
    GROUP BY tblASMain.PhysicianID
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Including nulls in a query? (97 SR-2)

    Or try this:

    SELECT tblASMain.PhysicianID, Sum(Abs(tblASMain.Gender="Male")) AS Gender
    FROM tblASMain
    GROUP BY tblASMain.PhysicianID, tblASMain.Gender;
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including nulls in a query? (97 SR-2)

    I haven't tested this, but another method might be ...
    Joining the physician table and tblASMain with an outer join, so that you retrieve all doctors, whether or not they have male patients? You could use the Nz function to display zero when the male patient count is null.
    There is a physician table somewhere, right?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including nulls in a query? (97 SR-2)

    Your suggestion worked very well and so did Charlotte's. Thank you both very much. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jols

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including nulls in a query? (97 SR-2)

    I have another query here but can't seem to get the docs that don't match the set criteria to show up in the results with a zero displaying. Here is my SQL.

    SELECT tblASMain.PhysicianID
    FROM tblASMain
    GROUP BY tblASMain.PhysicianID
    HAVING (((tblASMain.Super) Is Not Null)) OR (((tblASMain.Deep) Is Not Null)) OR (((tblASMain.OrganSpace) Is Not Null));

    Thanks for any help. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jols

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Including nulls in a query? (97 SR-2)

    I'm sorry, but a zero displaying where and for what? The only thing you're returning in this query is the PhysicianID. According to your criteria, you aren't even returning that if the Physician has null in all three of the possible values.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including nulls in a query? (97 SR-2)

    Sorry Charlotte, I left out the count PhysicianID field.

    SELECT tblASMain.PhysicianID, Count(tblASMain.PhysicianID) AS CountOfPhysicianID
    FROM tblASMain
    WHERE (((tblASMain.Super) Is Not Null)) OR (((tblASMain.Deep) Is Not Null)) OR (((tblASMain.OrganSpace) Is Not Null))
    GROUP BY tblASMain.PhysicianID;

    I want to display a count of all PhysicianID's where one of those fields in the where condition is not null. It works but once again it doesn't list those PhysicianID's that have null in the Super, Deep, or OrganSpace fields. I would like them to be listed in the query with a zero value. I'm not sure that I can use the Abs function in this situation or not?
    Thanks,
    Jols

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Including nulls in a query? (97 SR-2)

    If you want to list *all* the physicians, then why are you using criteria at all?
    <hr>It works but once again it doesn't list those PhysicianID's that have null in the Super, Deep, or OrganSpace fields<hr>
    What do you mean, it works? If it works, then you're getting all the physicians who have at least one non-null value in one of those three criteria fields. You will *not* get any physicians who have null values in all three fields because your criteria filters for Not Null. However, you won't get a count of zero for those physicians in any case, because you're counting physicianID, and it can't be zero or you wouldn't see the physician at all.

    You would be better off returning a calculated expression that evaluates the where condition like this:

    SELECT tblASMain.PhysicianID,
    Sum(Abs(tblASMain.Super Is Not Null OR tblASMain.Deep Is Not Null OR tblASMain.OrganSpace Is Not Null)) AS CountOfPhysicianID
    FROM tblASMain
    GROUP BY tblASMain.PhysicianID;
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Including nulls in a query? (97 SR-2)

    Sorry for being half brain dead here! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    That was what I needed Charlotte, thanks!!
    I really appreciate all your help!!

    Jols

Posting Permissions

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