Update table with data from other tabe

[gregpen]

The problem,

For hours I have been trying to update a particular column in a Product table (Quantity). I run a query that groups all sales from the week and appends then to a Weekly Sales Table, I am then trying (everything) to update the quantity column in the Product table so that it represents the current stock level at the end of the week. [Products].[Quantity]-[WeeklySales].[SalesQty].
I have tried to use update queries, append queries but still no go. I am not sure if I am getting the expression right or not. Any type of help / thoughts would be greatly appreciated.

Thank you

 

[mac318]

Place your code in the SQL where you would normally place the field name. Access will add "Exp1:" in front of it. "Exp1" will show up as a field on the form's "List Fields."

mac

 

[gregpen]

I have entered as suggested but still no go. The table I am trying to update (Products) has the following fields - Product, Productcode, Costprice, Quantity, CompanyNum.
The Table I am trying to draw the data from (WeeklySales) to update the Products table has th following fields - Lastdate, Product, Quantity.
The only field that I need to update is the (Quantity) field in the (Products) table, which I assumed would be done via an update query with a simple expression of -

[Products].[Quantity]-[WeeklySales].[Quantity]

I did what was suggested (Thanx mac318), I entered -
[Products].[Quantity]-[WeeklySales].[Quantity] into the field box of the QBE Grid and tried to run the query Access prompted me for a destination so I entered Quantity into the Update To: box on the Grid No Go.

Is my syntax right or should it be something different.

Again thanks mac318 for your input much apprecited.

 

[mac318]

Try this:

Qty:[Products].[Quantity]-[WeeklySales].[Quantity]

Also be sure that your SQL has both tables joined with the appropriate field.

If you get a request for anything, it means that you have mispelled one of the fields. Are you sure that the field is named Quantity in both tables?

mac

 

[gregpen]

Just need a little more info if possible:

Isn't the method you mentioned going to create a new field named Qty.

Should this expression be entered into an update query if so what is the destination and how should it be entered.

Thx again Mac..

 

[tlbroadbent]

Try this query.

Update Products
Inner Join WeeklySales
On Products.Product = WeeklySales.Product
Set Products.Quantity =
Products.Quantity - WeeklySales.Quantity Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[mac318]

If you have a field to accept the resultant, yes, you should use an update query. I misunderstood you from the beginning. After you select update query, there will be an update to line for each field. Place your original formula:
[Products].[Quantity]-[WeeklySales].[Quantity]
on the line that says "Update To" under the field in which you want the result.

If this isn't what you are trying to accomplish, give me another try. I'll stay with you until we get the result you seek.

mac

 

[gregpen]

Sorry one other thing that should be said is that the WeeklySales tables contains data from many different weeks.

All I neeed to update to the Products table are the items that have been sold during the latest date on the Weeklysales table.

The suggested methods want to update all records to the Products table.

Thanx Guys I know we are getting closer thank you very much...

 

[tlbroadbent]

Update Products
Inner Join WeeklySales
On Products.Product = WeeklySales.Product
Set Products.Quantity =
Products.Quantity - WeeklySales.Quantity
Where WeeklySales.Lastdate =
(Select Max(Lastdate) From WeeklySales) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[mac318]

Greg, select SQL view and place Terry's code there just as he typed it. Then look at design view and you can learn how to use the design view to produce complex SQL statements.

mac

 

[gregpen]

Thanks Mac and Terry, your help has been greatly appreciated, It makes it alot easier to understand when laid out the way you guys have thank you again.