calc totals between two dates

[summergirl]

Ooh, this is a stumper. I hope someone can help!

Here are the fields we're dealing with:

LastAssessDate = calculated date

CurrentDate = calculated, using Status(CurrentDate), not Today function

MayHrs = number entered in a related file (represents the month of May)

JunHrs

JulHrs, etc. for entire calendar year

How do I make a calculation that figures out the sum of monthly hours between the LastAssessDate and Current Date?

Example:

LastAssessdate = Feb 1,2004

CurrentDate = March 30, 2004

JanHrs = 10

FebHrs = 24

MarHrs = 6

The answer I'm looking for is 30. I just don't know how to get there with a calculation. I had the start of a field definition, but it was 10 miles long and didn't produce the correct result. Should I be trying to create a single massive calculation?

This is one of those logic problems that makes my hair gray(er)! Any assistance is welcome.

Thanks,

Mary Z

Version: v5.x

Platform: Windows XP

[CyborgSam]

Here's one way to do this using Sum:

First, make sure you have a date field that identifies the JanHrs as being January of some year.

In the related file, make a boolean calculation that checks to see if the date field is between the Last & Current dates. I assume you can easily make a reverse relationship from the related file to the main, if not, a script could set globals in the related file.

In the main file, define a global number "True" and set it to 1. Make a relationship between "True" and the boolean calculation, I'll call it Foo. Define a calculation field in main "TotalHours" with Sum(Foo: JanHrs) + Sum(Foo:FebHrs)...

The boolean calculation ensures that the record is in the date range, making it OK to add in the Sum calc.

BTW: why do you have separate fields for each month's hours? Could that DB just have a date field for the month and the hours field?

[summergirl]

CyborgSam said:

BTW: why do you have separate fields for each month's hours? Could that DB just have a date field for the month and the hours field?

My database keeps track of student information. The parent file keeps track of demographics, etc. The related file tracks how many monthly hours a student attends each of several class sites. From a parent file portal, users enter the January student attendance hours in the JanHrs number field, Feb. student hours in the FebHrs number field, etc., for each class site in which the student is enrolled.

First, make sure you have a date field that identifies the JanHrs as being January of some year.

I think your solution will work perfectly, but only if you can tell me how to change my JanHrs number field to a date field - or create a calculation that will do that for me.

I think I have two problems here. First, I'm trying to compare numbers and dates. Second, I have just enough FM knowledge to be a danger to myself and others! Thanks for your patience.

Let me know if I need to provide more details.

Mary Z

Version: v5.x

Platform: Windows XP

[summergirl]

I have it under control now. I thought I had to change the hours number field to a date field. Instead, I created a new field for each month in the child file:

Jan2004 = Date(1, Day(Status(CurrentDate), 2004).

Then I created another new field:

FlagJanDates = If (Jan2004>=LastAssessDate and Jan2004<=Status(CurrentDate),1,0)

In the main file I created a single global field as you suggested:

GDateMatch, value set to 1

I set up a relationship for each month in the main file:

FooJan = matching data between GDateMatch and FlagJanDates

Finally, in the main file, I created a single TotalHrs calc field.

Voila! It works! But you knew all along that it would, didn't you...

Thanks for a big shove in the right direction. I would NEVER have figured it out without your help!

[CyborgSam]

Mary->

Great! I suggest making it a bit more flexible by eliminating the hard-coded today. For eample: what if you need to pick up stats from the past to recheck something. Also, I'd put a year in with each record, otherwise you'll need a new database file next year.

So Jan's calc could be Date(1, 1, theYear) where the year is set from the school year.

Perhaps editable StartDate and EndDate would be better, and the default would be LastAssessDate and today. This lets you do out of time calculations (you'll probably have to go back to check some bad data entry at least once).

So JFlagJanDates would be If (Jan2004>= StartDate and Jan2004<=EndDate,1,0)

Also, the If test is redundant since all elements in the test are boolean, you can shorten the calc to:

(Jan2004 >= StartDate) and (Jan2004 <= EndDate)

[summergirl]

Hey, CyborgSam, I never thought about the potential for needing an end date other than the current system date. In my situation we have separate db for each year, and there IS sometimes a need to recheck info from previous years. (Are you some kind of psychic or what?!)

I also didn't know I could dispense with the If statement. That's two new things I learned today. I think I should call it all good and go home for the day...

Thanks again for sharing your braincells.

Mary Z : )

[CyborgSam]

Mary->

> (Are you some kind of psychic or what?!)

You made a typo, it's spelled psycho.

Glad I could help,

Sam

Version: v7.x

Platform: Mac OS X Panther