If and Sum functions

[Vecordious]

Hi All,

Here is my delimma.

I have a timetable database and a workload database which calculates each teacher's 'points' based on the amount of hours taught.

I am having trouble getting the workload database to look at the timetable database and select only semester 1 classes and add them up (same goes for semester 2)

The way the workload database works is, each teacher enters their initial and on doing so will get a summary of all the hours they have taught in semester 1 and 2 and a total of their points.

Here's one of the formulas I have tried:

If ( Timetable::Semester="1";Sum ( Timetable::Workload points ) )

What it does is calculate the entire year of teaching instead of only Semester 1. And it won't calculate anything for Semester 2 (using the same formula with Semester=2).

P.S. I am very much still a novice at this.

Edited September 30, 2008 by Guest
Spelling corrections

[dreamingmind]

If ( Timetable::Semester="1";Sum ( Timetable::Workload points ) )

That formula says:

If the related record's 'semester' field contains a '1', then sum the 'workload points' fields in the related table.

Restated:

Sum all the related records when 'semester' = 1

What you want is to have a relationship that sees only the semester 1 records for a teacher and sums those.

Right now, you probably have the two tables related on teacher. You'll need it related on teacher and semester.

Maybe that's enough to set you on the right track?

Regards,

Don

[Vecordious]

Hi Don,

Thanks for your reply.

Therein lies the problem. I can't relate the two tables on Semester because the 'Workload' section has two fields called "Semester 1 points" and "Semester 2 points" which is meant to automatically calculate the points for each semester separately.

The timetable has both semesters in it. I'm trying to minimise the amount of clutter by only using one timetable file instead of splitting it up into 2 files. Another reason for only wanting one file is our timetable search engine (through IWP) is tied to it.

I was thinking that there could be a way to get a calculation to find all semester 1 hours then sum up the points associated with each of those. Or maybe a script?

Regards,

V.

[Vaughan]

I've worked on a similar system.

First, you need to identify semesters by their year, otherwise you'll end up with lots of records marked "Semester 1". They need to be something like "2008 Semester 1" or perhaps put the year in a separate field and include the year in the relationship.

"the 'Workload' section has two fields called "Semester 1 points" and "Semester 2 points" which is meant to automatically calculate the points for each semester separately"

This is part of the problem. Each workload record should be for one semester only. Two records one field; not one record two fields.

[Vecordious]

Hi Vaughan,

Thank you for that. Yes, I do have a field for the year but that doesn't do much for what I am trying to achieve.

I till try your suggestion of having different records for calculations in different semesters instead of having calculations for both semesters in one record.

Regards,

V.