Query that calculates from the previous values.

[Gedeon]

Dear friends. I'm trying to create a query like in the picture.

value_1 is separately calculated.
value_c is calculated based on value_1.
My problem is to calculate 'value_total' which should get the last calculated data and add current data.
I have tried "DSum" and "Dlast" functions but this hangs or calculates very slow.


Does anyone know how to solve this problem?

[Gedeon]

any ideas ?

[NoellaG]

Hi,

running totals in a query are hard to do, but you could do the first calculation in a query and then try the running total in a report.
If you need to have it in a query, then you could use an intermediate table and use VBA to populate it with the running totals.

succes
NG

[ItsMe]

The only other way I know of is to take a trip to the data using Dlookup. It should be a little faster than Dmax or Dsum because you can use criteria with Dlookup.

DLookUp("[MyField]","MyTable","[ID]=" & [ID]-1)
https://support.microsoft.com/en-us/kb/210504

[June7]

What was the expression you tried with DSum?

DMax and DSum can use filter criteria as well. All of the domain aggregate functions have WHERE CONDITION argument.

DSum("ValueField", "MyTable", "DateField<=#" & [datefield] & "#")

DLookup won't return a running sum, it only returns single value of record specified in the WHERE CONDITION.

Yes, domain aggregate can perform very slowly in queries. As NoellaG suggests, a report is the best vehicle for this sort of calculation because then textbox has RunningSum property.

[ItsMe]

...DLookup won't return a running sum, it only returns single value of record specified in the WHERE CONDITION....

Correct, I am imagining avoiding Dsum and incorporating the Dlookup in a calc. So something like
MyValueTotal: DLookUp("[MyField]","MyTable","[ID]=" & [ID]-1)+[MyField]

[June7]

Still not seeing how that produces a running sum - it just adds two adjacent records which can be done with a nested subquery. http://allenbrowne.com/subquery-01.html#AnotherRecord

And it assumes there are no gaps in the ID sequence.

[Gedeon]

Thank you very much for your answers. I'll try all the aforementioned methods and will write a feedback soon.

[InsuranceGuy]

RunningSum: (select sum([DayCount]) from SampleTable ST where ST.dateField<=SampleTable.dateField)

What was the expression you tried with DSum?

DSum("ValueField", "MyTable", "DateField<=#" & [datefield] & "#")

I completely agree with June.

DLookup is not suited to your need for a running total because it will only look up a single field result from a source, not from within the query dataset you are building--so you can't build a recursive consideration within the same query using DLookup. You can do so with DSum and an appropriate criteria (A.K.A. a where filter)

I would only add that you can do multiple criteria in all of the Domain functions, including DSum and DLookup.

The issue with Domain functions is that they are VBA-based rather than JET-based. Simply put, JET-based functions are faster because they don't have to go consult the VBA libraries. Instead they run entirely in the database engine.

If we take June's code that is a field definition:

DSum("ValueField", "MyTable", "DateField<=#" & [datefield] & "#")

If we want it to run in JET (fast), we need to make some adjustments. The field definition should instead be:

RunningSum: (select sum([ValueField]) from MyTable MT where MT.dateField<=MyTable.dateField)

You will also need to ensure you group by your MyTable.[dateField].

Allen Browne covers this a bit here: http://allenbrowne.com/QueryPerfIssue.html