Unanswered: Calculating a count of items, without counting duplicates
I am developing several databases for a non-profit organization. The organization keeps track of visitors from various countries. I know the calculation for "counting" each visitor, but how do I count total number of countries represented?
For example, the organization has had 450 visitors from 110 countries. I want each unique country to be counted only once, even though the country might appear in several records (each record is for one visitor). There are anywhere from 1-20 visitors from any given country in the database.
The country is recorded in a text field by itself. I imagine this involves some sort of IF, THEN calculation.
Any assistance would be greatly appreciated. Thanks.
Originally posted by Munir
still need some guidance on this.
Assuming you have a Unique Id or a serial in that file, create a relationship on the Country field, using a SelfJoin FileA:Country::FileA:Country.
Use this calculation
CheckDupes = Case(Serial =:elfJoinerial,1,0)
As this calculation tags the first occurance with a 1 and 0 for all others, create a SelfConstantRelationship (using a calc indexed =1 at both sides of your relationship) and use calculation
This would give you the total number of country in your db.