Counting unduplicate data

  • Thread starter Thread starter catherine22
  • Start date Start date
C

catherine22

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!
 
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)
 
Back
Top