DSUM Running total by group

[chowing0823]

Dear fellows:



I have been trying to write a query to make a running total by groups, but I keep getting error in return. What's wrong with my code?

Code:SELECT test.name, test.Date, test.amount, DSum("[amount]","[test]","[ID]<=" & [ID] & "And [name]=" & [name]) AS Expr1
FROM test;

fieldsField types
ID Number
Name Text
Date Date/Time
Amount Number


Org. table

ID	Name	Date	Amount
1	ABC	11/1/2014	20
2	ABC	11/3/2014	60
3	EEF	12/2/2014	40

[pbaldy]

The text field requires delimiters:

DLookup Usage Samples

[chowing0823]

Hi there:

Thanks a bunch. I have changed the code, it returns values to me but it's not adding with the correct groupings. What gives?

name	Date	amount	Expr1
ABC	11/1/2014	20	20
ABC	11/3/2014	60	80
EEF	12/2/2014	40	120

[pbaldy]

What's the expression now? Hopefully the reserved word "Name" isn't the problem, but shouldn't be since it's bracketed.

[chowing0823]

The expr1 returns running total for all of the records, regardless of the names. My objective is to start a new running total based on group. Any help would be much apperciated

Objective:

name	Date	amount	Expr1
ABC	11/1/2014	20	20
ABC	11/3/2014	60	80
EEF	12/2/2014	40	40

current

name	Date	amount	Expr1
ABC	11/1/2014	20	20
ABC	11/3/2014	60	80
EEF	12/2/2014	40	120

[chowing0823]

SELECT test.name, test.Date, test.amount, DSum("[amount]","[test]","[ID]<=" & [ID] & "And [name]=" & '[name]' &"") AS Expr1
FROM test;

[pbaldy]

You have the quotes wrong. Try

DSum("[amount]","[test]","[ID]<=" & [ID] & " And [name]='" & [name] & "'")

[chowing0823]

Paul:

You are a true gentleman, thanks for providing me such detailed answers. Yes, the query now returns me with the correct values.

Many thanks

[pbaldy]

Happy to help and welcome to the site by the way!

[Fredo]

I have the same situation, but doesn't work

[Welshgasman]

I have the same situation, but doesn't work

Please do not hijack someone else's thread.

Post a new question and show what you have tried.

'doesn't work' is about much use as a chocolate fireguard.