cherryjari
asked on
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","NameI
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!
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","NameI
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!
put an example of how you want the data to look
ASKER
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
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
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;
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;
it can be done without vba
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.Te
I had to use that type of a query in a prior database of mine
SELECT test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.TestDate)<=test_1.Te
I had to use that type of a query in a prior database of mine
how are you ordering them in the query above? what are all the fields in the tables that you are pulling these from?
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"
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.Te
GROUP BY test.NameID, test.Name, test.Qty
why is your test.nameID not unique?
SELECT test.NameID, test.Name, test.Qty, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.TestDate)<=test_1.Te
GROUP BY test.NameID, test.Name, test.Qty
why is your test.nameID not unique?
ASKER
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.
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.
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)<=te
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
SELECT test.AutoNumberField, Sum(test.Qty) AS [Accum Total]
FROM test, test as test_1
WHERE (test.AutoNumberField)<=te
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
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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].[l
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
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].[l
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
Use the VBA route then it will work
(PS You are getting the same results as I have been getting with the query route)
(PS You are getting the same results as I have been getting with the query route)
you need to use the auto number in there, like my last query
ASKER
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].[l
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
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].[l
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are copying it out, please note: " AND [Name]='" & [Nme] & "'") ..... the " & [Nme] & " is surrounded by single quotes ( ' ) as Nme is a string.
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
Hence why the VBA option is the fastest
I actually didn't know you could write a function and reference it in sql, can you do that with Access 97 as well?
ASKER
It works!! Thanks d_a_h!! Thanks both of you!!
Yes as it is classified as a user defined function. In Access you can use Built in Functions, this is just your own version.
no problem! glad to help