Running balance query (1 Viewer)

tttccc · Mar 28, 2012

Hi all, I am new to using access and trying to work out a query that will transform the set of data I have into one with a sort of running total figure.

The data I have is in the format shown here:

Left hand field is item, right hand field is movement of that item

a 1
a 5
a -1
b 2
b -3
b 2

And I was looking to add this extra column on the end which calculates a running total for each item.

So the columns are now: item, movement, balance

a 1 1
a 5 6
a -1 5
b 2 2
b -3 -1
b 2 1

Apologies for the bad demonstrations, can't upload a picture showing how my tables are laid out at work.

If anyone has any suggestions on where to start let me know.

Thanks all

plog · Mar 28, 2012

Running balance implies there exists an order to your data (i.e a,1 comes before a,5 comes before a,-1). How do you determine that order? Is there a date field with each record?

As long as there is, then you would create a field in your query using the DSum function (http://www.techonthenet.com/access/functions/domain/dsum.php) implementing the item type and date field of each record in the criteria section of the function.

If you need more help, reply back with the names of relevant fields of your data source (item field, quantity field, date field)

tttccc · Mar 28, 2012

Ah I see,

yes my table has a date field, i only left it out to make it easier to represent the table on here.

I have three fields named as follows located in "Table1":

Item, Date, and Qty.

Should I add a fourth field with something like this?

Balance:dsum("qty", "Table1" ,"item = [item]")

RainLover · Mar 28, 2012

I am on my way out so I simply did a Copy Paste from an old Database of mine.

Code:

SELECT Orders.EmployeeID AS EmpAlias, Sum(Orders.Freight) AS Freito, Format(nz(DLast("Freight","Orders","[EmployeeID] < " & [EmpAlias])-[Freito],0),"Currency") AS Difference, Format(DSum("Freight","Orders","[EmployeeID]<= " & [EmpAlias])-[Freito],"Currency") AS RunTot, [RunTot]+[Freito] AS Balance
FROM Orders
GROUP BY Orders.EmployeeID;

Please use it as a guide.

PS Do not use Date as a Field Name. It is a reserved word.

plog · Mar 28, 2012

Ditto on 'Date' being a bad field name. Below is the SQL to generate a query for your running total:

Code:

SELECT Item, Qty, DSum("[Qty]","Table1","[Item]='" & [Item] & "' AND [Date]<=#" & [Date] & "#") AS RunningTotal
FROM Table1;

RainLover · Mar 29, 2012

Just be carefull on the DSum as it gets its Data directly from the Table which may have more records than the query.

The Query could be filtered.

Greek · Sep 29, 2016

Would someone kindly look at my dsum syntax. I am getting a #Error when I run it. Thanks.

plog · Sep 29, 2016

Reminds me of a sit-com gag a saw in the early 90's where a guy bought a VCR, went to set it up but it didn't have a manual, it included an instructional video which showed the user how to set up the VCR.

That's your query. CumSum is part of qryCustomer, but to get the CumSum value, qry Customer needs to run, but to run that query it needs for the CumSum field to run, but for the CumSum field to run it needs the qryCustomer to run, but for qryCustomer to run...

You can't reference the query you are running in the query itself.

Greek · Sep 30, 2016

Many thanks!

Running balance query (1 Viewer)

tttccc

New member

plog

Banishment Pending

tttccc

New member

RainLover

VIP From a land downunder

plog

Banishment Pending

RainLover

VIP From a land downunder

Greek

Registered User.

Attachments

plog

Banishment Pending

Greek

Registered User.

Similar threads

Users who are viewing this thread