Don't count duplicate fields

[Guest]

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?

 

[Joseph Meehan]

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?

Don't forget that you are likely to have more than one family with the
same last name.

One solution to this is to create two tables.

Table one will have each family listed along with things like address
phone number etc. Table two will list each member of the family of
interest. Parents, children etc. I would add a field to ID their type.

Set up a relation between the two tables One - Many and away you go.

 

[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?

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]