I need to extract information about our program participants from years gone by, without including any more recent information. I can select a program year -- say 2004/05 (school year) and find all the partipants that year and earlier (each year for each participant is a separate record in the AY_activeYears table). By exporting the contact_ID and activeYear of the found records of active years that fall into my range to a temporary FM file, I can eliminate any more recent information. Now I want to count how many of each contact_ID I have in my temporary file. That number equals the number of years each person has participated (longevity). For instance, I might have 7 records for contact_ID #1 which equals 7 years of participation or 2 records (or 2 years) for #21, etc. I want a calc/summary to give me the total years/records for each contact_ID. Then I need to summarize how many contact_IDs there are with each X number of years/records. It should be simple but I can't make it work. Please help.

If I'm not clear (not unusual) I apologize.

Just to clarify - you have data such as:

Contact 1 Year 2001

Contact 1 Year 2002

Contact 1 Year 2003

Contact 1 Year 2004

Contact 1 Year 2005

Contact 2 Year 2000

Contact 2 Year 2001

Contact 2 Year 2002

Contact 2 Year 2003

Contact 2 Year 2004

Contact 3 Year 2002

Contact 3 Year 2003

Contact 3 Year 2004

Contact 4 Year 2001

Contact 4 Year 2002

Contact 4 Year 2003

Contact 5 Year 2000

Contact 5 Year 2001

Contact 5 Year 2002

and you want to get:

5 years: 2 contacts

3 years: 3 contacts

What is the intended end product of this - does it go into a report or what?

Exactly.

The most recent year can be anywhere from 1988 to the present, and the parameters for selecting participants can vary with every search (i.e., all the females in years 2001-2003, or all the students in years 1999 and 2001 by gender).

We are a grant-funded organization and must report to our funders on a quarterly basis. They let us know what they want reported and each grantor might ask for different demographic/participation information. This quarter, one of our funders is asking about total high school participants, by gender, by ethnicity, by longevity of participation. The find process is easy enough, but the summarization calcs are what I haven't been able to figure out.

Actually, I have figured out half of the problem -- I can calculate the number of occurances for each ID. But I still can't summarize the number of unique IDs for each year.

Edited December 3, 200916 yr by Guest
Additional information

Well, it's not easy to summarize by a summary. I'd probably do it using a method called Fast Summaries:

http://www.kevinfrank.com/demo-files-78.html

Roughly, you sort your records by ContactID, re-ordered based on a summary field (count of SerialID). Then you loop through the groups: if the count of the group is the same as the previous one, you raise your counter by one; otherwise you write a new report line with the current counter and the count of the group, and reset the counter to 1.

Note:

I don't see why you need to export data in order to isolate it - a simple find should be all it takes.

I was exporting because the calculation that add up the total years of participation lives in the contactInformation table and counts ALL the years so I couldn't get totals up to a certain year and not after. But I could export only the relevant activeYear information. It feels like duplication of data, but now I also have a calc totaling active years in the activeYears table so a simple find should work for me.

Thanks for the referral to the 'fast summary' example. It's way beyond what I would have been able to develop for myself; I never even thought of using variables. But it looks like it does exactly what I need. I'm having a great time playing with it and poking around under the hood.

I was exporting because the calculation that add up the total years of participation lives in the contactInformation table and counts ALL the years

All you need for this is another relationship to the Years table, filtered by the cut-off year. Have a look at the attached - it also shows another way to produce your requested result, this time from the Contacts table.

SumSummary.fp7.zip