• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

Accumulation query problem

I'm having a problem creating accumulation query.  I have a table (NameID, Name, Qty)  I want to calculate the accumulating sum column (Accum Total) within the Name ("xxx", "yyy", ..)

NameID      Name      Qty      Accum Total
1      xxx      1      1
1      xxx      3      4
1      xxx      2      6

2      yyy      2      2
2      yyy      3      5
2      yyy      4      9
2      yyy      1      10

3      zzz      2      2
3      zzz      1      3
3      zzz      1      4

I'm using Access 2000, this is what I got so far:
SELECT test.NameID, test.Name, test.Qty, DSum("[qty]","test","NameID = " & [NameID]) AS [Accum Total]
FROM test;

NameID      Name      Qty      Accum Total
1      xxx      1      6
1      xxx      3      6
1      xxx      2      6
2      yyy      2      10
2      yyy      3      10
2      yyy      4      10
2      yyy      1      10
3      zzz      2      4
3      zzz      1      4
3      zzz      1      4

It only calculate the sum for each group.  Someone help!


0
cherryjari
Asked:
cherryjari
  • 11
  • 8
  • 5
2 Solutions
 
yhwhlivesinmeCommented:
put an example of how you want the data to look
0
 
cherryjariAuthor Commented:
This is the result table I want to be..
NameID     Name     Qty     Accum Total
1              xxx         1           1
1              xxx         3           4
1              xxx         2           6
2              yyy         2           2
2              yyy         3           5
2              yyy         4           9
2              yyy         1          10
3              zzz         2           2
3              zzz         1           3
3              zzz         1           4
0
 
d_a_hCommented:
You will need to create a function and then change your query

Public tmpCS As Double
Public tmpName As String

Function Cumulative_Sum(intCS, strName) As Double
    If strName = tmpName Then
        tmpCS = tmpCS + intCS
    Else
        tmpCS = intCS
        tmpName = strName
    End If
    Cumulative_Sum = tmpCS
End Function

And now change your query to:

SELECT test.NameID, test.Name, test.Qty, Cumulative_Sum(test.Qty, test.Name) AS [Accum Total]
FROM test
ORDER BY test.Name;

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
yhwhlivesinmeCommented:
it can be done without vba
0
 
yhwhlivesinmeCommented:
you will need something like a date field, otherwise how will it know to do it in that order? here's what you will need in a query though:

SELECT test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.TestDate)<=test_1.TestDate

I had to use that type of a query in a prior database of mine
0
 
yhwhlivesinmeCommented:
how are you ordering them in the query above? what are all the fields in the tables that you are pulling these from?
0
 
d_a_hCommented:
yhwhlivesinme:  Using your answer am getting an error "You tried to execute a query that does not include the specified expression 'Name' as part of an aggregate function"
0
 
yhwhlivesinmeCommented:
sorry, you will have to put that in a group by clause:

SELECT test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.TestDate)<=test_1.TestDate
GROUP BY test.NameID, test.Name, test.Qty

why is your test.nameID not unique?
0
 
cherryjariAuthor Commented:
yhwhlivesinme,
Thanks for reply.  I also have autonumber field in the table to keep the data in order.  I do have the date field but some of them is the same date, so the query might not work properly.
0
 
yhwhlivesinmeCommented:
I would use the autonumber field, and I would do this instead of what I have above:

SELECT test.AutoNumberField, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.AutoNumberField)<=test_1.AutoNumberField
GROUP BY test.AutoNumberField

so your query's group by clause doesn't have anything else in it.  Then I'd create another query referencing the query above and pull in whatever you want
0
 
d_a_hCommented:
IF you are going for yhwhlivesinme version then you will need to reinsert the Name field into his query as I presume that Autonumber is for the table and not specific to each group.

0
 
yhwhlivesinmeCommented:
yes he is correct, and I believe you will need to add test.name=test_1.name into the where clause because otherwise it will sum up everything (which is what my query in my project was supposed to do).  So something like this:

SELECT test.AutoNumberField, test.name Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.AutoNumberField)<=test_1.AutoNumberField and test.name=test_1.name
GROUP BY test.AutoNumberField, test.name
0
 
cherryjariAuthor Commented:
Thanks for both of you.  But my query is still not correct.

SELECT test.Line, test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test AS test_1
WHERE (((test.Line)<=[test_1].[line]))
GROUP BY test.Line, test.NameID, test.Name, test.Qty

Line      NameID      Name      Qty      Accum Total
1      1      xxx      1      10
2      1      xxx      3      27
3      1      xxx      2      16
4      2      yyy      2      14
5      2      yyy      3      18
6      2      yyy      4      20
7      2      yyy      1      4
9      3      zzz      2      6
10      3      zzz      1      2
11      3      zzz      1      1
0
 
d_a_hCommented:
Use the VBA route then it will work

(PS You are getting the same results as I have been getting with the query route)
0
 
yhwhlivesinmeCommented:
you need to use the auto number in there, like my last query
0
 
cherryjariAuthor Commented:
I'm getting close!  Something is little off.

SELECT test.Line, test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test AS test_1
WHERE (((test.Line)<=[test_1].[line]) AND ((test.Name)=[test_1].[name]))
GROUP BY test.Line, test.NameID, test.Name, test.Qty;

Line      NameID      Name      Qty      Accum Total
1      1      xxx      1      3
2      1      xxx      3      6
3      1      xxx      2      2
4      2      yyy      2      8
5      2      yyy      3      9
6      2      yyy      4      8
7      2      yyy      1      1
9      3      zzz      2      6
10      3      zzz      1      2
11      3      zzz      1      1
0
 
d_a_hCommented:
As you are so adamant that you want to go down the query route (in some ways don't blame you, though VBA is quicker!) here is the code

SELECT test.AutoNumberField AS ANF, test.Name AS Nme, DSum("[Qty]","test","[AutoNumberField]<=" & [ANF] & " AND [Name]='" & [Nme] & "'") AS Expr1
FROM test
GROUP BY test.AutoNumberField, test.Name;
0
 
d_a_hCommented:
If you are copying it out, please note:  " AND [Name]='" & [Nme] & "'") ..... the " & [Nme] & " is surrounded by single quotes ( ' ) as Nme is a string.  
0
 
yhwhlivesinmeCommented:
you can use DSum, but it is much slower, so depending on how large your tables are you may not want to use it.  When constructing my queries, shanesuebsahakarn suggested that, but we ended up going down a different road since it was so slow
0
 
d_a_hCommented:
Hence why the VBA option is the fastest
0
 
yhwhlivesinmeCommented:
I actually didn't know you could write a function and reference it in sql, can you do that with Access 97 as well?
0
 
cherryjariAuthor Commented:
It works!!  Thanks d_a_h!!  Thanks both of you!!
0
 
d_a_hCommented:
Yes as it is classified as a user defined function.  In Access you can use Built in Functions, this is just your own version.
0
 
yhwhlivesinmeCommented:
no problem! glad to help
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 11
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now