Proper use of update query

A

Access Novice

Based on "Northwind" database.

I am trying to create an update query as follows:
I added a field to the customers table, and named it "Status".

I wish to update this filed according to each customer's total order. I
created a totals query which calculates the total orders per customer (the
SUM of unitprice*quntity from order details table).

I wish that for all customers which their total order is above 200,000 the
status will be updated to "VIP"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "Very good customer"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "good customer"
All others will be updated to "Normal".

I did as follows: I created a select query (names 'temp') which calculates
the total orders per customerID.
Based on this 'temp' query I added another query based on the 'customers'
table and 'temp' query. I added only the 'status' field and wrote in the
update to field the following sentence
IIf([temp]![Expr1]<=25000,"Normal",IIf([temp]![Expr1]<=50000,"good
customer",IIf([temp]![Expr1]<=75000,"very goog customer","VIP")))

I always get the message "operation must use an updateable query"

What did I do wrong?

Thanks for your time.
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Oh, frapjous joy! Isn't it great the way Access/JET is so stupid about
subqueries in the SET clause of UPDATEs. Anywho.... Here's how you
have to do it - one at a time! Or, you could run your "temp" query as a
make table query & then run the UPDATE against the new table, using it
as if it were the temp query.

One at a time UPDATEs:

UPDATE Customers
SET Status = "Normal"
WHERE CustomerID IN
(SELECT O.CustomerID
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY O.CustomerID
HAVING Sum(OD.UnitPrice*OD.Quantity)<=25000)

UPDATE Customers
SET Status = "Good"
WHERE CustomerID IN
(SELECT O.CustomerID
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY O.CustomerID
HAVING Sum(OD.UnitPrice*OD.Quantity) > 25000
AND Sum(OD.UnitPrice*OD.Quantity) <= 50000)

.... etc. ...

Make table query:

SELECT CustomerID,
IIf(Sum(OD.UnitPrice*OD.Quantity)<=25000,"Normal",
IIf(Sum(OD.UnitPrice*OD.Quantity)<=50000,"Good",
IIf(Sum(OD.UnitPrice*OD.Quantity)<=75000,"Very Good","VIP")))
As Status
INTO tmpStatus
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY CustomerID

Then the UPDATE would be like this:

UPDATE Customers INNER JOIN tmpStatus
ON Customers.CustomerID = tmpStatus.CustomerID
SET Customers.Status = tmpStatus.Status
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQIuoechKqOuFEgEQJDKgCg+wcG47EVs6lAMfnW0YEqB5vOJhsAn1tW
yva/jP6DSwT1z9S0JtFpVvZi
=j5/t
-----END PGP SIGNATURE-----

Access said:
Based on "Northwind" database.

I am trying to create an update query as follows:
I added a field to the customers table, and named it "Status".

I wish to update this filed according to each customer's total order. I
created a totals query which calculates the total orders per customer (the
SUM of unitprice*quntity from order details table).

I wish that for all customers which their total order is above 200,000 the
status will be updated to "VIP"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "Very good customer"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "good customer"
All others will be updated to "Normal".

I did as follows: I created a select query (names 'temp') which calculates
the total orders per customerID.
Based on this 'temp' query I added another query based on the 'customers'
table and 'temp' query. I added only the 'status' field and wrote in the
update to field the following sentence
IIf([temp]![Expr1]<=25000,"Normal",IIf([temp]![Expr1]<=50000,"good
customer",IIf([temp]![Expr1]<=75000,"very goog customer","VIP")))

I always get the message "operation must use an updateable query"

What did I do wrong?

Thanks for your time.
Click to expand...
 
A

Access Novice

Work like a charm.

I use it "one at a time".

I didn't know I should run it that way.

Many Thanks for your help.

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Oh, frapjous joy! Isn't it great the way Access/JET is so stupid about
subqueries in the SET clause of UPDATEs. Anywho.... Here's how you
have to do it - one at a time! Or, you could run your "temp" query as a
make table query & then run the UPDATE against the new table, using it
as if it were the temp query.

One at a time UPDATEs:

UPDATE Customers
SET Status = "Normal"
WHERE CustomerID IN
(SELECT O.CustomerID
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY O.CustomerID
HAVING Sum(OD.UnitPrice*OD.Quantity)<=25000)

UPDATE Customers
SET Status = "Good"
WHERE CustomerID IN
(SELECT O.CustomerID
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY O.CustomerID
HAVING Sum(OD.UnitPrice*OD.Quantity) > 25000
AND Sum(OD.UnitPrice*OD.Quantity) <= 50000)

... etc. ...

Make table query:

SELECT CustomerID,
IIf(Sum(OD.UnitPrice*OD.Quantity)<=25000,"Normal",
IIf(Sum(OD.UnitPrice*OD.Quantity)<=50000,"Good",
IIf(Sum(OD.UnitPrice*OD.Quantity)<=75000,"Very Good","VIP")))
As Status
INTO tmpStatus
FROM Orders As O INNER JOIN [Order Details] As OD
ON O.OrderID = OD.OrderID
GROUP BY CustomerID

Then the UPDATE would be like this:

UPDATE Customers INNER JOIN tmpStatus
ON Customers.CustomerID = tmpStatus.CustomerID
SET Customers.Status = tmpStatus.Status
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQIuoechKqOuFEgEQJDKgCg+wcG47EVs6lAMfnW0YEqB5vOJhsAn1tW
yva/jP6DSwT1z9S0JtFpVvZi
=j5/t
-----END PGP SIGNATURE-----

Access said:
Based on "Northwind" database.

I am trying to create an update query as follows:
I added a field to the customers table, and named it "Status".

I wish to update this filed according to each customer's total order. I
created a totals query which calculates the total orders per customer
(the SUM of unitprice*quntity from order details table).

I wish that for all customers which their total order is above 200,000
the status will be updated to "VIP"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "Very good customer"
All customers which their total order is between 100,000 & 200,000 the
status will be updated to "good customer"
All others will be updated to "Normal".

I did as follows: I created a select query (names 'temp') which
calculates the total orders per customerID.
Based on this 'temp' query I added another query based on the 'customers'
table and 'temp' query. I added only the 'status' field and wrote in the
update to field the following sentence
IIf([temp]![Expr1]<=25000,"Normal",IIf([temp]![Expr1]<=50000,"good
customer",IIf([temp]![Expr1]<=75000,"very goog customer","VIP")))

I always get the message "operation must use an updateable query"

What did I do wrong?

Thanks for your time.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query to see only first several records 1
Sum up numbers 4
Update Query ? 1
Query concatenation help 1
Query Help Request 2
Sort order by customer name not working in query 5
Crosstab query help please 1
How create ranking within subgroup 4

Top