Running Lookup in Access

[KUYJS]

Hi,

Is it possible to use running range in access formula like we do in excel
Example:
row 2, max(a$2:a2)
row 3, max(a$2:a3)
row 4, max(a$2:a4) and so on...

 

[ranman256]

You don't use Max that way in Access.
either run a report that has Running totals,
or a query that shows the Max of a column.

 

[KUYJS]

Query will give me max of entire column/field whereas my requirement is max of target column till target row minus one means till previous row.

This way each row will have max of target column till previous row.

 

[xenou]

That would be done with a report as stated. Or in Excel if you like. It is not really something you would do in a query (it is feasible if you have a key that you can use for ordering and filtering , but very inefficient SQL).

 

[xenou]

Here is a sample query (by way of example using an SQL solution):

My Data:
TransDate Qty
1/1/2018 1
1/2/2018 1
1/3/2018 2
1/4/2018 5
1/5/2018 4
1/6/2018 3
1/7/2018 5
1/8/2018 6
1/9/2018 8

My Query:

select 
	t1.TransDate, 
	(select Max(Qty) from Table7 t2 where t2.TransDate <= t1.TransDate) as MaxOfQty
from
	Table7 t1
order by 
	t1.TransDate

Result:
TransDate MaxOfQty
1/1/2018 1
1/2/2018 1
1/3/2018 2
1/4/2018 5
1/5/2018 5
1/6/2018 5
1/7/2018 5
1/8/2018 6
1/9/2018 8


I probably shouldn't overstate the inefficiency - it would probably bad for large tables (in excess of a hundred thousand records) and probably not too bad for small tables (less than 10000 rows). Not really sure about the in between size.

This is what is called a correlated query which mean the engine has to calculate a result for every row because of the type of link created here between the inner query and the outer query (in this case, specifically, there is a new TransDate criteria for each row, and a query for the max on the qty up to that TransDate).