Need current and previous data for final report (1 Viewer)

foshizzle

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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?
 

foshizzle

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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
 

Attachments

  • Database31.zip
    103.4 KB · Views: 399

June7

AWF VIP
Local time
Yesterday, 21:05
Joined
Mar 9, 2014
Messages
5,424
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.
 
Last edited:

foshizzle

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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.
explain2.png
 

June7

AWF VIP
Local time
Yesterday, 21:05
Joined
Mar 9, 2014
Messages
5,424
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

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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
example3.png
 

Attachments

  • Database31.zip
    120.2 KB · Views: 391

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
19,169
see the changes.
i made my own queries (prefixed with "a_").
 

Attachments

  • Database31.zip
    127.3 KB · Views: 411

foshizzle

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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.

explain21.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 01:05
Joined
Nov 27, 2013
Messages
277
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

..forever waiting... waiting for jellybean!
Local time
Today, 13:05
Joined
May 7, 2009
Messages
19,169
there are other queries (in the background, prefixed with "a_qry") that builds
to create a_qryFinal.

you should test it thoroughly.
 

Users who are viewing this thread

Top Bottom