INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Open two recordsets and populate field in one table based on criteria from another

Open two recordsets and populate field in one table based on criteria from another

Open two recordsets and populate field in one table based on criteria from another

(OP)
Good Afternoon Experts,

I am in need of some help. I have not written any code for 8 years so I am a bit rusty to say the least:) I have an item table (70K records) that has 4 fields: Item, Cube, Weight, and Code (code not currently populated). I have a case code table with 8 records, each containing the criteria for determining a case code based on cube and weight. I need to open each record in my item table and cycle through the 8 case code records to find a match where item cube and weight are within the upper and lower threshold for both cube and weight then return the corresponding case code to my "code" field in the item table. I have experimented with several threads and can't seem to modify something successfully to get what I want so I thought I would ask. Any help is appreciated! Below are examples of my two tables (hopefully formatting comes through) Thanks!

Item Table
Item Number Cube Weight Code
12345 0.52 1


Case Code Table
ID case_code cube_low cube_high weight_low weight_high Type
1 S1 0.00 0.10 0 1.00 Easy
2 S3 0.00 0.30 1 4.00 Easy
3 S4 0.00 0.30 4 20.00 Average
4 H1 0.00 3.00 20 50.00 Hard
5 AP 0.00 10.00 70 9999.00 Team
6 H4 0.00 9,999.00 50 70.00 Vary Hard
7 S2 0.10 0.30 0 1.00 Easy
8 S5 0.30 3.00 0 20.00 Average
9 H2 3.00 20.00 0 50.00 Hard
10 TP 10.00 20.00 70 9999.00 Team
11 H3 20.00 9,999.00 0 50.00 Hard
12 T2 20.00 9,999.00 70 9999.00 Team

RE: Open two recordsets and populate field in one table based on criteria from another

I'm guessing that case_code is what you want to add to the Item Table and that there are 3 fields in the Item Table?
Item Number
Cube Weight
Code
You could also use an array. To use a recordset:

Private Sub GetCode()
Dim db as database
Dim rs, rst as recordset
Dim rscntr, rstcnt, rsLoopcnt, rstLpcnt as Long
Dim sqlstr as String

Set db = currentdb()
Set rs = db.openrecordset("Select * from [Item Table]", dbopendynaset) 'dynaset allows changes
Set rst = db.openrecordset("Select * from [Case Code Table]", dbsnapshot) 'snapshot because you're not making changes to this table
rscntr = rs.recordcount
rstcnt = rst.recordcount
Do While rsLoopcnt <= rscntr
Do While rstLpcnt <= rstcnt
If rs.Fields("Cube Weight") >= rst.Fields("weight_low") And rs.Fields("Cube Weight") <= rst.Fields("weight_high") Then
sqlstr = "Update [Item Table] set [Code] = '" & rst.Fields("case_code") & "' where [Item Number] = '" & rs.Fields("Item Number")_
& "'" 'the _ at the end means code line continued on the next line
DoCmd.RunSql(sqlstr)
rstLpcnt = rstcnt
Else
rst.moveNext
rstLpcnt = rstLpcnt + 1
End If
Loop
rsLoopcnt = rsLoopcnt + 1
Loop
End Sub

I hope I remembered all the little ' and ". I should have typed it in Access so it would tell me if I forgot!
Good luck.
Laurie

RE: Open two recordsets and populate field in one table based on criteria from another

You don't need records sets you can use a update Statement

CODE --> SQl

UPDATE [Item Table] As I
INNER JOIN [Case Code Table] As C
ON (CUBE Between cube_low AND cube_high )
AND (Weight  Between weight_low AND weight_high )
SET Code = Type 

CODE --> VBA

DIM STR As String
Str = "UPDATE [Item Table] As I INNER JOIN [Case Code Table] As C ON (CUBE Between cube_low AND cube_high ) AND (Weight  Between weight_low AND weight_high ) SET Code = Type"
currentdb.execute str 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close