Forgive me, but I've only used Filemaker in the most prefunctory way
until recently, and I've played enough around with MySQL to be really
confused right now with a FM7 database I'm trying to create.

I have information in a table about courses my department has offered
over the past decade. This information is includees the fields YEAR,
SEMESTER, COURSE, STUDENTS, and SECTIONS. The latter two contain the
total of number of students in COURSE during that semester, and the
number of sections of that course we offered then. (For example, we
could calculate the average number of students per section during that
semester by finding STUDENTS/SECTIONS.)

What I want to do is summarize the total number of students our
department taught each semester and each year and the total number of
sections we offered each semester and year.

How can I do this in Filemaker? I've heard the terms "self
relationship" and "match fields" but I don't know how these work. I
can't find them references in the Missing Manual book (on FM8) I own,
and I can't find anything helpful in the on-line help.

Can anyone explain how I might create the conditional sum(s) I seek?
Thanks in advance.

The term you are looking for in the help file is summary field and you
will need to do total summary fields for number of students and number
of sections. Then, if I have understood correctly what you want to do,
your best bet is the new layout wizard. Go into layout mode, create new
layout (Layouts>NewLayout/Report menu) select columnar list report
(give your layout a name as you go through that page of the wizard, and
choose the table that the records will be coming from). In the next
screen go for report with grouped data, include subtotals and grand
totals, choose the fields you want to see, choose your groups, sorts
etc and say yes to the offer to make a script.

What you will end up with is a fairly standard report which will do
what you want and a script which will do the necessary data sorting to
make it work - but more importantly it will give you something to pick
apart and work out what makes it tick. If itr doesn't quite fit your
need you can then modify it easily.

Just one thing if you are not familiar with FileMaker you have to go
into Preview mode to get a proper picture of what the report is going
to produce.

In article <1131745669.628700.174370 (AT) z14g2000cwz (DOT) googlegroups.com>,
"millerj" <millerj (AT) truman (DOT) edu> wrote:

You don't need a relationshipat all if you're only printing a summary
report of your data. It's easier to use Summary fields.

You say you want to summarise the "total number of students" and the
"total number of sections", both for "each semester and year".

First you'll need two extra fields:

s_Students {Summary Field}
= Total of Students

s_Sections {Summary Field}
= Total of Sections


Next you'll need a new Layout which will be printed (or read on-screen
in Preview Mode) that is something along the lines of:


Year: [Year]


Semester: [Semester]


Course Students Sections
[Course] [Students] [Sections]


Semester Totals: [s_Students] [s_Sections]


Year Totals: [s_Students] [s_Sections]


GRAND TOTALS: [s_Students] [s_Sections]





Notes: [] denotes a field on the layout.
You don't actually need the Body Part if you don't want all
the individual Courses listed.
Those *ARE* the same Summary Field in the three Trailing
Sub-summaries Parts.

All done!

Now all you need to do is Find the appropriate records you want the
report for, Sort them by Year and Semester (and Course if you want
those alphabetical) and then print or Preview the layout.

It doesn't matter whether you sort the Year and / or Semester in
increasing or decreasing order (whichever you prefer for the report),
BUT they do need to be Year first and Semester second.

FileMaker will do all the hard work of dividing the report into the
appropriate sections and sub-totals for you. )




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Just to give a hint of what it is :
A relationship is made between 2 files (FMP6) when a given field has the
same value in both files. Say a 'CompanyID' between a 'Persons' file and
a 'Company' file, which permits you to see in the Company File who are
the persons who belong to it. In that case CompanyID is a 'matching'
field.
A 'self relationship' is a relationship between a file and itself. With
my example, a self relationship in the Persons file with CompanyID as a
matching field allows you to see in a portal of a person record layout
those who belong to the same company as his.
Remi-Noel