• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

Problem with MS Access Update query

I am trying to create a query in MS Access that will update my PAYSHEET table with data from a table called
"CallCenterPaymentPendingUpdate_Paysheet_Source#".  Both tables have a multiple column PK set consisting of paysheet.techID & Paysheet.Region & Paysheet.PaysheetTypeNumber &  Paysheet.ReportDate & Paysheet.ItemNumber.

I know that the "HAVING IN SELECT" or "WHERE IN SELECT" typically users just one field to match on, but I need to link them on the combined PK  so I concatinated the fileds like so:  paysheet.techID & Paysheet.Region & Paysheet.PaysheetTypeNumber &  Paysheet.ReportDate & Paysheet.ItemNumber.

Because I need to link two tables I keep getting the error "Operation must use an updatable query".  SO I rewrite it using a correlated subquery.  The closest I can get to solving this problem is this query:

UPDATE Paysheet
SET Amount = [paysheet].[Amount]+[CallCenterPaymentPendingUpdate_Paysheet_Source#].[NumPayments]
WHERE
cstr(paysheet.techID & Paysheet.Region & Paysheet.PaysheetTypeNumber &  Paysheet.ReportDate & Paysheet.ItemNumber)
in
(
SELECT cstr(paysheet.techID & Paysheet.Region & Paysheet.PaysheetTypeNumber &  Paysheet.ReportDate & Paysheet.ItemNumber)
FROM
paysheet INNER JOIN  [CallCenterPaymentPendingUpdate_Paysheet_Source#]  ON [CallCenterPaymentPendingUpdate_Paysheet_Source#].TechID =
Paysheet.TechID
AND   [CallCenterPaymentPendingUpdate_Paysheet_Source#].Region = Paysheet.Region
AND   [CallCenterPaymentPendingUpdate_Paysheet_Source#].PaysheetTypeNumber = Paysheet.PaysheetTypeNumber
AND   [CallCenterPaymentPendingUpdate_Paysheet_Source#].ReportDate = Paysheet.ReportDate
AND   [CallCenterPaymentPendingUpdate_Paysheet_Source#].ItemNumber = Paysheet.ItemNumber)

Which gives me an prompt box because it does not know what [CallCenterPaymentPendingUpdate_Paysheet_Source#].[NumPayments] is.

So I tried this query:

UPDATE Paysheet INNER JOIN [CallCenterPaymentPendingUpdate_Paysheet_Source#] ON (Paysheet.Region =
[CallCenterPaymentPendingUpdate_Paysheet_Source#].Region) AND (Paysheet.PaysheetTypeNumber =
[CallCenterPaymentPendingUpdate_Paysheet_Source#].PaysheetTypeNumber) AND (Paysheet.TechID =
[CallCenterPaymentPendingUpdate_Paysheet_Source#].TechID) AND (Paysheet.ReportDate =
[CallCenterPaymentPendingUpdate_Paysheet_Source#].ReportDate) AND (Paysheet.ItemNumber =
[CallCenterPaymentPendingUpdate_Paysheet_Source#].ItemNumber) SET Paysheet.Amount =
[paysheet].[Amount]+DLookUp("NumPayments","CallCenterPaymentPendingUpdate_Paysheet_Source#","Region = " & Paysheet.Region & " AND
techID = " & Paysheet.techID & "  AND PaysheetTypeNumber = " & Paysheet.PaysheetTypeNumber & " AND ReportDate = " &
Paysheet.ReportDate & " AND ItemNumber = " & Paysheet.ItemNumber);

But I get the error "Operation must use an updatable query" again.

Help!
0
cef_soothsayer
Asked:
cef_soothsayer
  • 3
  • 2
1 Solution
 
Carl2002Commented:
Have the data you want to update in a stand alone table, this should fix the error.

Carl.
0
 
cef_soothsayerAuthor Commented:
Carl,  Please explain further...

The purpose here is to update the PAYMENTS table with data from a newly imported table.
This will be done daily so as to keep the payemnts table up to date with each new import.
I do not have the option of altering the PK on the payments table either.
0
 
cef_soothsayerAuthor Commented:

I think I just fixed this...

CallCenterPaymentPendingUpdate_Paysheet_Source# is not really a table - it is a query.  And it contains a count aggregate which is by definition not updatable in access, even if the aggregate is used in a subquery.



0
 
cef_soothsayerAuthor Commented:
Since Carl's comment led me to think in the right direction I'll award him the points...
0
 
Carl2002Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now