Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query problem (2000)

    I hope I can explain this. I have fields called - Type, Client Number ,Group Number and a few other fields but these are the only fields I need to explain the problem. I want to run a totals query where I group on type and then I want a count of Client numbers for each type. My problem is that each client has a certain amount of groups. When I do a totals query Grouping on Type and counting on Client Number I am getting a count of the groups that are associated with the Client. How can I get around this. Maybe there isn't a way to get a unique count of Clients.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query problem (2000)

    If you create a totals query which includes only the Type and the Client Number (don't include Group Number), and you do GroupBy on the first and Count on the second, you should get the correct results. If that doesn't work, post the SQL for your totals query and let us look at it.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    SELECT BlueChoice.CLEnrollID, Count(BlueChoice.ClNum) AS CountOfClNum
    FROM BlueChoice
    GROUP BY BlueChoice.CLEnrollID;

    There are only 183 Clients and the breakdown adds up to 3790 which is the number of groups

    Thanks for your help.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query problem (2000)

    Well, if you 183 clients, then the total of the two records you should get would be 183. Is CLEnrollID the Type you mentioned previously, and is CINum the unique Client ID? If so, then I think you need to do a query that gives you only the UniqueValues of ClientID and Type as a select query, and then do the totals query from that query. I suspect you have many more than 183 records in your table, and when you total across all of them, you get the result you got.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (2000)

    That was so simple. I did everything and it wasn't working. The uniquevalues was what I missed. Thank you

Posting Permissions

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