"Not Updateable query" error..

[Guest]

Trying to update a table based on another. Getting messsage "Operation must
use an Updateable query. Anyone see anything wrong with this?

Business unit is on both tables..trying to build/update Master table with
2nd table.

UPDATE BUMasterSpend INNER JOIN BUCatSpendTotal ON
BUMasterSpend.BusinessUnit = BUCatSpendTotal.BusinessUnit SET
BUMasterSpend.HardWareSpend = [BUCatSpendTotal]![SumOfSpend]
WHERE [BUCatSpendTotal]![Category]="Hardware";

Thanks!

 

[Duane Hookom]

Is one side of the join a primary key?
Is either BUMasterSpend or BUCatSpendTotal a totals query? Access doesn't
allow you to edit records in a query that contains a totals query. You might
be able to use a domain aggregate function or a subquery.

 

[Guest]

Yes..BusinessUnit is primary key on BUMasterSpend..BUCatSpendTotal is a
totals query.

Do you mean pull the totals query into the Update query as a subquery?

Thanks,
Don

Is one side of the join a primary key?
Is either BUMasterSpend or BUCatSpendTotal a totals query? Access doesn't
allow you to edit records in a query that contains a totals query. You might
be able to use a domain aggregate function or a subquery.

--
Duane Hookom
MS Access MVP
--

Trying to update a table based on another. Getting messsage "Operation
must
use an Updateable query. Anyone see anything wrong with this?

Business unit is on both tables..trying to build/update Master table with
2nd table.

UPDATE BUMasterSpend INNER JOIN BUCatSpendTotal ON
BUMasterSpend.BusinessUnit = BUCatSpendTotal.BusinessUnit SET
BUMasterSpend.HardWareSpend = [BUCatSpendTotal]![SumOfSpend]
WHERE [BUCatSpendTotal]![Category]="Hardware";

Thanks!

 

[Duane Hookom]

Using the Northwind database, this will not work after adding a TotalFreight
field to the Customers table:

UPDATE Customers
SET TotalFreight =
(SELECT SUM(Freight)
FROM Orders
WHERE Orders.CustomerID = Customers.CustomerID);

This did work:

UPDATE Customers
SET TotalFreight =
DSum("Freight","Orders","CustomerID = """ & [CustomerID] & """");

--
Duane Hookom
MS Access MVP
--

Yes..BusinessUnit is primary key on BUMasterSpend..BUCatSpendTotal is a
totals query.

Do you mean pull the totals query into the Update query as a subquery?

Thanks,
Don

Is one side of the join a primary key?
Is either BUMasterSpend or BUCatSpendTotal a totals query? Access doesn't
allow you to edit records in a query that contains a totals query. You
might
be able to use a domain aggregate function or a subquery.

--
Duane Hookom
MS Access MVP
--

Trying to update a table based on another. Getting messsage "Operation
must
use an Updateable query. Anyone see anything wrong with this?

Business unit is on both tables..trying to build/update Master table
with
2nd table.

UPDATE BUMasterSpend INNER JOIN BUCatSpendTotal ON
BUMasterSpend.BusinessUnit = BUCatSpendTotal.BusinessUnit SET
BUMasterSpend.HardWareSpend = [BUCatSpendTotal]![SumOfSpend]
WHERE [BUCatSpendTotal]![Category]="Hardware";

Thanks!