Obtain previous value from query (1 Viewer)

foshizzle

Registered User.
Local time
Today, 05:39
Joined
Nov 27, 2013
Messages
277
I have the following query which will run on any given date. How could I obtain the previous value for ActualBal?
i.e., if the report date is run for 5/1/21, would could I get the ActualBal for 4/30/21?

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;
 

Ranman256

Well-known member
Local time
Today, 05:39
Joined
Apr 9, 2015
Messages
4,339
on a form , put a textbox txtRunDate. User enters a date.

the query would then sum data to that date.
select sum(amt) from table where [EventDate]<=forms!fMyForm!txtRunDate
 

foshizzle

Registered User.
Local time
Today, 05:39
Joined
Nov 27, 2013
Messages
277
Sorry; to clarify, I need to be able to use the value for the previous ActualBal in a new query
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:39
Joined
Feb 19, 2013
Messages
16,553
Use the same query but change the criteria to tempvars start date-1 and same for enddate

if you want to compare, join the two queries - you’ll probably need to left join to the new query in case there isn’t a previous value

I suspect you would do better to start again with your sumtran2 query or even earlier for a more efficient query
 

Users who are viewing this thread

Top Bottom