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 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!
