How to make a running Sum on an expression in a Query (1 Viewer)

[mveijndh]

Hi guys,

I've to make a running sum in a query that makes a value based on exchange rates. Meaning I have to calulate a GBP value first and than add up these values to have them displayed on a form.
I have to do this for multiple tables as I have to store multiple fact's, Labour, expenses, purchases etc. In the end I need a total on costs.
I've tried in a single query, that did not work, so now I'm trying this in multiple query's. Problem is I'm not able to make the running sum of the expression in the Query. How can I best do this?
I'm fine is the results is individual values on the form as I can sum them there.

 

[arnelgp]

you need to have an Autonumber field in your query in order to build what your requirement.
need more info about the fields in your query.

 

[plog]

Running Sum implies order (first record, second record, last record, etc.). Do you have a field on which you order your data?

Can you post some sample data to demonstrate your issue? Please post 2 sets:

A. Starting sample data from your table(s). Include table and field names and enough sample data to cover all cases.

B. Expected results using A. Show me what you expect your query to return if you feed it the data you provide in A.

 

[mveijndh]

Hi,

A sample set is not simple as this is part of a very large database. I do have the potential to order the data as in almost all tables I have the posibilety to add the order ID. As I need to do this on many tables, the qustion was intented as a general one. What do I need to do to provide me the ability to make a running sum of the costs on a project. The costs are divided in Labour, Mileage, Expenses, Purchases, Freight , Invoices, etc.
Thanks beforehand

 

[arnelgp]

take for example the labor cost running sum:

select field1, Dsum("costField","laborTable","ID <= " & laborTable.ID) As RunningSum FROM laborTable;

 

[mveijndh]

Hi Arnelgp,

I'll give it a shot in a test. I assume this can be part of a larger database as well?
I'll check out the Dsum() on this forum. I think I'll find lost of information about that. Just did not know this was the function to use.

Thanks

 

[arnelgp]

you can also use subquery as a replacement for Dsum:

SELECT T1.field1, (SELECT sum([costField]) FROM laborTable WHERE ID <= T1.ID) As RunningSum FROM laborTable AS T1;

 

[mveijndh]

If I'm not mistaken:
The part within () runs before the main query is executed?
I think that'll sort my problem best.

 

[arnelgp]

yes sub-query runs first.

 

[delowardidar]

take for example the labor cost running sum:

select field1, Dsum("costField","laborTable","ID <= " & laborTable.ID) As RunningSum FROM laborTable;

it is fine. its working. but ID is not sequential then? and I want to show report short on Date. so i need to calculate the running sum depending on date. and note that there are many same date record present.

then what will be the solution?

Question Summery:

>no sequential id (id present)
>date short (Ascending)
>more then one entry in an one date
>report short on date ascending

Goal:
showing opening & closing balance in report of a ledger

Thanks in advance

 

[mveijndh]

It's what you want, either you sort on the ID or you sort on date? or use the Date/time format to sequential sort the query.

 

[Galaxiom]

Goal:
showing opening & closing balance in report of a ledger

Sum the transactions up to the beginning of the period and again for the end. If you want to be really efficient, then sum the transactions during the period and add them to the opening balance.

This is avoids the massive overhead of calculating a running sum.

 

[delowardidar]

Thank you user Mr. "mveijndh" for your reply. if i query short on date then date sequence is ok, but running sum calculation is easy based on sequential ID. so if you post a entry today (e.g. 27-3-2017) for back date (e.g. 1-3-2017), assume previous entry was 20,

so this entry hold the id no. 21, and query position is 1st . that why running balance will show wrong balance.

 

[delowardidar]

Sum the transactions up to the beginning of the period and again for the end. If you want to be really efficient, then sum the transactions during the period and add them to the opening balance.

This is avoids the massive overhead of calculating a running sum.

Thank you so much. I think you are right. this will be better. would you please give me the expression or some more information that how can i indicate "from the beginning" and "end" when user entering his required period.

Thanks in advance.