I have a file with 1000 names. About 1/2 of them are duplicates, some
entered two, three or more times. I would like to generate a report that
gives me a total count of the individuals. I have created a report with a
sub summary section and entered the name. I found all records and sorted by
name. Now the report is listing each person once (that is good). I entered
a field for the Record Number but it is giving me a total of all the people
entered.

I am looking for a number like 450, meaning there are 450 individual names
and omitting all the duplicate records.

I don't know how clear I am <sigh!>, but can this be done? Where am I going
wrong???

Thanks

In article <0OmdnZ2dnZ2e-l2vnZ2dnWoXkt6dnZ2dRVn-zJ2dnZ0 (AT) comcast (DOT) com>,
"Stephen Larivee" <NOlariveeslSPAM (AT) yahoo (DOT) PLEASEcom> wrote:

Names by themselves aren't a good indicator of being the same person.
Many people can have the same name (some even the same birth date as
well), but are actually different people. In fact, from simple data in
a database there's NO method of working our who are individual people
(eg. even using the address method, the same person can easily be in
the database under two different addresses).

But anyway ...

There's two ways to find the number of unique names:

A. Relationship
You can define a relationship linking Name to Name. Then
you can define a Calculation field that calculates as 1/Xth
where X is the number of records with that name:
ie.
ThisNameFraction {Calculation, Number result}
= 1 / Count(Relationship::Name)

Obviously adding these together will total to 1 for each
name, and therefore a simple Summary field can be used to
count the unique names by totalling this field:
ie.
UniqueNameCount {Summary, NOT Running Total}
= Total of ThisNameFraction


B. Summary Fields Only
You can also achieve the same effect by using only Summary
fields. First define a Summary field to count the number
of records for each name:
ie.
ThisNameCount {Summary, NOT Running Total}
= Count of Name

Then using the GetSummary function (not sure if this has
changed in FileMaker 7) you can calculate the 1/Xth
fraction in a similar way to above:
ie.
ThisNameFraction {Calculation, Number result}
= 1 / GetSummary(ThisNameCount, Name)

Using the "Name" field as the second parameter means the
summary data retrived by the GetSummary function will
only be for that unique name.

This gives you the same counter field as the Relationship
method, so it can be totaled using the same Summary field:
ie.
UniqueNameCount {Summary, NOT Running Total}
= Total of ThisNameFraction


Either way, the summary field UniqueNameCount will give you the total
number of unique names. It needs to be placed in a Leading or Trailing
Grand Summary part of a layout, and then sort the records by Name and
view the layout in Preview Mode or print it out.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thank you very much. I have printed your answer and will pursue the summary
fields approach.

Thanks again!!


"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote

I did this. I am using FMP 7. I selected Summarize repetitions: All
Together (instead of individually)



I did this. Do I need the 1/ before GetSummary???

I did this, again choosing Summarize Repetitions: All Together

I followed your advice and I am coming up with the number 1 in the Leading
Grand Summary.

In article <6e-dnd13xKI2K5LeRVn-1w (AT) comcast (DOT) com>, "Stephen Larivee"
<NOlariveeslSPAM (AT) yahoo (DOT) PLEASEcom> wrote:

Sorry, I haven't used FileMaker 7, but "Summarise Repetitions" sounds a
bit peculiar. You could prehaps try the "Individually" option and see
what happens.

The "1/" is needed since that gives each record a 1/Xth fraction of the
toal number of records for that name, then when they're all added
together you get a total of 1 for that name. Then adding together all
these you get the total number of unique names.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote


Thank you. I will give it another try.

In article <Vv2dnZ2dnZ23zJfwnZ2dnV4hkt6dnZ2dRVn-0Z2dnZ0 (AT) comcast (DOT) com>,
"Stephen Larivee" <NOlariveeslSPAM (AT) yahoo (DOT) PLEASEcom> wrote:

One other thing - you DO have to sort the records and then print the
layout or view it in Preview mode for Summary fields to work properly.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

There is another way to get a count or list of unique names.
Create a value list of the name field.
Set a global text field via script to:

ValueListItems ( Get ( FileName ) ; "VLNAMEHERE" )

This will put the contents of the value list into the global text
field. To get a count of the names, count the carrage returns + 1
because the last name doesn't have a carrage return. This also allows
you to get the names out by themselves if you need to work with them.

In article <1125122044.367321.95580 (AT) z14g2000cwz (DOT) googlegroups.com>, "FP"
<a (AT) pottnerconsulting (DOT) ca> wrote:

DOH!

Of course. I always forget about that sneaky, backdoor method. )

You don't need a script and Global field though.

You can have a "semi-live" count by simply defining a Value List to get
it's values from the Name field and then creating a new Calculation
field to count the names:

NumUniqueName {Calculation, Number result}
= PatternCount (ValueListItems(FileName, ValueListName),
"¶") + 1

where "FileName" is the name of the database file and "ValueListName"
is the name of the Value List you defined.

The "¶" symbol on the last line is the carriage return character which
is availble via a button in the define calculation windows (next to the
"-" symbol on my copy, but it may have been moved in FileMaker 7).

The only real problem here is that a new name isn't added to the Value
List until you change to another record (or possibly by committing the
new record to disk). Simply exiting the new record by clicking outside
all fields or pressing the Enter key doesn't add the name.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Harry,

I had better luck with your first method (creating a Name relationship).
That is coming out fine.

Once again, you have lived up to your name!!

Thank you for your help and patience.


"Helpful Harry" <helpful_harry (AT) nom (DOT) de.plume.com> wrote