Solved

# stepping thru an access table calculating fields

Posted on 2011-03-01
337 Views
I am trying to go thru an access table from start to end. I have an AMT - YTD_Total - Threshold_Amt - and Limit_Amt fields. I have to add the Amt Field to YTD_Total and I go thru peoples ID numbers. For exp.
rec1         Joe Blow     id 12345             Amt - 5          YTD_Total -  5
rec2         Joe Blow     id 12345             Amt - 10         YTD_Total - 15
rec2         Mary Blow   id 67891             Amt - 20        YTD_Total - 20
rec2         Mary Blow   id 67891             Amt - 30        YTD_Total - 50
I would need to update the field in the table I am steping thru as I go.
Is this possible and if so how would I do it? I have never stepped thru a table and updated as I go before.

0
Question by:Grizbear51
• 2
• 2

LVL 39

Expert Comment

ID: 35014604
This is "running sum". You can find a lot of examples for it:
0

Expert Comment

ID: 35016636
Here's a simple example of looping through a recordset and updating the records as you go:

Dim RunningTotal as long

rst.open "MyTable", CurrentProject.connection

Do until rst.eof
RunningTotal = RunningTotal + rst![Amount]
rst![YTD_Total]=RunningTotal

rst.update
rst.MoveNext
Loop

rst.close
set rst=nothing

I'm sure you can work out the rest for yourself
0

Author Comment

ID: 35024173
Towntalk,
You got me started. I had trouble with the connection working but finally researched enough to get it working however I found a problem that is driving me crazy. The access table looks sorted but when I go thru the loop it seems to be picking random records. Below is my code. I even tried rst.movefirst before the loop and that didnt fix it so Im really confused as to how to start at the top of the table and work down. What the code is trying to do is run the total as long as the id is the same if not zero out and start running new total with new id (in case you were wondering) Its not fully tested since Im tryin to start with record one. Any ideas.

conn.Provider = CurrentProject.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/ERPDP/Databases/Pharmacy.mdb"

Do Until rst.EOF
CardID = rst![CARDHOLDER_ID]
If rst![CARDHOLDER_ID] = CardID Then
VarGross = rst![PLAN_PAID_AMT]
rst![Gross] = VarGross
RunningTotalY = RunningTotalY + rst![PLAN_PAID_AMT]
rst![YTD_Total] = RunningTotalY
If RunningTotalY > 15000 Then
rst![Threshold] = 15000
Else
rst![Threshold] = VarGross
End If
If RunningTotalY > 90000 Then
rst![Limit] = 90000 - RunningTotalY
Else
rst![Limit] = 0
End If
rst.Update
rst.MoveNext
Else
VarGross = 0
RunningTotalY = 0
End If
Loop

rst.Close
Set rst = Nothing
0

Accepted Solution

TownTalk earned 500 total points
ID: 35024823
I realise now I gave you a piece of code that wouldn't work. You correctly figure out that you need the adOpenDynamic in order to be able to update records.

I assume also now that your table is in another .MDB So this is why you are using the ADODB.Connection variable. CurrentProject.connection is only valid for tables within the current database.

It sounds like you need an index on the table in order to get the sequence that you require. So lets say CARDHOLDER_ID is the field you want to be in ascending numeric order. Design the table and make sure that field is indexed, then after the rst.open statement in your code, add a line as follows:

rst.index="CARDHOLDER_ID"

btw in order to tidy up your code at the end, after you've closed the table you need.

conn.close
set conn = nothing
0

Author Closing Comment

ID: 35027644
Thanks - got me started in the right direction.
0

## Featured Post

### Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …