Counting only unique records

[EdS]

Each record in my mediation log has fields for ID, date, family, month. For any one family there could be 8 ID's associated with the month BUT those 8 ID's might only represent 5 individuals. I can write a formula to count the 8 but can't conceptualize how to count the five. I'd like to print the results by month using summary fields if possible as follows:

Month total individuals

Family A 8 5

Family B 12 8

any ideas?

[ESpringer]

I'm not sure I understand: it seems an ID is not for a person, but for a visit? Among the fields you mention, I see no place to record the name or ID *for an individual*? So -- help me out here -- what is it in your database that distinguishes individuals?

Or is it that individuals *do* have individual IDs, and that each log record relates to only one such individual, so that there may be eight RECORDS (for a family in the course of a month) with only five distinct individual IDs (three individuals appear twice)? (In that case I wouldn't say "there could be 8 ID's," but maybe you meant eight records with IDs in them?)

If your main file is for Sessions, and each individual has an ID, there should be a related table called Individuals (where the match is Individuals::ID = Sessions::ID). Then, you could use a summary count calculation to see how many records for individuals are related to your found set (or your sorted group in sub-summary sorted mode).

Tell us more about the structure you're aiming at, and what your file structure is. In particular, what does each record in the log represent (a visit with an individual? A group meeting with multiple people?), and what is being "IDentified" by your ID field...

[EdS]

Sorry about the shortage of information! In a mediation several students meet to resolve the problem. Each student belongs to a school "family". Often students from different families mediate together. Two files support the system.

The event log records info about the nature of the conflict (eventID, date, category, referring person, description, etc.). The event log relates to the participant log (by eventID) which stores the attributes of each participant (id, name, grade, ethnic, school family etc.).

I am reporting participant counts from the participant file and want to report monthly the total number of students in each family who mediated (participants) and the number of actual students. In a typical month a family might have 15 participants (occurrences) involving only 10 students (a few repeaters). If Susie mediated 3 times, she would count 3 times as a participant but only once as an individual.

I would like to organize the report by month with each line within the month showing: school family name, participantCount, individualCount

I hope this helps clarify.

[ESpringer]

Sorry this is so late. In FM6, though, I don't see why you would try to do this with only two files. You should have as many files as "kinds of things": (1) individuals/participants; (2) Families; (3) Events, and perhaps (4) Involvements/Roles/Participations (a join file to link individuals to events and note their role relative to that event).

I don't see why you conflate the two when you write that "in a typical month a family might have 15 participants (occurrences) [and this is also the same as students who mediated? Is there no other way individual students participate?]... unless you mean that there are 15 role-takings of the kind you'd track in what I'd call the Participations file. (That's a clearer name than "participants" because the number of participants really is the same as the number of individuals, but the number of "participatings" or participations can be higher.)

Unfortunately, for what you want to do, you'll have to use calc fields as "bridges" (or "tunnels") to get the count of individuals across to your family file via the Participations file (it becomes much easier in FM7)...

The participations file has fields like EventID,Date,ParticipantID,Role; but it must also then have the participant's Family ID (which can be auto-enter) so that the Family file can find the Participations of its members. If you want to break the report down by month, you need a concatenated Family_Month calc field in both the Families and the Partcipations databases. Then, I believe, you can build a VALUE LIST of all the individual names who turn up in the Participations file FOR that family FOR that month. Since value lists weed out duplicates, you can then perform calcs on this list to display the individuals who participated, or to count them as individuals, etc...

At any rate -- if you're still out there -- feel free to ask more. I'll stop since you may have drifted away by now and I may have drifted away from your actual task...