Running Total in Query

[pnpez]

Hello,

I very desperately need help with a query that I'm creating for a client as part of a bigger process. I have a running sum in a newly created field of a select query (soon to be make table query). The DSum is working fine except I need it to start over at a change in employee number, as follows:

Empl # Amount Cumulative Total

255 5 5
255 20 25
260 2 2
260 8 10

Is this scenario possible in a separate calculated field?
Can anyone help me to solve this?
Thanks,
pnpez

 

[vbaInet]

You didn't show us your DSum() statement.

This can be done easily in a report.

 

[pnpez]

VBA,

I really need this to be in a query because it is just a part of a bigger picture.

Here's the true Dsum:
Running Sum : DSum("[License Fees with Emp #].[ContractedSale]","License Fees with Emp #","[ID]<= " & [License Fees with Emp #].[ID])

The table = License Fees with Emp #
The field to be accumulated = ContractedSale
The primary key = ID
The field it needs to break on = Empl Numb

Empl Numb should probably be a part of the DSum but don't know how.

Again, this is what I need:

ID EmplNum ContrSale RunSum
(1) 255 $4 $4
(2) 255 $2 $6
(3) 260 $8 $8
(4) 260 $5 $13
ETC ETC

Thanks. FYI, I'm sure this would be easier in a program, but I'm not a programmer.

 

[vbaInet]

You just need to filter based on EmplNum too. See below:

Running Sum : DSum("ContractedSale", "License Fees with Emp #", "[ID] <= " & [License Fees with Emp #].[ID] & " AND EmplNum = " & [License Fees with Emp #].EmplNum)

 

[pnpez]

Thanks, this looks like what I need, but I'm getting this error message when I run the query:

Syntax error in date in query expression '[ID]<= 1 AND EmplNum = 15607'.
It's recognizing some field as "date" but I don't have dates anywhere. The employee number listed here is probably the first number it looks at with ID = 1. I don't get it, maybe my Employee number should be a number field, instead of text. ID field is just autonumber.

 

[vbaInet]

I don't get it, maybe my Employee number should be a number field, instead of text.

That's right!

 

[pnpez]

VBA,

After many changes to my fields, such as employee numb(like filling in blank cells), I finally got it to work. Thanks so much for prolonging my stay at the client.

 

[vbaInet]

Happy to help!