an updatequery to update stock amounts

[sybmathics]

Hi,

I'm trying to create an updatequery that will reset the quantity of articles
in stock based on the last order.

So, I have the following tables:
tblarticles: ArticleID (key), Description, Stock
tblCustomers: CustomerID (key), Name, Location
tblOrders: OrderID (key), OrderDate, CustomerID
tblOrderLines: OrderID (key), ArticleId (key), Quantity

I created a query that gives the latest OrderID
I use this query to create a new query with OrderID, ArticleID and Quantity
(qryOrderedInLastOrder)


Now I want to create an updatequery using the tblArticles and
qryOrderedInLastOrder
I have added Stock and want it te be updated to Stock -
qryOrderdInLastOrder!Quantity.
However, I get warning message "operation must use an updateable query".
I don't see what I'm doing wrong.
Please help.

Sybmathics

 

[Douglas J. Steele]

What have you done in terms of making it an Update query? What does the SQL
look like? (If you're not familiar with how to get to the SQL associated
with a query, look under the View menu when you've got the query open, and
you'll find SQL View as one of the choices. Select that, then copy and paste
the query in your response)

 

[sybmathics]

:

What have you done in terms of making it an Update query? What does the
SQL look like?

Doug,

The sql statement to produce only the last orderID reads like this:
SELECT Max(tblOrderlines.OrderID) AS LastOrderID
FROM tblOrderlines;

The statement to produce articleNr and current stock reads like this:
SELECT tblOrderlines.OrderID, tblOrderlines.Quantity, tblArticles.articleId
FROM tblArticles INNER JOIN (qryLastOrderID INNER JOIN tblOrderlines ON
qryLastOrderID.LastOrderID=tblOrderlines.OrderID) ON
tblArticles.articleId=tblOrderlines.ArticleID;

The update query then uses tblArticle and abovementioned query like this:
UPDATE tblArticles INNER JOIN qryQuantityInLastOrder ON
tblArticles.articleId=qryQuantityInLastOrder.articleId SET tblArticles.stock
= tblArticles!stock-qryQuantityInLastOrder!Quantity;


This results in a warning message "operation must use an updatable query".

Hopefully the message makes more sense to you thanit does to me.

Greets,

Sybmathics

 

[Douglas J. Steele]

I suspect there's something about the join between tblArticles and
qryQuantityInLastOrder that's resulting in the query not allowing updates.

Take a look at
http://office.microsoft.com/en-us/assistance/HP051880011033.aspx or
http://msdn.microsoft.com/library/en-us/off2000/html/acconDeterminingWhenCanUpdateDataQueryS.asp

 

[sybmathics]

I suspect there's something about the join between tblArticles and
qryQuantityInLastOrder that's resulting in the query not allowing updates.

Doug,

I tried all sorts of other joins also. The sql in previous message was just
the last one I could think of.
However, all other constructions led to the same error message.

I' ve now reconstruted the whole business using a tablemake-query as
go-between.
Then the update to the value in the newlycreated table seems to work wuite
well.
It takes some 4 ro 5 clicking OK-buttons, however.

I suppose writing vba-code could take care of that, but I'm no good at VBA
for Access.

I'll have a look at your suggesed hyperlink.

Thanks,

Sybmathtics