Counting number of summary fields

[David Feyrer]

I have produced a list of persons taking courses. How can get a coun
tof the number of persons in the summary fields?

David Feyrer
Epiphany Enterprises

[Helpful Harry]

In article
<4d0a2a53-4c30-4e1c...@a15g2000yqf.googlegroups.com>, David

Summary Fields look simple, and in fact making the Summary Field is
simple. Obviously what you want is just:

s_NumCourseAttendees Summary
Count of PersonName

(It's usually better to use a unique ID rather than a name.)

*BUT* it's using the Fields that cause a lot of people problems. This is
partly due to the flexibility of Summary Fields - what they summarise
depends on where you put them on the Layout and how you Sort the Records.

Another problem is often caused by the fact that Summary Fields do not
really work in Browse Mode - you have to Print or Preview the Layout for
FileMaker to calculate the sammary results. (If you want an on-screen
summary, then it's better to use a Relationship and Calculation Field.)

For your purposes you could create a report Layout that looks something like:


Number of Participants by Course

Course Name Number of Attendees

|Header
------------

[CourseName] [s_NumCourseAttendees]

|Sub-summary (by CourseName)
------------

Total Participants: [s_NumCourseAttendees]

|Grand Summary (Trailing)
------------


where the [] denote Fields.

If you then Find some / all Records and Sort them by the CourseName Field,
you can Print / Preview this Layout to get something like:

Number of Participants by Course

Course Name Number of Attendees

Accounting 15
Biology 14
English Lit. 11
Mathematics 18
Zoology 12

Total Participants: 70


Unless you specifically want to list each Record (e.g. the person's name),
you do not need a Body Part on the Layout.

Putting the s_NumCourseAttendees Summary Field in the Sub-summary Part of
the Layout means it will summarise the Records broken down by the Sort
order of that Field ... as long as you do remember to Sort the Records.

Putting the same s_NumCourseAttendees in the Trailing Grand Summary Part
of the Layout gives an overall count of ALL attendees (within the Found
Set).

You could break it down further with Sub-summary Part that count attendees
attending by Course category (e.g. Science, Arts, Literature, etc.) using
the same Summary Field. You have to Sort the Records appropriately as
well.


Helpful Harry :o)

[David Feyrer]

On Feb 7, 12:25 am, HelpfulHa...@BusyWorking.com (Helpful Harry)
wrote:
> In article
> <4d0a2a53-4c30-4e1c-b9cd-af8bc75e8...@a15g2000yqf.googlegroups.com>, David

This gives me the number of course attendees per class but what do I
do if students take more than one course?

[Helpful Harry]

In article
<f9b4cf89-f5dc-4c69...@hb4g2000vbb.googlegroups.com>, David

Feyrer <dfe...@feyrerobx.com> wrote:
>
> This gives me the number of course attendees per class but what do I
> do if students take more than one course?

It was only an example of using Summary Fields, but it will work if each
course a students is taking is a separate Record (e.g. in a related
Enrolments Table).

If the courses are stored in a Repeating Field or normal Text Field, then
you've got a rather difficult job to try and summarise that and it can't
be done using Summary Fields. Your best option would be really to change
the database to use a related Enrolments Table.

It's not possible to give an exact answer without knowing how your
database is structured, how the data is stored, and what you want to
obtain from it.

Helpful Harry :o)

[David Feyrer]

On Feb 9, 12:16 am, HelpfulHa...@BusyWorking.com (Helpful Harry)
wrote:
> In article
> <f9b4cf89-f5dc-4c69-b781-85d5bec5b...@hb4g2000vbb.googlegroups.com>, David

Harry,

I think you are correct, I need to redesign the data base around using
an enrollments table with courses being the multiple rather than the
way I have it now which is multiple students. ? ? ? Otherwise I need
to devise some fancy calculation.

David

[Helpful Harry]

In article
<c0a4a9ae-f133-4d80...@m5g2000yqk.googlegroups.com>, David
Feyrer <dfe...@feyrerobx.com> wrote:
> On Feb 9, 12:16=A0am, HelpfulHa...@BusyWorking.com (Helpful Harry)

> wrote:
> > In article
> > <f9b4cf89-f5dc-4c69-b781-85d5bec5b...@hb4g2000vbb.googlegroups.com>,
> > David Feyrer <dfey...@feyrerobx.com> wrote:
> > >
> > > This gives me the number of course attendees per class but what do I
> > > do if students take more than one course?
> >
> > It was only an example of using Summary Fields, but it will work if each
> > course a students is taking is a separate Record (e.g. in a related
> > Enrolments Table).
> >
> > If the courses are stored in a Repeating Field or normal Text Field, then
> > you've got a rather difficult job to try and summarise that and it can't
> > be done using Summary Fields. Your best option would be really to change
> > the database to use a related Enrolments Table.
> >
> > It's not possible to give an exact answer without knowing how your
> > database is structured, how the data is stored, and what you want to
> > obtain from it.
>

> Harry,
>
> I think you are correct, I need to redesign the data base around using
> an enrollments table with courses being the multiple rather than the
> way I have it now which is multiple students. ? ? ? Otherwise I need
> to devise some fancy calculation.
>
> David

There are many ways of creating a database like this and it depends on
what you need it to do, but the "normal" approach would usually be using
three Tables, something like:

Students <-> Enrolments <-> Courses

linked by ID codes (StudentsID and CourseID).

The reports like the one you're trying to generate can be easily produced
through the Enrolments Table, as can other reports like an attendee name
list to give each course teacher.

Helpful Harry :o)

[David Feyrer]

On Feb 15, 3:07 pm, HelpfulHa...@BusyWorking.com (Helpful Harry)
wrote:
> In article
> <c0a4a9ae-f133-4d80-bce2-0e75d3a45...@m5g2000yqk.googlegroups.com>, David

Harry,
Have already made the modifications, thanks much.

David