I am having trouble trying to update a field based in a table based on the
results from another table.

I want to update a tables field based on the results of a view using
another field as the join.

VIEW1 contains partnumber and orderqty (this is a grouped result from
another table)
TABLE1 contains partnumber and onorderqty

I would like to update TABLE1.onorderqty to TABLE1.onorderqty +
VIEW1.orderqty

hopefully in VB so I can run it on a form close button.
But SQL Server does not seem to like 2 tables in an update query.
Can any one help ?

Liam

"Liam" <lm2000uk (AT) hotmail (DOT) com> wrote in
news:bg3cc9$dnt$1 (AT) titan (DOT) btinternet.com:

A simple way would be to include onorderquery in View1.

Then one can update View1 setting onorderquery = onorderquery +
orderquery.

Views in SQL-Server are essentially Tables comprised of (maybe filtered)
columns from other persistent tables. As such they are powerful and
dangerous too.

Updating the view will update the table.

As I've mentioned before, this somewhat erodes the whole notion of table
as a collection of intrinsically related data. The relationships among
columns can now be quite arbitrary, and meaningful or meaningless; the
location of the columns as far as tables go has become or is becoming
irrelevant.

--
Lyle

"Lyle Fairfield" <lylefair (AT) yahoo (DOT) com> wrote

----
Sorry you lost me there. I have come over from normal Access and need to
recreate the program in SQL server.

View1 =
SELECT dbo.TBLStock.PartNumber, SUM(dbo.TBLPurchaseOrderLines.POQty) AS
POQty
FROM dbo.TBLPurchaseOrderLines INNER JOIN
dbo.TBLStock ON dbo.TBLPurchaseOrderLines.Partnumber =
dbo.TBLStock.PartNumber
WHERE (dbo.TBLPurchaseOrderLines.POTransfered = 0)
GROUP BY dbo.TBLStock.PartNumber, dbo.TBLPurchaseOrderLines.POTransfered

This groups together the qty's from a purchase order table, but I then need
to transfer that value back into TBLStock.onorderqty + TBLStock.onorderqty

If The SQL wizard for Update would allow 2 tables and a join I would have no
problem but for some reason it doesn't.


Regards

Liam

"John Winterbottom" <john_winterbottom (AT) hotmail (DOT) com> wrote


TBLPurchaseOrderLines
=========================
¦ PartNumber ¦ OrderQty ¦
=========================
¦ ABC123 ¦ 10 ¦
-------------------------
¦ DEF456 ¦ 20 ¦
-------------------------
¦ ABC123 ¦ 30 ¦
=========================

Table is then grouped to sum up the quantities per partnumber

ViewPOLNTGrouped
=========================
¦ PartNumber ¦ OrderQty ¦
=========================
¦ ABC123 ¦ 40 ¦
-------------------------
¦ DEF456 ¦ 20 ¦
=========================

Then I would like it to add those sums for each partnumber to the current
figure in the stock table

TBLStock
=================================================
¦ PartNumber ¦ OnOrderQty ¦
=================================================
¦ ABC123 ¦ 10 + ViewPOLNTGrouped.OrderQty ¦
-------------------------------------------------
¦ DEF456 ¦ 05 + ViewPOLNTGrouped.OrderQty ¦
=================================================


I was hoping to do it from a RUNSQL statement in the Close Button on the
Form or call it from a stored procedure but I don't know what a stored
procedure is or how to use one.
Please help as I have been struggling for a week now and I can find little
reference to update queries on the net.


Regards

Liam

"Liam" <lm2000uk (AT) hotmail (DOT) com> wrote

I normally use ADO. Search http://msdn.microsoft.com for examples