Average Age Calculation on a Vehicle Fleet

[Snapperhead]

Hi

 

I'm a total newb to FMP, & have been looking for a few ideas on how to implement some calculations on a database.

 

I'll confess these types of calculations are way beyond my capabilites.

 

1. I need to perform an Average Age Calculation over a number vehicles.

 

2. I need to ensure vehicles remain under a given age. (say 20 yrs) - within a contract period.

 

Amongst other things, I currently have the following fields in each record.(lets say there are 4 records)

 

"date_first_registered"

"Age_in_years" (calc)

"contract_commence_date"

"Contract_finish_date"

 

 

In this example

 

Vehicle 1 is 9 yrs

2 is 11 yrs

3 is 18 yrs

4 is 2 Yrs

 

Contract commenced 1/1/2007 - finishes 1/1/2012

 

I'm trying to achieve 2 things

 

1. An average age of fleet (as of this year or today)

 

2. A report (or somthing) that gives lists the average age of vehicles for each year within the contract dates. 2007,2008......2011,2012.

 

I hope i've included the right info.

 

Thanks

 

 

BTW If I don't respond....my head already exploded,trying to figure this out.

[LingoJango]

If you don't need to use it in other calcs, for average age you can create a summary field defined as the average of your age field.

 

The second part is trickier as you need a year-by-year listing - presumably one for each contract?

 

I'll give it some thought. Probably some kind of scripted report that loops through the contract years to produce records in a special report table (i.e. one that does not hold underlying data, where records are all deleted every time a report is created). Maybe this idea will set you on the right path.

 

On the other hand if you don't really need a listing but a calc that says OK or not OK for a given contract (assuming you have a Contracts table), you could probably use a script and a relationship to go through each contract's years and average the age of the vehicles, give you an OK if it's fine and point out which years are not ok if it isn't.

 

Or maybe someone else will have a better idea.

[Snapperhead]

If you don't need to use it in other calcs, for average age you can create a summary field defined as the average of your age field.

QUOTE]

 

Part one resovled.

 

Thanks so much, I do feel a bit silly I couldn't work that one out.

 

I'm working on a solution to the reporting ( the second part)

 

I'm playing around with an excel worksheet, I have the fleet average over the contract period done, with annual averages & and overall average, now to try an transpose the information into the existing database.

 

Thanks for setting me on the right track, I've been working on this for so long now, I'm practically brain dead.

 

[LingoJango]

Hey, snapperhead, just replied to you in another thread.

 

It would be a good idea to tell us about the overall database structure - how many tables do you have? What are they about and what kind of fields, other than the ones you've listed in this thread? How are they currently related?

 

I suggest reading the White Paper for Novices to see how these things can work. Re-read it if you've already done so.

 

The main point is that you should not hard-wire what is flexible. I have a nagging suspicion, based on your other thread, that you're running separate calcs for 32 vehicles or 32 contracts - not a good idea.