Update tbl1 from tbl2, nested count for top? 1

[hovercraft]

I'm really at a loss for how to accomplish this.

I want to update a table (tbl1) which has a list of pre-defined serial numbers (my_serial) and some empty fields (casenum, ticketnum, dob, lastname)
I need to update tbl1 with records from a second table (tbl2). tbl2 has the fields (cust_casenum, cust_ticketnum, cust_dob, cust_lastname)

Not all of the fields are empty in tbl1, so I'm trying to find the next record with empty fields (empty being casenum,ticketnum etc...my_serial is populated) and update them to the value of tbl2.

I thought perhaps I could nest a COUNT of the records in tbl2 as the value for TOP in a sql statement but then I found out I couldn't use UPDATE with TOP and now my brow hurts from squinting and scowling.

Does anyone have any suggestions on how to tackle this?

Thanks in advance,
Hovercraft

 

[randy700]

Assuming the values in tbl2 are correct, I'd simply run the update query. If it updates the fields that are not null, so be it.


Randy

 

[hovercraft]

Thanks randy but that will not work. There's no field to join on. The records that have data need to keep the data they have.

For example if tbl2 has 100 records I need to grab the first 100 records of tbl1 where all the fields are empty (except the my_serial field)and update them with the 100 records from tbl2.
It's sort of a half-append/half-update thing. But more than likely I'm approaching this incorrectly.

 

[hovercraft]

I found this as an example solution, sorta..:

UPDATE myTable SET Field1 = 'SomeValue'

Where PrimaryKey IN 
      (Select TOP 1000 PrimaryKey From myTable Order By PrimaryKey)

except in my situation the "top" number is dependant on the number of records in tbl2 and I need to update tbl1. And there is not a primary key. I'm basically assigning serial numbers from a pre-defined batch of serials. tbl2 is updated on a regular basis so I have to update tbl1 with tbl2 data.

 

[randy700]

How about... (untested)

Dim db as Database
Dim rsA as Recordset, rsB as Recordset
Dim strSQL1 as String, strSQL2 as String
strSQL1 = "SELECT * FROM tbl1 WHERE Trim(Len(casenum & ticketnum & dob & lastname)) = 0"
strSQL2 = "SELECT * FROM tbl2"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strSQL1)
Set rsB = db.OpenRecordset(strSQL2)
rsA.MoveFirst
rsB.MoveFirst
With rsA
   Do Until .EOF
      If not rsB.EOF
         .Edit
         casenum = rsB!cust_casenum
         ticketnum = rsB!cust_ticketnum
         dob = rsB!cust_dob
         lastname = rsB!cust_lastname
         .Update
         rsB.MoveNext
      End If
      .MoveNext
   Loop
End With

Randy

 

[hovercraft]

I recieve a compile error: "invalid outside procedure".

Should there be a "then" after "If not rsB.EOF"?

we're using 2007 and vista.

 

[randy700]

Sorry. Been working with (would you believe it) COBOL lately. Should be

Dim db as Database
Dim rsA as Recordset, rsB as Recordset
Dim strSQL1 as String, strSQL2 as String
strSQL1 = "SELECT * FROM tbl1 WHERE Trim(Len(casenum & ticketnum & dob & lastname)) = 0"
strSQL2 = "SELECT * FROM tbl2"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strSQL1)
Set rsB = db.OpenRecordset(strSQL2)
rsA.MoveFirst
rsB.MoveFirst
With rsA
   Do Until .EOF
      If not rsB.EOF [COLOR=red]Then[/color]
         .Edit
         casenum = rsB!cust_casenum
         ticketnum = rsB!cust_ticketnum
         dob = rsB!cust_dob
         lastname = rsB!cust_lastname
         .Update
         rsB.MoveNext
      End If
      .MoveNext
   Loop
End With

Randy

 

[hovercraft]

What's a ... "CO BOL"????
(just kidding)

Thanks Randy! I had to tweak a bit as I had failed to mention that all fields are text.
and for some strange reason it wasn't working until I placed the recordset name in front of the field for tbl1, even though you have a "with" statement.
But it works and works great!!
Thank you!

Hovercraft
This is what I ended up with

Private Sub Command0_Click()

Dim db As Database
Dim rsA As Recordset, rsB As Recordset
Dim strSQL1 As String, strSQL2 As String
strSQL1 = "SELECT * FROM tbl1 WHERE lastname Is Null AND casenum Is Null AND ticketnum Is Null AND dob Is Null"

strSQL2 = "SELECT * FROM tbl2"
Set db = CurrentDb()
Set rsA = db.OpenRecordset(strSQL1)
Set rsB = db.OpenRecordset(strSQL2)
rsA.MoveFirst
rsB.MoveFirst
With rsA
   Do Until .EOF
      If Not rsB.EOF Then
         .Edit
         rsA!casenum = rsB!cust_casenum
         rsA!ticketnum = rsB!cust_ticketnum
         rsA!dob = rsB!cust_dob
         rsA!lastname = rsB!cust_lastname
         .Update
         rsB.MoveNext
      End If
      .MoveNext
   Loop
End With


End Sub
[\code]