Access 2002: Fiscal year calculations

[noclueforid]

Hello. I am creating a database to track contracts, their cost, and project future fiscal costs of those contracts. Basically the operator enters the contract start date, end date, and total cost of the contract. What I need this data to produce is 1. Calculate an average cost per month of the contract duration. 2. By the dates entered, calculate which fiscal years are covered. 3. Track each fiscal years total cost.

I am having issues with calculating the data for #2. I am not new to Access, but have never had to do a calculation such as this, and can't seem to come up with any solution. Any suggestions on how to generate that data automatically?

[Jim Doherty]

Hello. I am creating a database to track contracts, their cost, and project future fiscal costs of those contracts. Basically the operator enters the contract start date, end date, and total cost of the contract. What I need this data to produce is 1. Calculate an average cost per month of the contract duration. 2. By the dates entered, calculate which fiscal years are covered. 3. Track each fiscal years total cost.

I am having issues with calculating the data for #2. I am not new to Access, but have never had to do a calculation such as this, and can't seem to come up with any solution. Any suggestions on how to generate that data automatically?

Whatever your fiscal year is then you add the appropriate number on months to it offset against a control date (ie your contract date or it might be something else). You then return the 'year' portion or that date value you can embed this is a query obviously replacing the 'yourfieldname' section with your own control date and altering the numeric section in this instance '6' to suit yourself.

year(dateadd("m ",6,yourcontrol date)) as fiscalyear

Now obviously the above is a calculated value and as such you would want to consider querying your data based on this column value itself, fine logically speaking, but consider the speed overhead of querying against a calculated value for each row of a query recordset. The calculated value is not indexed... so over very large datasets it will be slow.

You might therefore, depending on your data row size, decide it a suitable candidate to be table stored (ie create a new indexed field in your table) and update this field with a static value in the afterupdate event of some form control or other. A numeric indexed field will be very fast query wise.

You can then include this column in any query you might raise as part of your dataset. This goes against the general thrust of 'one should not store calculated values' but a fiscal year is a fiscal year and always remains so statically. Provided you dont change the control date in the table, or if you do, that you have some control mechanism in place for updating this 'static' fiscal year value in the table it doesnt really matter. You have the control over your data.