Don't count duplicate fields

D

dewiener

I am working on a database for a day camp- so some families have more than
one child. Each child is its own record including [last name] [first name]
[address] [dob], etc. I want to count how many families there are in other
words I want to count by [Last Name] but without counting duplicate names .
Does anyone have any suggestions?
 
J

John Vinson

I am working on a database for a day camp- so some families have more than
one child. Each child is its own record including [last name] [first name]
[address] [dob], etc. I want to count how many families there are in other
words I want to count by [Last Name] but without counting duplicate names .
Does anyone have any suggestions?
Click to expand...

You'll need a query based on another query:

SELECT Count(*) FROM
(SELECT DISTINCT LastName, Address FROM CampTable)
GROUP BY LastName, Address;

I'm assuming that you might have some duplicate last names which the
address will distinguish, and that you'll take the risk of "blended
families" in which kids use different last names.

John W. Vinson[MVP]
 
Top