SOLVED

Forecast

Super Contributor

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): 

Date12/1/20211/1/20222/1/20223/1/20224/1/20225/1/20226/1/20227/1/20228/1/2022
Amount10010010010010050502525
      reduced 50reduced 25expirey 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. 

 

13 Replies

@Tony2021 

 

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).

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.
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!
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.
Arnel, I went to the crosstab query and put a criteria of 1<>3 and it shows all 3 records instead of only 1 record from the form's combo box. What I am looking for is to "fill in the spaces" that are seen in the cross tab. It seems to be a pretty close solution if it wasn't for the null values between the dates in the crosstab. I am not sure if this answers your question but please let me know. thank you

@Tony2021 

i hope this is what you meant.

see the New rowsource of the combobox.

also see the code behind the command button.

@arnel_gp 

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. 

 

Tony2021_0-1639018828616.png

 

like i said before, each LC has different start, end date.
let take 111 for example, it started on dec-3-2021.
you don't want to fill the oct-5 or nov-1, the LC does not exists (yet) on those dates.
otherwise your boss (if you are not) or some auditor will catch this. The LC has not
been created, yet there is a retro-forecast?

@arnel_gp 

 

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.

@Tony2021 

 

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.

@arnel_gp

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

Tony2021_0-1639764586911.png

 


 

best response confirmed by Tony2021 (Super Contributor)
Solution
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.
I see what you are saying now. Ok. thank you so much. Very grateful for your help. that code is amazing.