Total by Year ?

[Totes]

Hi,

Hate to bother everyone with this (more than likely simple question)…

I am plodding along pretty well, with my books and this forum, however I have one I just cant seem to make work.

I have a date field, and I have a total hours field (the two fields go together in the same layout).

I have a field which I want to show the total hours for all the fields which have a date during 2006.or 2007 or 2008 etc.

Example

Date Field

xx/xx2006

xx/xx/2006

there is an hour field adjacent to the date.

Hour Field

22

31

Then a total hours by year

Total for 2006: This is what I cant make add up

I want to have a field that will show total hours for 2006 as 34 (or whaterver the total should be for the years selected) even if there is a 2007 date between them ( I just want a total for the years I need)

Any suggestions.

Thanks,

Jim

Edited July 10, 2006 by Guest

[Breezer]

The easiest way is to use a summary field for the Hours then perform a find on the date range for the year field, such as, 1/1/2006...12/31/2006.

(In FM8, you can simply type */*/2006 instead of the old date range)

Your summary field will then show you the total hours for the year 2006 or for wahtever year you perorm the date range for.

Create a new field, "sum_Hours" and define it as summary, and for the options select "Total of Hours"

You can also script this process if your users will be doing this processing often.

Edited July 11, 2006 by Guest

[Totes]

Thanks for the help.

I think what I am attempting to do (if at all possible) is keep a running total without having to do a find type.

The fields are in a portal, so...what I need is a field that keeps a total for the "Total Hours" field, as the hours are entered.

Does that make sense? I know that more than likely I have not explained it very well...sorry about that.

If I enter date in 2006 and 33 hours I need the total hours field to show 33, then if I add another 2006 date in the same portal table, with 10 hours I need the "Total Hours" field to now show 43.

I dont even know if this can be done, with my limited knowledge of calculations etc. I have beat my head on the desk attempting this for 3 days before I posted.

I think Im looking for Sum (Hours) if the date is 2007....am I headed in the right direction?

Thanks again for any advice.

Jim

Edited July 11, 2006 by Guest

[Breezer]

It is much better if you are using a portal. You are definitely in the right direction.

For the Sum(Hours), change it to

Sum (TO_Name::Hours)

where "TO_Name" is the name of your table occurence that defines your relationship for the portal and it's usually displayed on your portal while in layout mode.

Edited July 11, 2006 by Guest

[Totes]

Breezer,

Thanks for that bit...I got it in place and it works.

Now what I want to do is to add to that, so that the return sums are based on the dates listed.

So (dont laugh as this is my first calculation attemp)this is what Im poking around with.

"If (Training_2007::Training_Date = Date (*; * ; 2007 ); Get Sum(Training_2007::Training_Course_Hours)"

It will not provide me with the totals for all hours within the 2007 range I get the "?" in the field I want the totals to show up in.

What am I missing.

Thanks again,

Jim

Edited July 11, 2006 by Guest

[Breezer]

Could you explain exactly why you want to do this? It seems, IMHO, you need to implement this using global field(s). The global field would hold the year (variable) of which you are interested in without hard coding it into your programming.

BTW, do you already have hours for year 2007 in your database?

[Totes]

It is basically showing that a person took a training class on a certain date (2/5/2006) and then there are class hours associated with the class they took Course_Hours

The fields are laid out as:

Training_Date:

Course_Title:

Course_Description:

Course_Hours:

And of course each student has their own running data for each course they take, which could be 20 courses in a given year.

There are currently over 200 students in the DB.

The instructor has to keep up with how many hours each person has taken in any given calendar year, as the student is required to have at least 40 hours.

So, by having a running total field based on the “Training_Date” and the “Course_Hours”, the instructor can at a glance just look at the running total field which I have broken down into years (2007 / 2008 /2009 etc.) to give the student their current training hours for any given year. Or the instructor can look back at a glance at any past years to get an hour total for the given student.

A student could have course hours running over a 10-year period (or longer depending on how long they are employed).

No, I do not have hours for 2007, that was just the first year I had in place, due to the fact that it will more than likely be 2007 before I finish this project.

Thanks again,

Jim

Edited July 12, 2006 by Guest

[Breezer]

I don't know how you have set-up your relationships, but based on what you are teeling me, I would add a new calculation field in the courses table for Course_Year and use it for the relationship to get the sum of hours for a particular year.

Course_Year = Year (Training_Date)

I would then create a new table to hold the years and use this field "Course_Year" as the key field.

I can set-up a sample database if you'd like to use this approach.

[Totes]

Breezer,

I am open to any suggestions right now. So if you have the time to spend on a newbie...I would be grateful for any sample you could provide for me to learn by.

Thanks,

Jim

[Breezer]

Jim, here's a quick sample. It will give you an idea of what I'm thinking.

SampleTotalYear.zip

[Totes]

Wow!

Looks like a winner to me, I will have to take a few hours to disect it to understand how it works but it will do what I need.

Thanks for taking the time to help.

Jim

[comment]

How about something simple?

SampleTotalYear.fp7.zip