Re: Counting records with duplicates


"Helpful Harry" <helpful_harry@xxxxxxxxxxxxxxxx> wrote in message
news:270820051305321422%helpful_harry@xxxxxxxxxxxxxxxxxxx
> In article <6e-dnd13xKI2K5LeRVn-1w@xxxxxxxxxxx>, "Stephen Larivee"
> <NOlariveeslSPAM@xxxxxxxxxxxxxxx> wrote:
>
>> "Helpful Harry" <helpful_harry@xxxxxxxxxxxxxxxx> wrote in message
>> news:270820051135134533%helpful_harry@xxxxxxxxxxxxxxxxxxx
>> > In article <0OmdnZ2dnZ2e-l2vnZ2dnWoXkt6dnZ2dRVn-zJ2dnZ0@xxxxxxxxxxx>,
>> > "Stephen Larivee" <NOlariveeslSPAM@xxxxxxxxxxxxxxx> wrote:
>> >
>> >> 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???
>> >
>> > 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
>>
>> I did this. I am using FMP 7. I selected Summarize repetitions: All
>> Together (instead of individually)
>>
>>
>>
>> >
>> > 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)
>>
>> I did this. Do I need the 1/ before GetSummary???
>>
>> >
>> > 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
>>
>> 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.
>
> 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)


Thank you. I will give it another try.


.

Relevant Pages

  • Re: Reports - Summarizing Summary Fields...
    ... summary field summarize each columns data by month. ... ie. the Sub-summary Part totals up each Month's values ... values for ALL the records within the report. ...
    (comp.databases.filemaker)
  • Re: Counting records with duplicates
    ... I would like to generate a report that ... I selected Summarize repetitions: All ... >> Then using the GetSummary function (not sure if this has ... again choosing Summarize Repetitions: ...
    (comp.databases.filemaker)
  • Re: Grouping and summarizing data
    ...  how to group and summarize data. ... SumCustomer is summary field: total of price ... Now sort by customer. ...
    (comp.databases.filemaker)
  • Re: Counting records with duplicates
    ... About 1/2 of them are duplicates, ... I would like to generate a report that ... > Then using the GetSummary function (not sure if this has ... again choosing Summarize Repetitions: ...
    (comp.databases.filemaker)