Running Lookup in Access

KUYJS

New Member
Joined
Sep 3, 2018
Messages
43
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...
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,900
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

New Member
Joined
Sep 3, 2018
Messages
43
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,694
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,694
Office Version
  1. 2013
Platform
  1. Windows
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:
Code:
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).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,336
Messages
5,528,101
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top