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

    Uniqe total in report (2003)

    I have a report where I would like to sum contracts. The trouble is, a client can have more than one record and the contract count duplicates. I used the do not show duplicates but when I sum, the duplicate contract count gets summed in the total. Is there a way to sum just the one contract count per client? I hope this makes sense.

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

    Re: Uniqe total in report (2003)

    We'll have to know what the data look like. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: Uniqe total in report (2003)

    I can't really do that. The tables are linked. I just need to sum contracts for a client manager who has say 10 clients. Each client has a contract count. In the case where a client has more than one row of data, the contract count shows for each row. I want to total the contract count per client manager but I am getting an inflated total due to the client having more than one row. As in the example below. The Client Mgr Schwent has a Client Num 200 that appears twice because it has two agency types. The Contract count of 20 appears twice so that when I total this for Client Mgr Schwent, he has 40 contracts when he should only have 20 for that client 200. I am not sure how to total the number of contracts on a report.
    CM Client NumClient Name Contracts Broker Agency_Type
    Schwent 200 Bethlehem 20 Caruso Preferred
    Schwent 200 Bethlehem 20 Stewart Standard

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

    Re: Uniqe total in report (2003)

    The data design is not correct - the contract count shouldn't be there since it is not a property that is related to the broker.

    But anyhow, try this:

    - Create a query in design view based on the table.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Set the Unique Values property to Yes.
    - Add the CM, Client Num, Client Name and Contracts fields to the query grid.
    - Save this query as (for example) qryClients.

Posting Permissions

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