Re: Counting records with duplicates


"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.


.

Relevant Pages

  • 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: Software metering data not showing up in reports
    ... summarize data 12 hours old or older. ... > About to generate report head]Log]!><time... ... > Pacific Daylight Time> ... > New usage processing thread started, ...
    (microsoft.public.sms.inventory)
  • Re: How do I count individual records in the Detailed section of a
    ... By sub report do you mean detailed summary? ... By class I mean Class/Section for instance Acc220 002. ... attributes I mean Class average GPA, class minimum GPA, class maximum GPA, ... I am able to summarize most of the attributes I am looking for in the Detail ...
    (microsoft.public.access.reports)
  • Re: crosstab for durations
    ... I'm not sure how you can do this in a cross tab. ... details section and summarize it in the group footer for the project ... Therefore I would like to produce with crystal reports a crosstab report ... the duration for the period. ...
    (microsoft.public.vb.crystal)
  • Re: numerating pages, and summarize fields
    ... Summaries in the report footer will appear only at the end of the ... If you want to summarize the information on the page, ... > report header section1 ...
    (microsoft.public.vb.crystal)