Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Counting (Access 2000)

    Can anyone help me with this problem? Please see attached database.

    Each Client is designated as "Active" or "Inactive".
    Each Client is assigned an "Account Type".
    Each Clinic has an "Estimated Shot" field.
    Each Clinic has a check box that (if checked) cancels the clinic.

    I have a report that groups each Client and Clinic by Account Type.

    I want to have 3 text boxes on the report that:

    1. Adds the shots for all clinics that are not cancelled and its Client is active
    2. Counts all the Clients that are "Active" (with out counting duplicate Clients).
    3. Counts all the Clinics that are not cancelled (the "Cancel" check box has not been checked).

    My current query counts every name in the Client field (even though some are duplicates). How can I structure a query that will only count each name once?
    Attached Files Attached Files
    Easy Access

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting (Access 2000)

    You can't do it all in the same query, for if you include tblClinics, you are going to count clients twice (or more), since one client can occur in several clinics. The easiest way to count clients is probably using DCount:<pre>=DCount("*", "tblClient", "strClientStatus = 'Active'")</pre>

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Counting (Access 2000)

    Thanks you Hans for your quick and accurate response. I am in your debt. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Easy Access

Posting Permissions

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