Reset calculated value to 0 after each month

[foshizzle]

I am using qryRMonthlyGainSum4GainLoss (below) to obtain the Actual Balance as follows:
ActualBal: Nz(([Total]+[AirGainLossNZ]),0)
Another value for gain/loss, called AirGainLossNZ, is calculated in a separate query as shown below in qryRGainLoss15Report.

Somehow I need to reset the value for AirGainLossNZ after the last day of each month so the first day of each month has a gain/loss value of 0.
How would I go about doing this? Is it possible to keep the previous values in case the report needs re-run for a previous month?

qryRMonthlyGainSum4GainLoss
SELECT qryRMonthlySumTrans2.TransactionID, qryRMonthlySumTrans2.TransactionDate, qryRMonthlySumTrans2.AirlineCompanyLookup, qryRMonthlySumTrans2.BegBalance, qryRMonthlySumTrans2.GallonsRcvd, qryRMonthlySumTrans2.GallonsIssd, qryRMonthlySumTrans2.Total, qryRMonthlySumTrans4GainLoss.AirGainLossNz, Nz(([Total]+[AirGainLossNZ]),0) AS ActualBal FROM qryRMonthlySumTrans2 LEFT JOIN qryRMonthlySumTrans4GainLoss ON qryRMonthlySumTrans2.AirlineCompanyLookup = qryRMonthlySumTrans4GainLoss.AirlineCompanyLookup WHERE (((qryRMonthlySumTrans2.TransactionDate) Between [TempVars]![tmpStartDate] And [TempVars]![tmpEndDate])) GROUP BY qryRMonthlySumTrans2.TransactionID, qryRMonthlySumTrans2.TransactionDate, qryRMonthlySumTrans2.AirlineCompanyLookup, qryRMonthlySumTrans2.BegBalance, qryRMonthlySumTrans2.GallonsRcvd, qryRMonthlySumTrans2.GallonsIssd, qryRMonthlySumTrans2.Total, qryRMonthlySumTrans4GainLoss.AirGainLossNz;

qryRGainLoss15Report
SELECT qryRGainLoss13TotIssued.AirlineCompanyLookup, qryRGainLoss13TotIssued.LastOfRunIss, Last(FormatPercent((([LastOfRunIss])/([MaxOfTotalIss])))) AS PercentUsed, qryRGainLoss12ActualBalanceTotGainLoss.TotGainLoss, Round((([LastOfRunIss]/[MaxOfTotalIss])*[TotGainLoss]),2) AS AirGainLoss, qryRGainLoss14MaxTotIssued.MaxOfTotalIss, ([TotGainLoss]/[MaxOfTotalIss]) AS GainLossPcnt, (Last([LastOfRunIss])/([MaxOfTotalIss])) AS UsedNumeric FROM qryRGainLoss14MaxTotIssued, qryRGainLoss12ActualBalanceTotGainLoss, qryRGainLoss13TotIssued GROUP BY qryRGainLoss13TotIssued.AirlineCompanyLookup, qryRGainLoss13TotIssued.LastOfRunIss, qryRGainLoss12ActualBalanceTotGainLoss.TotGainLoss, qryRGainLoss14MaxTotIssued.MaxOfTotalIss;

Here is a sample report:

 

[Pat Hartman]

You need to include a WHERE argument to limit the data selected to the month in question.

Also,

why does that query have a Cartesian Product? That would almost certainly duplicate data. Use proper joins.

AND, Last() and First() are meaningless. It is quite likely that you are not getting the values you expect. Even if you are today, the query could fail tomorrow if the row order changes. Last() and First(0 refer to physical order NOT some logical order and since queries are unordered sets, you get whatever the query engine decides is "First" or "Last". This issue is much more obvious when using a "real" Relational database such as SQL Server but Jet/ACE mask the issue to some degree by being consistent in how rows are returned because there is only ever a single process running. People who mistakenly use First/Last usually mean Minimum/Maximum, either referring to a date or a numeric value.

 

[foshizzle]

Two questions:

1. If I include a WHERE, wouldn't that still keep the gain/loss value for that point in time?
2. In reference to your proper joins statement; are you referring to the qryRGainLoss15Report? I figured this one out on my own and as you can probably tell, I'm not a DB guy; it took me 15 queries to find the final result (which is correct as it stands now). I'm attaching the queries in a db here; perhaps you can provide some pointers on how to clean it up with proper joins?

 

[The_Doc_Man]

The solution to your problem should be an ordinary GROUP BY of the period in question. You have GROUP BY clauses in the queries you showed us. What is the problem that is blocking you from using that facility to get the answer? If you have dates in there (and it appears that you do), you could do something as simple as taking that date and doing this:

SELECT  ....., FORMAT( Some-date-field, "yyyymm" ) AS YM, ....  SUM( some-value) As SumSomeValue, ... 
FROM whereever-it-originates
WHERE select-some-criteria
GROUP BY other-group-by-field-names, YM ;

That should split up items according to your business separation, but that last GROUP BY will break up the output into year/month chunks for which all sums would be effectively reset to 0.