Conditional Sum of Numbers

[life036]

Hello,

I am trying to figure out how to display what seems to be something simple. I have a database that tracks the hours that volunteers work for our organization, and I'm trying to make a calculation field that displays the total amount of hours that a volunteer has worked in that particular year.

I can get it to sum up the hours for each individual volunteer easily enough, but I have no idea how to only sum up the hours for one particular year. Can someone kindly help point me in the right direction?

Thanks

[Vaughan]

One way could be to create Year field that is calculation Year( date of work ) then create a summary report using Year as the break field.

[life036]

One way could be to create Year field that is calculation Year( date of work ) then create a summary report using Year as the break field.

Just tried that; can't seem to get it to work. I must be doing it wrong. The calculation needs to be based on the year as well as a matching volunteer ID. The method you suggested is only producing one month's hours...

Here's how the current database works: I have a separate table for each year's hours, and then I am able to get a sum for each volunteer for that year. The trouble is, I have to go back in every year and create another separate table for that year's hours so that they sum up correctly. It's completely stupid, and I know there's got to be a better way. Here is how it looks now so you can see exactly what I'm trying to do. The yearly hour totals are circled in red:

Will a sub-summary even be able to show up directly on that layout in browse mode, or will I have to send the user to a separate layout in preview mode to correctly display the sub-summary that you are recommending?

Thanks

[Vaughan]

Summary reports display in Browse mode for FMP 10 and later.

I'm not sure what your database structure is, but you should have a table for Volunteers and another for Hours, related by a VounteerID field. There is no need to create a new table each year, in fact that is particularly bad design.

To display the hours for a particular year you'd need a global number field (to hold the selected year) and another relationship between Volunteers and Hours that uses the VolunteerID and YearGlobal fields in Volunteers and the VolunteerID and Year fields in Hours. Base the Sum() calculation on this relationship.

[life036]

Summary reports display in Browse mode for FMP 10 and later.

I'm not sure what your database structure is, but you should have a table for Volunteers and another for Hours, related by a VounteerID field. There is no need to create a new table each year, in fact that is particularly bad design.

Hi Vaughan, Thanks. I understand that it is a completely stupid design, which is why I started this thread to just have two separate tables. I simply haven't had the time, energy or know-how to redesign it correctly yet.

To display the hours for a particular year you'd need a global number field (to hold the selected year) and another relationship between Volunteers and Hours that uses the VolunteerID and YearGlobal fields in Volunteers and the VolunteerID and Year fields in Hours. Base the Sum() calculation on this relationship.

What do you mean by the "selected" year? I'd like to display a sum for each and every year at the same time, without the need for the user to "select" anything (just like how it currently displays in the picture above). Is this possible with the solution that you mentioned?

Thanks

[Vaughan]

I have a separate table for each year's hours

I'd like to display a sum for each and every year at the same time...

How can you do that if each year is in a separate table?

[comment]

I'd like to display a sum for each and every year at the same time, without the need for the user to "select" anything (just like how it currently displays in the picture above). Is this possible with the solution that you mentioned?

If you want to display this directly in the volunteer's layout, you will need to add a table of Years. See a more detailed explanation here:

http://fmforums.com/forum/topic/68409-3-tables-how-to-filter/page__p__324452#entry324452

This is, of course, after you have merged the separate Hours tables.

[life036]

How can you do that if each year is in a separate table?

I'm already doing it quite simply: Each of those fields you see circled in red is just a simple calculation field, defined in the main volunteer table. For instance, the 2007 field is just a calculation defined as:

Sum(Volunteer_Hours_2007::Hours)

I'd like to combine all of the yearly hours tables into one table, and redefine these calculation fields to only display a sum of hours for each specific year.