Dec 04 2021 04:34 PM
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.
Dec 04 2021 10:15 PM
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).
Dec 05 2021 05:23 AM
Dec 05 2021 02:37 PM
Dec 05 2021 10:01 PM
Dec 07 2021 04:29 AM
Dec 07 2021 07:09 PM
i hope this is what you meant.
see the New rowsource of the combobox.
also see the code behind the command button.
Dec 08 2021 07:01 PM
Hi Arnel,
thank you. Its really close but I only ask for a tweak if possible.
You can see the null values in the below pic.
How do you suggest the values would show instead of being null?
I hope I am makin sense. Let me know if you have questions.
Dec 08 2021 08:10 PM
Dec 15 2021 05:41 AM
Hi Arnel, yes that is true that the LC did not exist yet. I would only be interested in having the data populated after it is issued and prior to this a null is OK. sorry for my tardy reply. Interested in knowing how you can solve this. I hope it is clear. Let me know if you have any questions.
Dec 16 2021 04:10 AM
i made a temporary table ZZForecastAll.
i fill this table using Module1.fncUpdateZZForeCast.
then i made another Crosstab against this table.
notice that there maybe null at the beginning (LC not yet created) or at the end (LC end Date).
see the code and the Form.
Dec 17 2021 10:11 AM - edited Dec 17 2021 10:12 AM
Arnel, thats amazing. I wish I could do that.
Question:
The first amendment for LetterOfCreditID = 1 is negative and I think it shows positive below? The next amendment is -250 and it shows as a -250
Dec 17 2021 09:56 PM
SolutionDec 19 2021 06:24 PM
Dec 17 2021 09:56 PM
Solution