counting unique data

[jpac]

I am trying to create a script. In the attached file there are fields that list the location names and inspection dates. In some cases there are muptle dates for one location. I want to count Location Names as one inspection and show a total of all inspections. HELP

1-6-200.fp7.zip

[Fenton]

There are 2 ways to do this. The simplest is to create a self-relationship on the Location. Then count via a calculation:

Sum ( self-relationship::Loc Name )

You could also store the Locations in their own table, one record each, with a relationship to the inspections on the Location name, then count from there. More or less the same thing. But more "relational," with the advantage that you would only need to store things like the location address once. Also easier to see an "overview."

The "total" of all inspections is just the found count; you could use a Summary count field of that in a "Subsummary Part" for a report; but otherwise it's just the found count.

I redid your "sub total" field. I couldn't see what the calculation was doing; it was an odd one.

InspectionLocations.zip

[jpac]

Hi, I've tried to apply the solutions you have suggested without success. The problem is my skill level. I'm pretty much a newbe. While I am a ouick study, I don't seem to be thinking in that database mind-set yet. To arrive at the correct answer, I exported the file as a csv file, opened the file in Excel, used the sub-total function grouping by location name, then running another sub-total on the location names to arrive at a grand total of 31 inspection for the 34 violations, Convoluted, but it worked. I do thank you for help.

[Kent Durvin]

Another way to count unique values:

Make a value list, "Locations" with the contents of the location field. That will only have one entry per value.

Then to count the values, make a calculation field with:

PatternCount(ValueListItems("Count.fp5", "Locations"),"

[-Queue-]

I believe Fenton intended to use Count instead of Sum in the calculation he posted. Summing names doesn't make sense; counting them does.

[jpac]

HI GUYS, I still get any of this to work for me. I open to any new suggestions. Thanks