Re: Counting records with duplicates

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

Relevant Pages

  • 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)
  • help with report
    ... It's been a while since I have done programming work in FMP, ... I'm a little rusty. ... I made a summary report that shows the number of hours logged for each ... and the summary part is set up to summarize on the field "Job#". ...
    (comp.databases.filemaker)
  • 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: 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)