Update Query Help

[Mothra]

I'm trying to create an update query to update a cost field using values from another table. I can't get this to run, keep getting the 'must use updatable query error'. I must be missing something, but it looks fine to me?? If anyone is wondering the sub-select is to grab the latest entered cost for each item. Any help/suggestiosn would be appreciated. This is MS Access 2003.


UPDATE po_dpart a
SET a.cost = (SELECT b.cost
FROM dpart_cost b
WHERE b.date_set =
(SELECT MAX(b1.date_set)
FROM dpart_cost b1
WHERE b1.date_set <= #6/30/05#
AND b1.part_number = a.part_number
AND b1.supplierID = 'pas'
)
AND b.part_number = a.part_number
AND b.supplierID = 'pas'
)
WHERE EXISTS
(SELECT c.cost
FROM dpart_cost c
WHERE c.date_set =
(SELECT MAX(c1.date_set)
FROM dpart_cost c1
WHERE c1.date_set <= #6/30/05#
AND c1.part_number = a.part_number
AND c1.supplierID = 'pas'
)
AND c.part_number = a.part_number
AND c.supplierID = 'pas'
)
AND a.project_number = 100
AND a.po_number = 9003;

[hspc]

For clarification :
1-Do you run this from ASP for example or from the MS Access application?
2-Do you have problems with other update/insert statements .. or just this one ?

[Mothra]

I've somewhat moved past this (or avoided it anyway) but want to bump it back up. I think the problem is that Access will not update tables that are not related. Anyone know of a workaround?

hspc:
1) MS Access
2) No other problems, except the issue mentioned above.


Thanks.

[jp140768]

How many records does the second query produce? You will have a problem if it returns more than one.

I have done this, but in a slightly different format eg:
UPDATE table1 A
INNER JOIN table2 B
ON A.? = B.?
SET A.? = B.?

You can add in a where statement if required.

I ran into a problem doing this in Access where table2 resided on another server. I had a user send me an excel spreadsheet, containing a list of accounts that he wanted me to extract information from our AS/400 on. I set up a passthrough query to the AS/400, and tried the above statement and got the message you are getting. I got round this by creating a table from the passthrough query.

HTH