Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do YOU create a running total in a query
- Thread starter RBE
- Start date
Try this FAQ- faq701-1155 under Microsoft: Access Queries and Access/SQL.
It describes how to create an autonumber field in a query. You should be able to modify that function to your liking to create something that does a running total for you by passing the value you're adding up to the function.
jacksonmacd
Programmer
Wrote a function to create a new temporary table with the same records and primary key as the original table. Generated the running totals into the temporary table by looping thru the recordset and brute-force adding the values. Created a new query with the temporary table joined to the original table.
LonnieJohnson
Programmer
What a coincidence, I just posted a very simular procedure in another formum. Here is the response I posted. Hope it helps...
Subject: Re: [AccessDevelopers] Running Sum
This is a hard one. Here is one approach that I took.
1. Add a field to your table called RunningSum.
2. In your form add a private procedure to your form called TheSum()
This procedure will cycle thru your records and calculate a running
sum.
(note: I called the field that has the values to be added MyNums)
Private Sub TheSum()
Dim db As Database
Dim rst As Recordset
Dim intCnt As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("MyTable"
Do Until rst.EOF
rst.Edit
intCnt = intCnt + rst!MyNums 'this is the field we are summing
rst!RunningSum = intCnt
rst.Update
rst.MoveNext
Loop
Me.Refresh
End Sub
3. Now add a button to refresh call the proc and refresh the screen.
Private Sub cmdSum_Click()
Call TheSum
End Sub
This is a simple approach and may need modifying if you have a special
sort in the form's recordset. Try it and see if it works. Let me know
where it may need modifying.
ljprodev@yahoo.com
ProDev, MS Access Applications B-)
Subject: Re: [AccessDevelopers] Running Sum
This is a hard one. Here is one approach that I took.
1. Add a field to your table called RunningSum.
2. In your form add a private procedure to your form called TheSum()
This procedure will cycle thru your records and calculate a running
sum.
(note: I called the field that has the values to be added MyNums)
Private Sub TheSum()
Dim db As Database
Dim rst As Recordset
Dim intCnt As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("MyTable"
Do Until rst.EOF
rst.Edit
intCnt = intCnt + rst!MyNums 'this is the field we are summing
rst!RunningSum = intCnt
rst.Update
rst.MoveNext
Loop
Me.Refresh
End Sub
3. Now add a button to refresh call the proc and refresh the screen.
Private Sub cmdSum_Click()
Call TheSum
End Sub
This is a simple approach and may need modifying if you have a special
sort in the form's recordset. Try it and see if it works. Let me know
where it may need modifying.
ljprodev@yahoo.com
ProDev, MS Access Applications B-)
- Thread starter
- #5
tlbroadbent
MIS
Check this MS KB article.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
thegentleman
IS-IT--Management
Hello all,
I have a variation of this problem which none of the above solutions have helped resolve.
Basically I need to number the records in a query from 1 to the total number of records in sequence. This works fine normally using the FAQs linked to by euskadi above, but it does not work if the query is grouped. For example, if my ungrouped query holds 10 records I get 1,2,3,4 etc using this function, but when that query is grouped, that same field now returns 2,4,6,8 etc.
Does anybody know of a modified code that would solve this problem?
Regards
tg
I have a variation of this problem which none of the above solutions have helped resolve.
Basically I need to number the records in a query from 1 to the total number of records in sequence. This works fine normally using the FAQs linked to by euskadi above, but it does not work if the query is grouped. For example, if my ungrouped query holds 10 records I get 1,2,3,4 etc using this function, but when that query is grouped, that same field now returns 2,4,6,8 etc.
Does anybody know of a modified code that would solve this problem?
Regards
tg
LonnieJohnson
Programmer
Have you tried taking the results of the grouped query and feeding it into another query and then use the numbering logic above?
Just a thought. prodevmg@yahoo.com
ProDev, MS Access Applications B-)
May God bless you in the year 2002.
Just a thought. prodevmg@yahoo.com
ProDev, MS Access Applications B-)
May God bless you in the year 2002.
To RBE another variation. I don't know if you have this already. Uses a group by and unequal self join.
select t1.id, t1.amount, sum(t2.amount)
from table t1, table t2
where t2.id <= t1.id
group by t1.id, t1.amount
order by sum(t2.amount)
To the gentleman
Try adding distinct to the column you are grouping on.
count(distinct, groupid)
select t1.id, t1.amount, sum(t2.amount)
from table t1, table t2
where t2.id <= t1.id
group by t1.id, t1.amount
order by sum(t2.amount)
To the gentleman
Try adding distinct to the column you are grouping on.
count(distinct, groupid)
thegentleman
IS-IT--Management
LonnieJohnson - Thanks for the reply. This is something I first thought of but wanted to avoid due to the fact that this procedure already runs through three queries before it gets to this stage. I also noticed that using this solution resulted in numbers that were ascending but not starting from 1. For example, if the query brought back 4 results, the column would read '5,6,7,8' even after the number had been re-set. I also noticed an even stranger effect and ultimately a solution: I made this query into an append query and now the column that reads '5,6,7,8' appends to a second table as '1,2,3,4'. I tested this a couple of times and in each case it worked. I'm not going to try and find out why it does this - it works (albeit exhaustively) and that is all that matters!
Final Solution:
1. Create a New Query that uses the Original Query as its data source.
2. Select all Fields into the Query and create a new field which uses the function supplied by euskadi above to generate ascending numbers from 1.
3. Create a new Append Query and select the query created in step 2 as the source. Append the data into your final table.
grnzbra - thanks for the reply but this data is not going into a report.
cmmrfrds - thanks for your input!
tg
Final Solution:
1. Create a New Query that uses the Original Query as its data source.
2. Select all Fields into the Query and create a new field which uses the function supplied by euskadi above to generate ascending numbers from 1.
3. Create a new Append Query and select the query created in step 2 as the source. Append the data into your final table.
grnzbra - thanks for the reply but this data is not going into a report.
cmmrfrds - thanks for your input!
tg
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question
