Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help needed inserting values from one table into another 1

Status
Not open for further replies.

teach314

Technical User
Jul 29, 2011
183
CA
hi to all

Im having trouble writing some code, and I'm hoping to get some advice. Here's the situaltion...

I have tblP and tblQ.

In tblP, each PID holds 6 records having PRow from 1 to 6. The PVal will always have three 1s and three 0s, in varying orders.

In tblQ, each QID holds 3 records having QRow from 1 to 3. The QVal will be three different integers.

Code:
tblP                                     tblQ

PID   PRow   PVal   Result               QID   QRow  QVal
==========================               ================ 
 1     1      1                           1     1     8 
 1     2      0                           1     2    60
 1     3      0                           1     3    -7 
 1     4      1
 1     5      0                           2     1    12 
 1     6      1                           2     2    -4
                                          2     3    99
 2     1      0
 2     2      1                           ... etc
 2     3      0
 2     4      1
 2     5      1
 2     6      0

... etc

I need to INSERT the QVal numbers into the Result column of tblP WHERE PID = QID, and PVal = 0.
The order of inserting the QVal numbers is important - the same order must be kept.

The desired outut would look like...

Code:
tblP                      

PID   PRow   PVal   Result
========================== 
 1     1      1
 1     2      0       8
 1     3      0      60
 1     4      1
 1     5      0      -7 
 1     6      1
 
 2     1      0      12
 2     2      1 
 2     3      0      -4
 2     4      1   
 2     5      1
 2     6      0      99

... etc


Thanks in advance for any hints!
 
I would expect this might be best accomplished by creating a couple recordsets in vba. Then step through each of them and pull the value from one and update the other. The following code worked for me with your data sample.

Code:
Sub UpdateQ2P()
    Dim db As DAO.Database
    Dim rsP As DAO.Recordset
    Dim rsQ As DAO.Recordset
    Dim strSQLP As String
    Dim strSQLQ As String
    strSQLP = "SELECT PID, PRow, PVal, Result FROM tblP WHERE pVal = 0 ORDER BY PID, PROW"
    strSQLQ = "SELECT QID, QRow, QVal FROM tblQ ORDER BY QID, QRow"
    Set db = CurrentDb
    Set rsP = db.OpenRecordset(strSQLP)
    Set rsQ = db.OpenRecordset(strSQLQ)
    rsP.MoveFirst
    rsQ.MoveFirst
    Do Until rsP.EOF
        Do Until rsQ!QID = rsP!PID
            rsQ.MoveNext
        Loop
        If Not rsQ.EOF Then
            rsP.Edit
                rsP!result = rsQ!Qval
            rsP.Update
        End If
        rsP.MoveNext
        rsQ.MoveNext
    Loop
    rsP.Close
    rsQ.Close
    Set rsP = Nothing
    Set rsQ = Nothing
    MsgBox "Updates Complete"
End Sub


Duane
Hook'D on Access
MS Access MVP
 
I'd been trying to do this in SQL, but your VBA solution works really well. Thanks for the assist!
Teach314
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top