?
Solved

stepping thru an access table calculating fields

Posted on 2011-03-01
5
Medium Priority
?
379 Views
Last Modified: 2012-05-11
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
Comment
Question by:Stephen Roesner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:als315
ID: 35014604
This is "running sum". You can find a lot of examples for it:
http://www.blueclaw-db.com/download/running_sum_query.htm
0
 

Expert Comment

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


Dim rst as new adodb.recordset
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

by:Stephen Roesner
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.

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Provider = CurrentProject.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "c:/ERPDP/Databases/Pharmacy.mdb"
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "Response_Med_RX_Total_Data_50_65", conn, adOpenDynamic, adLockPessimistic
 
 
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

by:
TownTalk earned 1500 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

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question