Need current and previous data for final report

[foshizzle]

First off, apologies as this is my 3rd attempt at getting this report right. Please allow me to start fresh here:

I now have the following queries:
qryRMonthlySum2_DailyBal - uses data from qryRMonthlySum3_Compare to obtain daily transaction balance (DailyBal) for each airline. The DailyBal is used to formulate expressions
qryRMonthlySum3_Compare - used to get previous day
qryRMonthlySum4_GainLoss - pulls gain/loss value from table
qryRMonthlySum5_Summary - calculate's current Actual Balance as follows:

SELECT qryRMonthlySum2_DailyBal.TransactionDate, qryRMonthlySum2_DailyBal.AirlineCompanyLookup, qryRMonthlySum2_DailyBal.DailyBal, qryRMonthlySum2_DailyBal.GallonsRcvd, qryRMonthlySum2_DailyBal.GallonsIssd, qryRMonthlySum2_DailyBal.Total, qryRMonthlySum4_GainLoss.AirGainLossNz, [B]Nz(([Total]+[AirGainLossNZ]),0) AS ActualBal[/B] FROM qryRMonthlySum2_DailyBal LEFT JOIN qryRMonthlySum4_GainLoss ON (qryRMonthlySum2_DailyBal.TransactionDate = qryRMonthlySum4_GainLoss.MaxOfTransactionDate) AND (qryRMonthlySum2_DailyBal.AirlineCompanyLookup = qryRMonthlySum4_GainLoss.AirlineCompanyLookup) GROUP BY qryRMonthlySum2_DailyBal.TransactionDate, qryRMonthlySum2_DailyBal.AirlineCompanyLookup, qryRMonthlySum2_DailyBal.DailyBal, qryRMonthlySum2_DailyBal.GallonsRcvd, qryRMonthlySum2_DailyBal.GallonsIssd, qryRMonthlySum2_DailyBal.Total, qryRMonthlySum4_GainLoss.AirGainLossNz ORDER BY qryRMonthlySum2_DailyBal.TransactionDate DESC , qryRMonthlySum2_DailyBal.AirlineCompanyLookup;

The final product will require the user to enter an ending date inside a form, typically the last day of the month.
This query/report will display all columns from qryRMonthlySum5_Summary where qryRMonthlySum2_DailyBal.TransactionDate is the end date entered in the form. However, The DailyBal in the final query/report will need to be the previous month's ActualBal. (The DailyBal of each month will be the previous month's ActualBal)

How do I go about making this happen?

 

[June7]

Seems you are already familiar with query to pull value from another record http://allenbrowne.com/subquery-01.html#AnotherRecord

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[foshizzle]

Thank you for your response. I just cant figure out how to get both previous values for two different dates in the same query. Attached is a sample, following the instructions in your post. I've been using dates 4/30/21 and 5/1/21 for testing.
Thank you

 

[June7]

I am not understanding "get both previous values for two different dates". Perhaps you should show sample of desired output. You can use post editing tools to create a table in post or do a mockup in Excel and post that file or an image.

 

[foshizzle]

Here is a sample:
The top query was run for 4/30/21.
The bottom query was run for 5/1/21. (We don't yet have 5/31 data since its still May).

If I were to run a query for 5/1 (or 5/31), the DailyBal should be the end of the previous month's (4/30/21) ActualBal. Note, this new value has nothing to do with the remainder of the calculations for that row since its a separate query. The original DailyBal for the current month will not be used in the current query.

 

[June7]

I am still confused. Is the 4/30/2021 query correct? Or should DailyBal be 0?

Need to subtract April AirGainLoss from DailyBal on second query?

Sometimes can't get all aggregate calcs accomplished in query. Often report Sorting & Grouping with aggregate calcs in textboxes are the way to go. Textbox on report has RunningSum property which can be quite useful. Sometimes even domain aggregate functions are needed. You did not provide even a skeleton of report to work with. Again, suggest a mockup in Excel to show the design you want to achieve.

 

[foshizzle]

Hi - I didnt provide the report in the original sample because its the same data in the query. However, I've attached the report to this new sample DB for your reference here.

I see your confusion because Its the same problem I had when trying to figure this out.. In summary, this is a completely rebuilt database and there was no previous data prior to 3/31/21. In regards to this, the DailyBal column for the 4/30/21 query will be slightly off in that there was no real previous ActualBal column to pull the previous month history from.

The Gain and Loss is actually calculated from a combination of other queries; its not as easy as subtracting values from the current query here.

Additionally, here is another example from the report perspective

 

[arnelgp]

see the changes.
i made my own queries (prefixed with "a_").

 

[foshizzle]

Hi Arnel: so you got the previous ActualBal to show on the Begin Bal column, but I need the remainder of the columns to remain as-is.
i.e., If you run the report for 5/1/21, the Begin Bal column should show the ActualBal for 4/30/21; the remaining columns need to be for 5/1/21.

See markup below.

 

[arnelgp]

As i have said, i did not run any of your query.
The new report is using a_qryFinal as recordsource of the report.

 

[foshizzle]

Ah, sorry; I get it now.. I think I've gotten the two queries to work for the one report but I wont know until Tuesday when I go back in.
Thanks so much for your help!

 

[arnelgp]

there are other queries (in the background, prefixed with "a_qry") that builds
to create a_qryFinal.

you should test it thoroughly.