Greetings experts!
I need 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].
I thought crosstab query would work however it only shows a value on the date of the amendment and all other dates its shows a null.
Please see link to the simple db with only 2 tables and sample data.
Forecast_LC.accdb
Grateful for your help.
Let me know if it is not clear.
Example:
Initial 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
Ending Value is 25 (see table below last column to right)
Using the above data, the forecast would need to look like this (not the text though):
I need 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].
I thought crosstab query would work however it only shows a value on the date of the amendment and all other dates its shows a null.
Please see link to the simple db with only 2 tables and sample data.
Forecast_LC.accdb
Grateful for your help.
Let me know if it is not clear.
Example:
Initial 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
Ending Value is 25 (see table below last column to right)
Using the above data, the forecast would need to look like this (not the text though):
