Forecast
Hello experts,
I want to develop a query that will forecast the value of an asset.
There is an initial value and an amendment on a certain date in the future, and an expiration date
The query would need to show the initial value on the [DateOfIssue] and display that value for each month to the [DateAmended] then show that amended value until the [DateOfExpirey]. Displaying the values for each month is what is difficult for me. I can develop a query to show the values on the [DateAmended] but I need to display the values for each month until the [DateOfExpirey].
Example:
Amount: 100
DateOfIssue: 12/1/2021
DateAmended: 5/1/2022 (this is the first amendment)
AmendAmt: -50
DateAmended: 7/1/2022 (this is the second amendment)
AmendAmt: -25
DateOfExpirey: 8/1/2022
the forecast would need to look like this (not the text though):
| Date | 12/1/2021 | 1/1/2022 | 2/1/2022 | 3/1/2022 | 4/1/2022 | 5/1/2022 | 6/1/2022 | 7/1/2022 | 8/1/2022 |
| Amount | 100 | 100 | 100 | 100 | 100 | 50 | 50 | 25 | 25 |
| reduced 50 | reduced 25 | expirey date |
Please see link to the simple db with only 2 tables and sample data.
https://drive.google.com/file/d/11XXfCYbgkUBO5fQkO2aXQtNArm6cA7oV/view?usp=sharing
I am still having issues uploading to this site.
Grateful for your help.
Let me know if it is not clear.
- the query 1_qryTotalAmends is a Total query.
it is just summing the LC by Date.
there are 2 amendments on date jan-03-2022, (50 and -25).
so summing them will be 25.
also this only first step (as prefixed by 1_), the final query is numbered 4_.
if you can see each query, i am building the final, one query over the other.
