Help me with “Countâ€, please

[Peres from Brazil]

I have read all answer that contain the word “Count†and did not see a
solution for my problem:

I have a database with a field “Name†and other field “Date†.
Every day same one calls me, I input the Name of the caller and the Date in
the respective field.
I analyze this database using an Excel pivot table.
Now I want to know how many Names I have, but because of the repetition I
ways get the total number of records.
What I want is number of persons and not the number of names: If “Josephâ€
called me 30 times I want it count just as 1 and not 30
Thanks in advance for any help
Luiz

 

[Wayne-I-M]

Hi

Create a totals query and total on the names (not calls). If you also have
a column showing calls this will have the benifit of showing you not only how
many different people called but also how many times each person called.

 

[Ken Sheridan]

Luiz:

Your problem arises from the fact that your table is not fully normalized.
The table should be decomposed into two tables one containing each person
once only (their name address etc), the other containing the calls (the date,
subject etc). The Calls table should contain a foreign key column which
references the primary key of the People table. These columns should be
unique numeric PersonID values rather than the names as two people might have
the same name. The primary key of the People table can be an autonumber, but
the corresponding foreign key in the Calls table should be a straightforward
long integer number data type.

The two separate tables can be populated from the original table by means of
'append' queries. First create the two new empty tables. Then append the
distinct people from the original table to the new People table first. Then
join the original table to the new People table on the Name columns and
append the PersonID, CallDate etc columns to the new Calls table

For data input of calls use a form in which a combo box is bound to the
foreign key PersonID column, but shows the names from the People table in its
list (the combo box wizard can set this up for you).

To count the number pf people is then simply a matter of counting the rows
in the people table. Join the two tables in a query for the purpose of your
analysis in Excel.

It is possible to count distinct values in a table, however, by counting the
rows returned by a subquery, e.g.

SELECT COUNT(*) AS NameCount
FROM (SELECT DISTINCT [Name]
FROM [YourTable]);

I'd recommend that Name not be used as a column name, however; it could
easily be confused with the built in Name property. Similarly don't use Date
as a column name.

Ken Sheridan
Stafford, England

 

[John W. Vinson]

On Tue, 8 May 2007 08:33:00 -0700, Peres from Brazil <Peres from

I have read all answer that contain the word “Count” and did not see a
solution for my problem:

I have a database with a field “Name” and other field “Date” .
Every day same one calls me, I input the Name of the caller and the Date in
the respective field.
I analyze this database using an Excel pivot table.
Now I want to know how many Names I have, but because of the repetition I
ways get the total number of records.
What I want is number of persons and not the number of names: If “Joseph”
called me 30 times I want it count just as 1 and not 30
Thanks in advance for any help
Luiz

Create a Query based on your table. Don't include the Date field, just the
Name field - but include the Name field *twice*.

Change the query to a Totals query using the Greek Sigma icon (like a sideways
M).

Leave the default Group By on one instance of Name, and change it to Count on
the other.

John W. Vinson [MVP]

 

[Peres from Brazil]

Thanks a lot

"Wayne-I-M" escreveu:

Hi

Create a totals query and total on the names (not calls). If you also have
a column showing calls this will have the benifit of showing you not only how
many different people called but also how many times each person called.

 

[Peres from Brasil]

Ken

To tell you the truth, I have it normalized, and now, with your example I
could fully understand how it works… thanks very, very much , Luiz

Luiz:

Your problem arises from the fact that your table is not fully normalized.
The table should be decomposed into two tables one containing each person
once only (their name address etc), the other containing the calls (the date,
subject etc). The Calls table should contain a foreign key column which
references the primary key of the People table. These columns should be
unique numeric PersonID values rather than the names as two people might have
the same name. The primary key of the People table can be an autonumber, but
the corresponding foreign key in the Calls table should be a straightforward
long integer number data type.

The two separate tables can be populated from the original table by means of
'append' queries. First create the two new empty tables. Then append the
distinct people from the original table to the new People table first. Then
join the original table to the new People table on the Name columns and
append the PersonID, CallDate etc columns to the new Calls table

For data input of calls use a form in which a combo box is bound to the
foreign key PersonID column, but shows the names from the People table in its
list (the combo box wizard can set this up for you).

To count the number pf people is then simply a matter of counting the rows
in the people table. Join the two tables in a query for the purpose of your
analysis in Excel.

It is possible to count distinct values in a table, however, by counting the
rows returned by a subquery, e.g.

SELECT COUNT(*) AS NameCount
FROM (SELECT DISTINCT [Name]
FROM [YourTable]);

I'd recommend that Name not be used as a column name, however; it could
easily be confused with the built in Name property. Similarly don't use Date
as a column name.

Ken Sheridan
Stafford, England

I have read all answer that contain the word “Count†and did not see a
solution for my problem:

I have a database with a field “Name†and other field “Date†.
Every day same one calls me, I input the Name of the caller and the Date in
the respective field.
I analyze this database using an Excel pivot table.
Now I want to know how many Names I have, but because of the repetition I
ways get the total number of records.
What I want is number of persons and not the number of names: If “Josephâ€
called me 30 times I want it count just as 1 and not 30
Thanks in advance for any help
Luiz

 

[Peres from Brasil]

John,

Thanks for you replay, it's a very elegent way and hekp me to understand
this "Count" thing... see you...