Forum Discussion
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.
13 Replies
crosstab query will fit for your requirement.
but there is a catch since start date and end date of the LC is not same
from record to record, you can only show one LC at a time.
i made additional tables for months (zzTblMonths) and for years (zzTblYears).
we need these tables to create a cartesian query (cross-join query).
i made some queries and numbered them (sequence when i made them).
then i made a form with subform. the subform's sourceObject would be 4_qryForecastCrosstab.
the Criteria of this crosstab is changed via VBA (see the code of the button).
- Good solution. I was working on a cross-tab solution with talley tables for years and months and came back to verify the data. And here is your solution already!
- Hi Arnel, that is an ingenious solution. I understand only 1 LC can be displayed since the start and end dates do not match between the LC's.
Do you happen to know of a hack to consolidate them?
I can close out this question and ask another if advisable.
thank you very much.- post a table (same on the first post), of your consolidation result based on your 3 LC sample records. if it is doable, why not.
