Parsing contract data overlapping fiscal years

[pncbiz]

I'm building a database to track budgets, contracts, orders, and invoices. Right now, I'm focused on the budget and contract portion. Both are built (tables, forms) and operating indepedently; now I need to start building the ties between them.

I want the budget entry form to automatically calculate what the projected obligation will be based on the contracts in the database, and display that as an "Obligated" number. I can use DSUM for that (already have it, actually), but I want to be able to only present the amounts relevant to this fiscal year, since contracts dates and fiscal years rarely align.

Example: FY21 starts 1 Jul 20, ends 30 Jun 21. Contract A has a start date of 1 Oct 20, end date of 30 Sep 21, and a total amount of $12,000. In the budget entry form, for that cost center, I want the form to show $9,000 in Obligated funds (so the user knows they have to budget AT LEAST $9,000), because the contract only has 9 months in FY21

Another example: FY21 starts 1 Jul 20, ends 30 Jun 21. Contract A has a start date of 1 Apr 20, end date of 31 Mar 21, and a total amount of $12,000. In the budget entry form, for that cost center, I want the form to show $9,000 in Obligated funds (so the user knows they have to budget AT LEAST $9,000), because the contract overlaps into FY21 by 9 months.

I have built a FY table, with FYID (two digit fiscal year), FYSTART (date), and FYEND (date) to provide the reference information. What's the best approach to parsing that contract data so I only calculate the relevant amounts for the desired fiscal year?



Appreciate whatever ideas you can offer.

[June7]

A budget record for each year with reference to the contract record. Either enter the budget amount as dollars or indicate percentage of contract to apply and calculate the dollars.

[pncbiz]

Thanks for taking time to reply. I'd rather have the database do the math. I have a budget record for each FY (by department and cost code), but would like to have Access calculate how much to allocate against that record for that contract. Is that in the too-hard category?

[June7]

You have to provide it some parameters to base calc on. So maybe indicate date range in budget record and code calculates percentage based on overall date range in contract and then uses that percentage to calculate $. Sounds complicated. Calculating elapsed period can be tricky. Months don't have same number of days. How do you want to deal with ranges that start/end mid-month? The budget calcs could end up with values that when summed are greater/less than contract amount.

[pncbiz]

I like that idea! Turn the date overlap into a percentage (based on days), then apply that against the overall amount.
I agree - elapsed time is tough because of the reasons you state. But the budget doesn't have to be within the penny (just the dollar!) so a percentage method should be good enough. That would also address the issue of start/end mid-month.
Appreciate the help.