Cumulative Limit Concept

[Bedsingar]

Hello,

I'm trying to understand how I could achieve the following goal:

Table 1:
Project Number
Approved Funding

Table 2:
Project Number
Accounting Date
Actual Value

Table 3:
Project Number
Accounting Date
Forecast Spend

Table 4:
Project Number

There will be multiple entries in each table for each project number. What I need to do is create a query (or series of) which will show me where for each project in table 4:

Where Actuals to Date (Table 2) + Future Forecast (Table 3) > Total Approved
Spend (Table 1)

And also which entry in the forecasting table causes the cumulative limit set in Table 1 to be breached and thus infer the date on which this will happen.

It's a bit more complicated than anything I've dealt with before so could
really use some concept help.

Thanks



Josh

[jzwp11]

First of tables 1,2 and 3 look similar enough & contain similar data (of different types) such that I would combine them into 1 table with a field that distinguishes the type of value (actual spend, forecast, approved funding). This would fall under the general database design rule of having like data in 1 table.

tblProjectFinancials
-pkProjFinancialID primary key, autonumber
-fkProjectID foreign key to Table4
-dteFinancial (a date field)
-currFinancialAmt (the amount)
-fkFinancialTypeID foreign key to tblFinancialType

tblFinancialType (holds 3 records: actual spend, forecast, approved funding)
-pkFinancialTypeID primary key, autonumber
-txtFinancialType

Where Actuals to Date (Table 2) + Future Forecast (Table 3) > Total Approved
Spend (Table 1)

I assume that you actually meant to sum the records in each group since you may have multiple records:

Where Sum(Actuals to Date (Table 2)) + Sum(Future Forecast (Table 3)) > Sum(Approved (Table 1))


You can then create an aggregate query that groups the data by project number and financial type & sums the records

query name: qryFinancialTypeTotals
SELECT fkProjectID, fkFinancialTypeID, sum(currFinancialAmt) as SumOfcurrFinancialAmt
FROM tblProjectFinancials
GROUP BY fkProjectNumber, fkFinancialTypeID

Now create 3 separate queries (one for each financial type). Let's assume that data in tblFinancialType looks like this:

pkFinancialTypeID|txtFinancialType
1|Approved funding
2|Actual spend
3|Forecast

query name: qryApprovedFundingSum
SELECT qryFinancialTypeTotals.fkProjectID, qryFinancialTypeTotals.SumOfCurrFinancialAmt as ApprFundSum
FROM qryFinancialTypeTotals
WHERE qryFinancialTypeTotals.fkFinancialTypeID=1

query name: qryActualSpendSum
SELECT qryFinancialTypeTotals.fkProjectID, qryFinancialTypeTotals.SumOfCurrFinancialAmt as ActSpendSum
FROM qryFinancialTypeTotals
WHERE qryFinancialTypeTotals.fkFinancialTypeID=2

query name: qryForecastSum
SELECT qryFinancialTypeTotals.fkProjectID, qryFinancialTypeTotals.SumOfCurrFinancialAmt as ForecastSum
FROM qryFinancialTypeTotals
WHERE qryFinancialTypeTotals.fkFinancialTypeID=3



Now we need a query to bring the sums together via the project number. We will need the project table (your table4) and the 3 queries above. Another thing to consider is that there might be some projects where there has been no actual spending yet & thus no sum (or similarly for either of the other two types as well). To handle that we need to adjust the join type in the final query to include all records from the project table and those in each of the 3 sum queries that match. We also need to test to see if the sum value returned by the query is null. If it is, then we need to substitute the value zero. We will need the IIF() and IsNull() functions to do that. This query will look a little messy, so I decide to create an example database for you; it is attached. This query is named: qryFinancialSummaryByProject

Now to do the evaluation to identify those projects that meet this: Where Actuals to Date (Table 2) + Future Forecast (Table 3) > Total Approved
Spend (Table 1), we need a final query (qryFinal). Please see attached DB.

As some general recommendations, it is not recommended to have spaces or special characters in your table or field names. In the attached database I renamed you table4 to tblProjects and I joined it to the financial table via a the primary-->foreign key fields rather than the project number field since it is generally recommended that the primary key should have no significance to the user; I assumed that your project number has significance to your users.


One last note, if the 3 tables (1,2,3) are really not so similar, you would use a similar approach but you would start by creating queries similar to qryApprovedFundingSum, qryActualSpendSum and qryForecastSum but using your 3 tables instead. The next 2 queries (qryFinancialSummaryByProject, qryFinal) would use essentially the same structure (just different field/query names).

[Bedsingar]

@JZP11,

Thank you for your response, it all makes very good sense and your attached database illustrates your post very well.

This may be the solution I have to go with, but in my original post I may have not made it clear enough that I need to know which month the limit is breached. This is made more complicated by the fact that there would be several transactions for each project in any month.

This may explain (I'll break it down for just one project, but I'd need it to work for all of the projects in the table)

Say Project A's approved budget is £50k as in your example.

Actual spend to date (held in your tblProjectFinancials marked with Financial Type 2) = £10k

Then the Type 3 transactions (forecast) are as follows:

1st Jan £5k
1st Feb £5K
3rd Feb £2.5K
1st Mar £2.5k
1st Apr £10k
8th Apr £5k
1st May £10k
2nd June £1k
3rd June £9k
and so on...

Therefore your current qryFinal would tell me that I project A was forecasting a total of 50k - (10+5+5+2.5+2.5+10+5+10+1+9...) over spent (= £10k)

However the point at which it goes 0.01p overspent is on the 2nd June, and whilst I don't need to be day specific I would like to highlight which month the transactions exceed the approved total.

I would presumably be able to use a 'group by' to sum the transactions by month, but my question as to how I'd run a cumulative total and identify which month causes the breach remains unsolved. I think this is more difficult when you start trying to apply it to a large number of projects rather than the simple single example above.

let me know if any of that doesn't make sense.

Thanks

[jzwp11]

I've reworked a few things to include month and year. To get cumulative sums, subqueries work best. You will want to take a look at qryCumulativeSummary for the summary. I added a field that identifies when the approved amount is exceeded. Once the amount is exceeded all following are also flagged, so if you just want the first record that is flagged for each project, that will require another query. I am running out of time for the day, so I did not have a chance to work on that aspect.

[Bedsingar]

Perfect Thank you so much!

I think with these things its just getting your head around the concept of how.. so you've done the hard bit.

Cheers

Josh

[jzwp11]

I went ahead and finished up the last item to pull the project and the month ending date when it first goes over the cumulative approved fund value. It took 2 additional queries; see qryFinalProjectListWithFirstOverPeriod for the final output.