Loop through records and update value in VBA

I have a table with a xfrm_ID, and an amps value. There will be many records where the xfrm_id is the same. I have a column called Amp_seq that for each Xfrm_id I would like to number 1 to ..... from lowest amps to highest. below is an example of what I would like to have when completed.

Xfrm_id         Amps       Amp_seq
2345               1000              1
2345               1200              2
2345               1200              2
2345               1500              3

Any help is appreciated.
dgravittAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
You need to add a grouping section to the report and group by xfrm_id and add a sort by Amps.  Then you can add the Amp_Seq control and give it a ControlSource value of:

=1

Then set it's Group property to OverGroup.

You can hide the group header and footer so that the report doesn't change.  Otherwise the Xfrm_id would be in the group header and not show on each detail record.
0
dgravittAuthor Commented:
Sorry if I was confusing, but I need a loop that will populate the Amp_Seq in the table. The example was what I would like the data in table look like.
0
John TsioumprisSoftware & Systems EngineerCommented:
You will need 2 recordsets like this
Dim rst1,rst2
set rst1 = currentdb.Openrecordset("SELECT Xfrm_id  From YouTable GROUP BY  Xfrm_id ")
while not rst1.EOF
counter =1
set rst2 = currentdb.Openrecordset("SELECT * From YouTable WHERE  Xfrm_id=  " & rst1.Fields("Xfrm_id"))
while not rst2.EOF
rst2.edit
rst2.Fields("Amps") = 1000*counter
rst2.Fields("Amp_seq") = counter
rst2.Update
counter=counter+1
rst2.MoveNext
Wend
rst1.MoveNext
Wend

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
Are you sure you want to permanently store this value?  What happens if an item gets deleted? or something gets added in the middle?  Exactly what is the purpose of the sequence number?

PS John's code doesn't sort the recordset so since queries and tables are unordered sets, there are times when the query engine might deliver the rows "out of order".  The only way to control order is to specifically ORDER BY.  Also, rather than using two recordsets and runing potentially thousands of queries, just sort by the two fields and add break logic that resets the counter when the ID field changes.
0
John TsioumprisSoftware & Systems EngineerCommented:
I didn't have any info about ORDER BY thats why i didn't use it...maybe if have sample data i would/could apply sorting
0
PatHartmanCommented:
I would like to number 1 to ..... from lowest amps to highest. below is an example of what I would like to have when completed.
 That's how I knew.
0
dgravittAuthor Commented:
The table source is a query with two unions. I tried using the query with the unions as my record source for my report, but it is too slow. So, I make a table with the query then use it as my record source. Maybe this will help, I use the Amps_seq field to display the records with the 4 lowest values. It could be more than 4 records if some of the values are duplicated. The answer to your first question, this will be rebuilt every time before running the report. The table populated and then the amp_seq set. I thought it would be easier to use the amp_seq in case the number of values to return changes. In my query to feed the report, I just set amp_seq < 5, order amps lowest to highest. Hope this helps. I'm open to a better way of handling this.
0
PatHartmanCommented:
If this is a report, why would you return duplicate rows?  Why not let the union eliminate the dups?  Are you sure you need a union?  How can you tell where each row is coming from?
0
dgravittAuthor Commented:
the amps could be duplicated, but there are other fields that make it unique. I need all records for the lowest 4 amps. the data comes from 3 different sources, and is combined for one datasource. I need to loop through each xfrm_id, then assign the values to amp_seq for lowest to highest amps. I have manually assigned the amp_seq to some records,  and this gives me what I need. I just don't know how to write the code for loop.
0
Gustav BrockCIOCommented:
Just loop and update a recordset:

Dim rs As DAO.Recordset
Dim Counter As Long
Dim LastID As Long

Set rs = CurrentDb.OpenRecordset("Select Xfrm_id, Amps, Amp_seq From YourTable Order By 1, 2")
While Not rs.EOF
    If LastID <> rs!Xfrm_id.Value Then
        Counter = 0
        LastID = rs!Xfrm_id.Value
    End If
    Counter = Counter + 1
    rs.Edit
        rs!Amp_seq.Value = Counter
    rs.Update
    rs.MoveNext
Wend
rs.Close

Set rs = Nothing

Open in new window

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dgravittAuthor Commented:
This is so close. But, if an Amp value is the same for a Xfrm_id, the Amp_Seq needs to be the same as the first example shows. Thanks for the suggestion.
0
dgravittAuthor Commented:
I was able to make this work. Below is my final code. Thanks again.
Dim db As Database
Dim rs As DAO.Recordset
Dim Counter As Long
Dim LastID As Long
Dim LastAmp As Long
Set db = CurrentDb
LastAmp = 0
Set rs = db.OpenRecordset("Select XFBNK_ID, Amps, Amp_seq From AllEle where amps >=1 order by 1, 2")
While Not rs.EOF
    If LastID <> rs!XFBNK_ID.Value Then
        Counter = 0
        LastID = rs!XFBNK_ID.Value
    End If
    If LastAmp <> rs!Amps Then
    Counter = Counter + 1
    End If
    LastAmp = rs!Amps.Value
    rs.Edit
        rs!Amp_seq.Value = Counter
    rs.Update
    rs.MoveNext
   
Wend
rs.Close

Set rs = Nothing
0
Gustav BrockCIOCommented:
Thanks for the feedback.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.