Coding an update query

UPDATE qryPayments_Hdr_MatchAgainst_ActiveInstallPlans SET qryPayments_Hdr_MatchAgainst_ActiveInstallPlans.PaymentInstallPlanID = [InstallPlanID];

Select allOpen in new window

you need to know the logic exactly and provide tables structures
and show us the data before and after the update
so we can think of a solution if it is possible using just one query...

It's not possible to provide useful help with this little information and no data.

You should open a project in Gigs.

/gustav

Sorry for the confusion.  As I read my question now I can see the issue.

Here’s more detail, these are the pertinent fields in each table

tblPayments_Hdr
ID                    Primary Key
InstallPlanID      Long Integer – If This payment does not match an install Plan this field will be zero, otherwise it will contain the unique     primary  key of the install plan record it is matched to.


tblInstallPay_Main
ID            Primary key

The previous developer has already coded a query matching the payments to the install plans, creating a recordset with the following information.  I am not looking to replace the existing query.

Recordset returned from existing query.  
ID            this is the Primary key tblPayment_Hdrt of the payment record that has been matched to an install plan in the preceding query.
PaymentInstalPlanID      this the corresponding field ‘InstallPlanID’ from tblPayments_Hrd.  In this result set this field will always be zero.  
InstallPlanID                     This is the ID of the install plan matched to this payment record by the preceding query

All records in this recordset have been matched to an install plan in the preceding query.  In this recordset the value of ‘PaymentINstallPlanID’ is always zero and’InstallPLanID’ contains the ID of the InstallPlan this payment has been matched to.

Example of returned records
ID (Primatykey of tblPayments_Hdr)            PaymentInstallPlanID (InstallPlanID from tblPayments_Hdr)            InstallPLanID (ID primary key of tnlInstallPay_Main)
123                                                                       0                                                                                                                        456
234                                                                       0                                                                                                                        789
345                                                                       0                                                                                                                        965

After the existing logic is executed the values in tblPayments_Hdr would look like this
ID            InstallPlanID
123            456
234            789
345            965

For reference this is the current logic executed against the result set to update the 'InstallPLanID' in tblPayments_Hdr.

'
selectString = " Select [ID], [InstallPlanID]  from qryPayments_Hdr_MatchAgainst_ActiveInstallPlans "  ' brings in any payments not on an isntall plan
'
'
Dim db As DAO.Database
Set db = getCurrentDbC
'
Dim rsUpdatePayHDR As DAO.Recordset
'
Dim rsin2 As DAO.Recordset
Set rsin2 = db.OpenRecordset(selectString, dbOpenSnapshot, dbReadOnly)  ''''///////  Read Only ///////

'
If rsin2.EOF Then
    '
Else
    If rsin2.RecordCount > 0 Then
        '
        rsin2.MoveLast
        rsin2.MoveFirst
        '
        While Not rsin2.EOF
            '
            wkInstallPayID = Nz(rsin2!InstallPlanID, 0)
            wkPayHDrID = Nz(rsin2!ID, 0)
            '
            If wkInstallPayID > 0 Then
                '
                selectString = " Select [InstallPlanID] From tblPayments_Hdr Where [ID] = " & wkPayHDrID
                '
                Set rsUpdatePayHDR = db.OpenRecordset(selectString, dbOpenDynaset, dbSeeChanges)
                '
                If rsUpdatePayHDR.EOF Then
                    '
                Else
                    '
                        rsUpdatePayHDR.Edit
                        '
                        rsUpdatePayHDR!InstallPlanID = wkInstallPayID
                        '
                        rsUpdatePayHDR.Update
                        '
                        rsUpdatePayHDR.MoveNext
                End If
                '
                rsUpdatePayHDR.Close
                DBEngine.Idle dbRefreshCache
                '

              End If
            '
            'rsIn2.Update
            '
            rsin2.MoveNext
        Wend
            
    End If
End If
'
rsin2.Close
Set rsin2 = Nothing

Select allOpen in new window

that whole code seems to be doing just this

UPDATE tblPayments_Hdr as p
 INNER JOIN qryPayments_Hdr_MatchAgainst_ActiveInstallPlans as ap ON ap.ID = p.ID
   SET p.InstallPlanID = Nz(ap.InstallPlanID, 0);

Select allOpen in new window

When you name all your primary keys with the same name - ID, it is difficult for others to actually see the relationships without a relationship diagram.

Pat: Yes I agree it's bad practice to have all of the Primary Keys named ID.  I inherited this and am trying to get it to execute more quickly.

HainKurt:  I agree that your SQL looks like it should work but after running the SQL all of the 'InstallPlanID' fields in tblPayments_Hdr are still equal to zero.

Here's some output from the preceding query  “qryPayments_Hdr_MatchAgainst_ActiveInstallPlans"


All 47,000 records have a valid InstallPlanID from tblInstallPay_Main

However, when I run your SQL none of the InstallPlanID's in tblPayments_Hdr get updated.

I entered your query into the Access query designer but rather than Run (!) it, I just clicked 'View'.  Here's what it looked like:


47000 rows with zero in InstallPlanID.  I don't understand why though.  Do you have any ideas?

This is exactly what I am running

UPDATE tblPayments_Hdr AS p INNER JOIN qryPayments_Hdr_MatchAgainst_ActiveInstallPlans AS ap ON p.ID = ap.ID SET p.InstallPlanID = Nz(ap.InstallPlanID,0);

Select allOpen in new window

whats the result of

SELECT top 100 
       p.id, p.InstallPlanID as p_InstallPlanID, ap.InstallPlanID as ap_InstallPlanID
  FROM tblPayments_Hdr AS p
 INNER JOIN qryPayments_Hdr_MatchAgainst_ActiveInstallPlans AS ap ON p.ID = ap.ID

Select allOpen in new window

you are showing tblInstallPay_Main, but we are using qryPayments_Hdr_MatchAgainst_ActiveInstallPlans!!!

you can ignore that view and use tblInstallPay_Main instead

UPDATE tblPayments_Hdr AS p
 INNER JOIN tblInstallPay_Main AS ap ON p.ID = ap.ID
   SET p.InstallPlanID = Nz(ap.InstallPlanID,0)

Select allOpen in new window

I don't see how your latest suggestion

UPDATE tblPayments_Hdr AS p
 INNER JOIN tblInstallPay_Main AS ap ON p.ID = ap.ID
   SET p.InstallPlanID = Nz(ap.InstallPlanID,0)


would work.  You're joining the two different tables on their primary keys.

I think you were on the right track with

UPDATE tblPayments_Hdr AS p INNER JOIN qryPayments_Hdr_MatchAgainst_ActiveInstallPlans AS ap ON p.ID = ap.ID SET p.InstallPlanID = Nz(ap.InstallPlanID,0);

I'm still not sure why that isn't working.



Here is the result of top100 query you requested

qryPayments_Hdr_UpdateInstallPlanID.xlsx

you are showing tblInstallPay_Main

I am not showing 'tblInstallPay_Main.   The second picture above is the result of changing your 'Update' query to a 'Select' query

SELECT p.InstallPlanID
FROM tblPayments_Hdr AS p INNER JOIN qryPayments_Hdr_MatchAgainst_ActiveInstallPlans AS ap ON p.ID = ap.ID;

Thank you very much