Need to total nubers from layout by date range?

[Totes]

Hello,

Here is what I am sure is another simple newbie question, but I just cant figure it out.

I have a layout with a date field and about 25 fields each one containing a number.

EX:

Date: 01/13/2007

Field 1: 2

Field 2: 55

Field 3: 8

Etc.

When a new record is created the date field is automatically entered.

So now I have a date field and 25 fields containing numbers.

What I am attempting to do (without success) is to be able to run total for each field for a certain date range.

I may need to run a report for 11/01/2006 to 01/11/2007 for example and I need to see the totals for each of the 25 number fields for that date range.

Am I looking for a date range summary? I did a search for date range summary but could not find an answer to my question.

Could someone point me in the right direction one more time.

Thanks,

James

Edited January 13, 2007 by Guest

[CobaltSky]

What I am attempting to do (without success) is to be able to run total for each field for a certain date range.

Hello James,

There are a number of ways to achieve this.

One would be to create a set of summary fields. To do this, go into Define Database, selet the field type called "Summary" and create a new field. In the resulting dialog, choose the option for a total of one of your 25 fields.

These fields will return a total of the field in question for all records in the found set, so to get totals for a given date range, you simply need to perform a find for records in that range. The summary fields will then calculate the results you are looking for.

To perform a find for a date range, enter find mode, then in the date field type the start and stop date, with two or three period characters in between, thus:

1/1/2007..1/6/2007

- then perform the find and if any records have dates in the specified range, they will be located.

Once you have set up your 25 summary fields, you will probably want to create a special layout which has your summary fields on it and, if you're feeling adventurous, you might also wish to create a script which runs reports automatically for you. :wink2:

[Raybaudi]

Hi

you need:

1) 2 global date fields ( gStartDate and gEndDate

2) a multipredicate self-relationship

gStartDate ≤ date

gEndDate ≥ date

3) 25 calculation fields each holding a calc such this ( right for field1 )

Sum ( MyTable 2 :: field1 )

BTW: try the example attached

MyTable.zip

[Totes]

Again I want to thank everyone for helping a newbie out.

I was not even close in my attempts to make this work so I could have been at the desk for years attempting to figure this one out.

Again thanks for the help, Ill attempt to put it in action later tonight.

Keep your fingers crossed.

James

[comment]

Instead of 25 summary/calculation fields, you should consider moving all those values into a related table, where each value will be a single record. Then you can summarize them with a single summary field, with a sub-summary by type.