Forum Discussion
Solved
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 w...
- 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.
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.
- 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
