Adding field values together

[isjigsaw]

Help!

This should be simple but has been driving me crazy for a week solid now and is not looking like I'm going to figure it out anytime soon...

What I'm trying to do is to keep track of individual scores for a sparring competition. I'm concerned with total for the month, quarter and year.

Each quarter is simply

Sparring Month 1 + Sparring Month 2 + Sparring Month 3 (for Quarter 1)

The Year is also simply

Sparring Quarter 1 + Sparring Quarter 2 + Sparring Quarter 3 + Sparring Quarter 4

There are 12 fields for the months labled Sparring Month 1, Sparring Month 2 and so on.

There is also a seperate field labled Sparring Additional Points

The idea is that when points are put in the Sparring Additional Points field they are to be added to the current month (as specified in the Sparring Active Month field). The Sparring Quarter and Year fileds would also then update based on the new month value.

I tried the following in the individual months:

If ( Sparring Active Month = "1"; Evaluate ( Sum ( Sparring Month 1 Total; Sparring Additional Points); Sparring Additional Points ) )

or

Case ( Sparring Active Month = "1"; Sparring Month 1 Total + Sparring Additional Points )

These worked fine if the active month was 1. However when I switched months to Sparring Active Month = "2" the value in Sparring Active Month 1, the year and quarter cleared out and what ever number was in the Sparring Additional Points field became the value for Sparring Month 2, and the new value for the quarter and the year. As I would add more points into the Sparring Additional Points filed month 2 would update as would the quarter and the year, until I change the active month...

So, how do I get the month field to continue to add the points in until a new month is specified after which the new month will begin to add the points in that month? Also when the month is changed I don't want what ever number was left in the Sparring Additional Point filed to be added to the new month.

I tried a seperate filed (Sparring Add) that had this in it:

Evaluate (Case ( Sparring Active Month = "1"; Sparring Month 1 Total + Sparring Additional Points; Sparring Active Month = "2"; Sparring Month 2 Total + Sparring Additional Points; Sparring Active Month = "3"; Sparring Month 3 Total + Sparring Additional Points; Sparring Active Month = "4"; Sparring Month 4 Total + Sparring Additional Points; Sparring Active Month = "5"; Sparring Month 5 Total + Sparring Additional Points; Sparring Active Month = "6"; Sparring Month 6 Total + Sparring Additional Points; Sparring Active Month = "7"; Sparring Month 7 Total + Sparring Additional Points; Sparring Active Month = "8"; Sparring Month 8 Total + Sparring Additional Points; Sparring Active Month = "9"; Sparring Month 9 Total + Sparring Additional Points; Sparring Active Month = "10"; Sparring Month 10 Total + Sparring Additional Points; Sparring Active Month = "11"; Sparring Month 11 Total + Sparring Additional Points; Sparring Active Month = "12"; Sparring Month 12 Total + Sparring Additional Points; ); Sparring Additional Points)

But that did nothing no points into each month, quarter or year. nothing.

Any help would be appriciated!!

James.

[comment]

There are 12 fields for the months labled Sparring Month 1, Sparring Month 2 and so on.

I have stopped reading around here, because that's not a good arrangement - and the rest of it is likely to be much simpler once you fix this.

There is not enough detail about the actual data you are tracking, but seems like each record has a participant (?), a number of points awarded and a date. If that is correct, then all you need to do is calculate the quarter and the year of the given date, then summarize the records by (the calculated) year and quarter.

[isjigsaw]

Thanks for the lightning fast response!

Individual participants, yes, dates no.

These are maintained in our Student Info database where we keep various student data. The tracking of the scores was intended to be a part of that database where we could pull up all the students checked off as participating in a list and then simply add the points into the existing record updating the month, quarter and year (each of which we could sort by). We are not worried about keeping the weekly competition score. At the end of the year all the fields would be "zeroed" out and we'd start over with a new group of participants.

Awards are given to the top three students for each month, each quarter and then the overall top three at the end of the year. The competitions are held most Fridays, depending on holidays and special event schedules. Not every student participates in each competition Friday either therefore some wouldn't be updated each Friday while some compete every Friday...

Using a summary field would require a separate database entry for each time we awarded points correct?

The way "they" used to do it was like this...

Field Name Field Type Formula / Entry Option

Sparring Month Calculation (Number) Indexed, = Sparring Month + Sparring points

Sparring points Number Indexed, Auto-enter: “0”

Sparring Qt Calculation (Number) Indexed, = Sparring points + Sparring Qt

At the end of the month they would zero out the month and then do the same at the end of the quarter for the sparing quarter. This "worked" but I wanted to keep the running total for the entire year broken out by month and quarter for each student. This way we could keep a running track of each student participating throughout the year and print out a list of all the participants with the totals for each month, quarter and year.

[comment]

then simply add the points into the existing record updating the month, quarter and year (each of which we could sort by). We are not worried about keeping the weekly competition score.

It doesn't seem like a good method. When you "add points into the existing record" you [a] must do the addition yourself instead of having the computer do it for you; and there is no trail in case you have made a mistake (and considering [a], you will).

I suggest you create an individual record for each score awarded.

[isjigsaw]

[a] must do the addition yourself instead of having the computer do it for you; and there is no trail in case you have made a mistake (and considering [a], you will).

Why must I do the math? Plus I'm not worried about mistakes really; we don't have that many students participating every Friday, only 15 to 20 on any given Friday and its pretty relaxed all in all. I just want to keep it very simple for the instructors entering in the data; pull up the list of students and add the points to those that get points that day, let FMP do the math.

If I do this:

Month = Month + Points

Quarter = Quarter + Points

Year = Year + Points

it works. Just not nicely... At the end of each month you have to zero out the month then zero out the quarter at the end of the quarter, plus you lose the scores for each month and quarter which while not imperative would be nice to have.. The year just keeps chugging along until the end of the year when you zero it out too.

Why can't I have a field that says we are in January? I'll have FMP figure it out based on the current date. Then the field that updates the month score would look to see what month we are in and then add the points entered into the proper month (in this case Month 1 or January). When the month changes FMP then adds the points into the new month. The quarter and year fields will update themselves every time the month field changes.

[comment]

I am afraid I don't understand what you mean by:

Month = Month + Points

In any case, I believe you should enter each score as a separate (and permanent) record. Then you will be able to easily summarize the entered data in any way you like, using summary fields and sub-summary parts.