summary report

[okachick]

Hello,

I am trying to find a way to grab the information from two fields and calculate only those begining and ending fields from a summary report.

Example: The report is based on the following fields; Date field, Unit#, Kilometres start and kilometres finish. My search criteria is based on january 01 to December 31 for each of the unit#'s (there are 250 units)and sorted by Unit#. I want only the Kilometres start at the begining of the report and only the Kilometres ending, last record in the report for the specific unit#. Now I need to calculate it so it shows me the total mileage for the Unit# used but I don't want all the mileage calculated for the remaining of the year, only the first one shown and the last one shown. I would appreciate any help on this.

Thank you :

[RalphL]

You might be able to use the Summary Functions Minimum & Maximum. Minimum of start & Maximum of Finish. You will need to use the GetSummary function to calculate the difference.

[okachick]

Great! That's what I did and it worked. Now, I need to create a Trailing summary but when I try to create a field for that using the Getsum (max and min) fields (because they are already a summary field, it doesn't show me the proper calculation. Obviously I am doing something wrong here hahaha.

Please help

[RalphL]

Need more detail. I can't follow what you are doing. What is the result and what is wrong?

[comment]

Is it possible to have a discrepancy between the total of individual mileages and the total mileage of a unit? IOW, do you have any "lost mileage" in-between records?

If not, then you could simply define a calculation field cMileage =

Kilometres Finish - Kilometres Start

and use a summary field defined as Total of cMileage for all your summaries.

If yes, then it gets rather complicated.

[okachick]

Yes there are quite a few records with mileage that was either never entered or entered incorrectly so that was the reason for the first and last which I do know to be correct. What I ended up doing was exporting those fields to excel and doing a calculation to get the summary of them from there until I can find a solution to get the grand trailing summary of the other summary fields. If you have any suggestions I would greatly appreciate it.

[comment]

Is this really for version 6?

[okachick]

Unfortunately yes, I am using filemaker 6 and NOT by my choice. I actually gave my boss my own fmp6 because he only had fmp5 and I was not designing a db on that and refuse to "give" the company my fmp7 or 8 that I have. I have only now convinced them to buy a secondhand copy to at least get to 7 or 8 which has been a constant battle and also to get a server version as well. I can't even begin to tell you the nightmare I have importing/exporting from a copy into the main db because other people are using it one at a time to enter their work. : Sometimes employers just don't have a clue.

[comment]

OK, then. I suggest you define the following fields:

sMinStart = Summary, Minimum of Kilometres Start

sMaxEnd = Summary, Maximum of Kilometres Finish

sCount = Summary, Count of [any field that cannot be empty, e.g. serial ID or Unit#]

cPer =

( GetSummary ( sMaxEnd, Unit# ) - GetSummary ( sMinStart, Unit# ) ) / GetSummary ( sCount, Unit# )

sTotalMileage = Summary, Total of cPer

This last one, sTotalMileage, should return the correct results both in the sub-summary by Unit and in the grand total parts. I haven't tested this in version 6, but I cannot think of a reason why it wouldn't work there just as well.

[okachick]

The Min and Max work but not the trailing summary. I must be doing something wrong or perhaps it is because it is in FMP 6?

[comment]

Does this one work for you?

Trips.fp5.zip

[okachick]

oooooo thank you. I will try that at work tomorrow and let you know how it goes.