1 2 Previous Next 15 Replies Latest reply on Mar 8, 2012 3:13 PM by Malcolm

# Deriving stats - via Find, or via Summary fields?

In my solution, I’m dealing with Attendance records and their related People records.

Given a set of Attendance recs (which I’ve isolated, via a GTRR, by date range specified by the user), I then need to some analysis of the related People records. So I’m doing a further GTRR to isolate the related People recs.

I then need to find, within that set of records, the number of men, women, disabled people, people over 60, etc. (about 10 criteria at the moment, probably more in the future).

I’m trying 2 different approaches, and wondered whether either of these is better than the other, or whether there’s another, better, way that I’ve missed.

Approach 1 – I obtain the stats via a succession of Finds. So, for example, to get the number of women, I do a “Constrain Found Set” with the gender field set to “F”, store the found count in a variable, go back to the full set of records (by repeating the GTRR), do the next find, and so on.

Approach 2 – I add a calc field to the People table for each of the criteria, e.g. PPL_x_Female, which is defined as Case (PPL_Gender = "F";1;0). I then have a corresponding summary field for each criterion, PPL_xs_Female, defined as a Total of PPL_x_Female, to give me the total no. of women in the current found set.

I feel that approach 2 is better, although I’m worried about the no. of extra fields that I’ll accumulate in the PPL table.

Any recommendations as to which approach (if either…) to use? I really need to settle on the best technique at the outset, as I’ll be using it very widely in the system, and would really appreciate your guidance.

Thanks in anticipation,

Dave.

• ###### 1. Re: Deriving stats - via Find, or via Summary fields?

Can't you think of a more complicated solution or do you just love the work?

I think it can be done with two fields.

Create a stored calc field with the formula "1", let's call it "fl_true"

Create a summary field which is Count of the field "fl_true", let's called it "s_count found set"

Place the field "s_count found set" onto any of the sub-summary parts and it will provide the correct total for that sorted group.

Malcolm

• ###### 2. Re: Deriving stats - via Find, or via Summary fields?

Malcolm,

Thanks for this (and I suppose I do love the work in a way, but not that much!...).

I can see how your suggestion would work in a simple sub-summary report based on People, but I didn’t give you the full story.  The report I need to generate is actually based on the Attendances, and needs to look like this:

Programme 1 – Attendances: 47  (By 25 people - 12 women, 13 men, 3 disabled, etc.)

Programme 2 - Attendances: 24  (By 15 people - 11 women, 4 men, 2 disabled, etc.)

-----------------------------------------------------------------------------------------------------------------

Tot Attendances: 999   (By a total of 423 people - 213 women, 210 men, 61 disabled, etc.)

So the Attendances can be summarised with sub-summaries, but not the People (because it’s likely to be the same people in different Programmes). The report uses sub-summaries, but only for the Attendance totals (at various levels – e.g. Programme Type, Department, etc. which, for clarity, I haven’t shown on the illustration above).  And, as far as I can see, the People analysis within the Attendance stats will need to rely on other methods.

At least I think so, but maybe I am making the whole thing a lot more complex than I need to?  Nobody would be happier than me if there's a simple way to skin this particular cat...

Dave.

• ###### 3. Re: Deriving stats - via Find, or via Summary fields?

Dave Hobson wrote:

The report I need to generate is actually based on the Attendances, and needs to look like this:

Programme 1 – Attendances: 47  (By 25 people - 12 women, 13 men, 3 disabled, etc.)

Programme 2 - Attendances: 24  (By 15 people - 11 women, 4 men, 2 disabled, etc.)

-----------------------------------------------------------------------------------------------------------------

Tot Attendances: 999   (By a total of 423 people - 213 women, 210 men, 61 disabled, etc.)

So the Attendances can be summarised with sub-summaries, but not the People

I am missing something here: why can't you sort and summarize the Attendances records by fields in the People table?

Of course, the etc. part may be problematic, if the totals do not add up. In your example, there are 25 people attending Programme 1 - but the total of men, women and disabled is 28. How many different categories do you have, and how is a person membership in a category recorded?

Message was edited by: Michael Horak

• ###### 4. Re: Deriving stats - via Find, or via Summary fields?

Thanks for the input, Michael.  Sorry I didn't make it clear.  The totals do not add up, and that adds a level of complication that I'm struggling with.  People are always men or women (25 in the example), but of those, 3 are disabled, 4 are of an ethnic minority, etc.  The categories are either via fields within the People rec (e.g. PPL_Gender), or via "tags" (in a related Tag Allocation table - a person is "disabled" if s/he has the "disabled" tag).  So the analysis is based on a mixture of the two.

This, combined with the fact that a person can have more than one attendance within the set (i.e. the no. of attendances does not necessarily equal the number of people attending) is another layer of complexity.

Dave.

• ###### 5. Re: Deriving stats - via Find, or via Summary fields?

Hi Dave,

The attendance table sounds like it is probably fairly simple, and can handle having 3 more number fields added to it.

How about creating and summarizing some special number fields. In the Attendance record, do an auto enter-enter by calc as each att-rec is created based on the status of the person:

• if_Male, auto-enters 1 if Male
• if_Female, auto-enters 1 if Female
• if_Disabled, auto-enters 1 if Disabled.

Your sums of each of these should be accurate for your split level reporting summaries. The totals of F+M should always equal the total record count, but the Disabled sum will vary independently.

Stephen Huston

• ###### 6. Re: Deriving stats - via Find, or via Summary fields?

Stephen,

Thanks for this, and I understand your suggestion.  But the problem is that a Person can be associated with more than one Attendance within each summarised level, i.e. the number of People is (probably) less than the number of Attendances.  So counting a male for every "attendance by a male" does not give me the correct statistic, which should be the number of "unique males" in this group of Attendances - i.e. it's not good enough to say that "there were 50 attendances, of which 30 were by men":  what we need to be able to say is "there were 50 attendances, of which 30 were by men, but only 23 different men".  That's why, once I've got a set of qualifying Attendance records, I'm using a GTRR to get the related People recs, and finding out the number of men, women, and all the other categories from that set of People recs.

Or am I barking up the wrong tree?  I've got a sneaky feeling that, as Malcolm suggested earlier, I'm making this unnecessarily complicated, but a the moment I can't see the simpler way to get the information that we need.

Dave.

• ###### 7. Re: Deriving stats - via Find, or via Summary fields?

Dave Hobson wrote:

The categories are either via fields within the People rec (e.g. PPL_Gender), or via "tags" (in a related Tag Allocation table - a person is "disabled" if s/he has the "disabled" tag).

There is a complication here, and it's people having two types of attributes. Let's skip this issue for a moment, and suppose there is a repeating field in the People table, with each repetition indicating the presence of one attribute (e.g. if repetitions #1, # 3 and #5 contain 1, then this person is Male, Disabled and an Ethnic Minority).

Now, if you define a new relationships chain as:

Programs -< Attendance 2 >- People 2

with the relationship between Programs and Attendance 2 being filtered by a date range, you can place a repeating summary field from People 2 on the layout of Programs to receive the detailed summary.

• ###### 8. Re: Deriving stats - via Find, or via Summary fields?

Would this work?

In the Attendance programme table add one global field to hold the results of a custom function like http://www.briandunning.com/cf/1373

Passing as the list List(Attendance:PeopleID), this would return a clean list of people ids, draw a relationship from programme to people 2 and collect up your info.

HTH,

Tim

Should be from summary level table, that way you can grab the values directly in the summary part of the report. Message was edited by: timwhisenant

• ###### 9. Re: Deriving stats - via Find, or via Summary fields?

Thanks for this, and I understand your suggestion.  But the problem is that a Person can be associated with more than one Attendance

Well, of course, they'll want to attend more than one session.

create a TO from Attendance called AttendanceSelfJoin

create the relationship Attendance::Program ID = AttendanceSelfJoin::Program ID

Create a TO from People called AttendancePeople

create the relationship AttendanceSelfJoin::Person ID = AttendancePeople::Person ID

From Attendance you can now look through the self join into AttendancePeople. By looking through the filter of the self-join relationship you can see the entire group.

You will need to track the attributes of these people as distinct fields. I am discovering the joys of normalisation so I would create a separate table "People_Attributes" that contained lots of number fields, eg, person ID, isMale, isFemale, isWheelChairReqd, etc. each of which will be empty or carry the number one. (Each record containing only one attribute, so that sub-summary reports on multiple categories works).

With that in place, you have a direct line of sight from Attendance to People_Attributes and can start to crunch the numbers.

Malcolm

• ###### 10. Re: Deriving stats - via Find, or via Summary fields?

Tim,

That looks like a very handy CF, which I will definitely find useful, possibly with this, but certainly in other situations  Thanks for pointing it out.

Dave.

• ###### 11. Re: Deriving stats - via Find, or via Summary fields?

Malcolm,

Thanks for this.  I understand now how to see the related People without having to use GTRR.  I also like the idea of the normalised Attributes table, but I'm struggling to understand how to implement it.  Do you mean there would be a record for each attribute held by each person?  And re. the creation/deletion of each record - am I right in thinking this would be via the setting or changing of (for example) the gender on the person's detail layout?

Thanks again for the help.

Dave.

• ###### 12. Re: Deriving stats - via Find, or via Summary fields?

Michael.  I haven't really used repeating fields, so will need to get my head around this, but it sounds very interesting.  I need to spend some time working out how best to do this - it seems that there are various options, which comes as both a relief and a challenge!

Thanks,

Dave.

• ###### 13. Re: Deriving stats - via Find, or via Summary fields?

Hi Dave,

To get the count of actual people/attendees from the found set of attendances you want to use...

Use Go to Related Records for the Entire Found Set, capture the Found Count in that set, and return to where you want to use it.

If you have several different criteria for which to count attendees, you can make separate TOs for each relationship criteria, and capture the results via variables or global fields.

Stephen Huston

• ###### 14. Re: Deriving stats - via Find, or via Summary fields?

Dave Hobson wrote:

Michael.  I haven't really used repeating fields, so will need to get my head around this, but it sounds very interesting.  I need to spend some time working out how best to do this - it seems that there are various options, which comes as both a relief and a challenge!

Another option - which may be simpler to set up, even if more tedious - is to define a calculation field in the Programs table for each attribute you need to count. For example, cDisabled =

Count ( People 2::Disabled )

where Disabled in the People table is a field that's empty for a non-disabled person.

Then you also need to add a summary field for each such calculation field.

1 2 Previous Next