Results 1 to 5 of 5

Thread: Counting Zeros

  1. #1
    Join Date
    Jul 2006
    Posts
    3

    Question Unanswered: Counting Zeros

    Hello,

    I'm having a problem that I can't seem to solve. I am using A Master Client Table, which holds all the information of all the clients. I also have many different tables joined to this that have information such as gender,insurance, how they were referred,ect., so that the data can be entered easier.

    Right now I have queries set up to count how many clients have each of these things, and if there is no client with one of the insurances or ways of referral it will count a zero.

    For example -Insurance Types :3- Independant Health, 3 Blue Cross, 0-Community Blue and 5-Medicare. It counts the zeros fine right now, but if I enter some criteria such as a Date Range or if the person is an active client, it stops reading the zeros and I don't understand why this happens.

    Here is my SQL of what I have so far:

    SELECT [tblCodes-Insurance].InsuranceCode AS Insurance, Count([tblMaster-Client].ClientID) AS [Count]
    FROM [tblCodes-Insurance] LEFT JOIN [tblMaster-Client] ON [tblCodes-Insurance].InsuranceCode = [tblMaster-Client].InsuranceCode
    GROUP BY [tblCodes-Insurance].InsuranceCode
    ORDER BY [tblCodes-Insurance].InsuranceCode;

    I Really Appreciate any help that can be given

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi jrock76

    Welcome to the forum

    These fields you are filtering on wouldn't be in [tblMaster-Client] by any chance?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2006
    Posts
    3
    Yes. Both the tblMaster-Client and the individual tbls. (InsuranceCodes,ReferralCodes ect.)

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - a left join returns all rows from the left table and any matching rows from the right table if they match. However, filtering on the right table means only rows in the left table that match the returned rows in the right table will be returned. There are no matching records in the right table so... you end up with nothing. Follow that?

    There aren't really any satisfying ways to deal with this in Access. You need to filter the right table BEFORE joining it to the left table either as a derived table or as a seperate query that you apply the where clause to and join that. SQL Server (if you have access to that) is much better for this.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2006
    Posts
    3
    Pootle Flump,

    Thanks for your help. I'll see what I can do with that information.

Posting Permissions

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