Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Problems (2003)

    I am trying to build a query that will count the number of clients who came to our office for "advice/summary" service on a "family Matter" between a specific set of dates.

    I have a table that records the "date of session" their level of service (i.e. advice /summary), and their area of service (i.e. family). Each client has a unique client number. The level of service and area of service are yes/no checkboxes for each option.

    In Query design i have included Client Number, Family (area of law), Advice Summary (area of Service) and Date of Session. The Criteria for date of session is set to "Between [Start Date] and [End Date]. The criteria for family is set to yes and the criteria for advice summary is set to yes. I have created a final column in that is TOTAL:count(*).

    When I run the query I get a zero when i know there is one record that is a family matter for advice/summary between the dates i specify.
    I can't figure out what i have done wrong. Any advice would be appreciated.

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

    Re: Query Problems (2003)

    Try the following:
    - Remove the Client Number field from the query.
    - Select View Totals or click the Totals button on the toolbar.
    - Set the Total option for Family, Advice Summary and Date of Session to Where. This will automatically clear the Show check box.
    - Set the Total option for the Total: Count(*) column to Expression.
    If you still get a 0, we would need to see a stripped down version of the database. See <post#=401925>post 401925</post#> for instructions.

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

    Re: Query Problems (2003)

    Your query is based on the tables Law Activities and Client Information, with an inner join on Client Number.
    The Client Information table has no records, so your query, understandably, doesn't return anything. If you omit Client Information from the query (it doesn't play a role in the query anyway), it should do what you want.

  4. #4
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problems (2003)

    Im still getting a zero. I have attached the database.

    Thank you for your assistance.

  5. #5
    Lounger
    Join Date
    Nov 2005
    Location
    Br. Columbia, Canada
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Problems (2003)

    Thank you that fixed the problem. I saw that but it never even crossed my mind what that actually was. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I do have one last question relating to this. The query works to count all the clients that had appointments for the specific area and service in that month but if they came more than once it counts them for as many times as they came. Is it possible to have the query count each client only once for that specific area and service between the specified dates. Each client has a specific client number, but it can occur more than once in this activities table. I corrected the query in the database posted above.

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

    Re: Query Problems (2003)

    The easiest way to do this is through 2 queries:

    1) A Totals query based on the Law Activities table that returns all Client Numbers for which the criteria on Date of Session, Advice/Summary Service and on Family are met. Save this as qryUniqueClients.

    2) A query based on qryUniqueClients that returns the count of records.

    See attached version (warning: I removed everything not relevant to the problem!)

Posting Permissions

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