Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Counting unduplicate data

Discussion in 'Information Technology' started by catherine22, Jul 28, 2006.

  1. catherine22

    catherine22
    Expand Collapse
    Guest

    I have a form containing general client information (ex: name, address,
    etc) and a subform within that form which is filled out each time the
    client returns for a service. I am trying to create a query/report
    which gives the total number of clients that have come from each city
    in out county. However, if a client has come more than once (i.e. more
    than one subform is filled out for them) the information on the parent
    form (ex: city) is counted more than once. How sum the total number of
    people from a city w/o duplicates? Can I perhaps base it on a the
    unique client ID? Any help would be greatly appreciated. Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016
    Malaysia Malaysian 2013 Election - Vote Counting Underway Breaking News May 5, 2013
    No Counting Allowed in Gurmatt Jap Ji Sahib Dec 15, 2012
    Christianity Ye Olde King James Version, 400 Years and Counting Interfaith Dialogues Mar 27, 2011
    Will counting caste help to reduce inequality? Hard Talk May 11, 2010

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Catherine,

    I assume you have a Clients table which the main form is based on, and a
    Services (or some such) table that the subform is based on. I assume
    you don't want to just use the records directly from the Clients table,
    because some of them have never had a Service, is that correct? And I
    assume that the Client ID is the basis of the relationship between the
    Clients table and the Services table. So yes, you can use the Client ID
    from the Services table, in a query, the SQL view of which will look
    something like this...

    SELECT Clients.City, Count([Client ID])
    FROM Clients
    GROUP BY Clients.City
    HAVING [Client ID] In (SELECT DISTINCT [Client ID] FROM Services)

    --
    Steve Schapel, Microsoft Access MVP

    catherine22 wrote:
    > I have a form containing general client information (ex: name, address,
    > etc) and a subform within that form which is filled out each time the
    > client returns for a service. I am trying to create a query/report
    > which gives the total number of clients that have come from each city
    > in out county. However, if a client has come more than once (i.e. more
    > than one subform is filled out for them) the information on the parent
    > form (ex: city) is counted more than once. How sum the total number of
    > people from a city w/o duplicates? Can I perhaps base it on a the
    > unique client ID? Any help would be greatly appreciated. Thanks!
    >
     

Share This Page