Help with an update query (1 Viewer)

[ewan97]

Ok so I'm not really sure how to do what I want to do, I'm not sure what to google to find out how to do it.

I want to update values in one table using values in another table. The table I want to be updated has a stock value for each product, I have a query that pulls the records from the most recent order from a composite key table.

The records I want to use to update the StockNumber values have a ProductID that corresponds to the ProductIDs in the table to be updated as well as a Quantity value that needs to be taken away from the StockNumber

UPDATE [tblqryInvoice-Product] 
INNER JOIN Products ON ([tblqryInvoice-Product].ProductName = Products.ProductID) AND ([tblqryInvoice-Product].Quantity = Products.StockNumber) 
SET Products.StockNumber =[StockNumber]-[Quantity];

This update sql bit that I managed to gleam from a tutorial website and somewhat adapt produces the message "you are about to update 0 records" when I run the query

 

[ewan97]

These are screenshots of the tables and query design in question.

And please before anyone says it I know you aren't meant to store a stocknumber value with a product and it should instead be caluculated but this is the way we have been told to do it for our school project and i have several deadlines for my already overdue project.

 

[CJ_London]

why are you linking stocknumber to quantity? these would have to be the same to return any records so you might as well have

SET Products.StockNumber 0;

 

[ewan97]

I thought that's what I had to do because I want to take the Quantity value away from StockNumber but clearly I'm wrong.
How can I take the quantity value from the stocknumber value? That's my ultimate goal here.

 

[RainLover]

In your third pic it shows that you are trying to take Quantity away from StockNumber.

You have both of these fields in the table Products. Quantity does not exist in that table so that will never work.

 

[ewan97]

So is there a different way I can do this that just used the values in the records without the field names needing to be the same?

 

[RainLover]

I assume ProductID is AutoNumber. So you need a field in the other Table or query that matches.

This becomes your join. Forget the other one. It does nothing and nor should it.

Get your naming conventions right. For Products it should be tblProducts and the other is either prefixed with tbl or qry. I have no idea which one it is. This is why we use prefixes.

 

[CJ_London]

you have productname linked to productid - which implies you are using lookup fields in your table where it is actually a number but you see the name so you call it productname - just another thing to make it confusing for you.

Try removing the link between stocknumber and quantity and perhaps it will work

 

[RainLover]

Create an Alais or two.

myQuantity:Quantity and myStockNumber:StockNumber.

Then it becomes myStockNumber - myQuantity.

No confusion this way.

Place those new fields in the first row.

Then in a new row give it a name like Result and myStockNumber - myQuantity. in the line UpdateTo

 

[ewan97]

Ok so I tried messing around with the links a bit and it turns out the only one I did need was between ProductName and ProductID. It worked and I couldn't be happier to finally get this sorted.

 

[RainLover]

ewan

There is a Document in our archives that is called Naming Conventions. Suggest you read it a couple of times. Sing out if you don't understand.

 

[RainLover]

Ok so I tried messing around with the links a bit and it turns out the only one I did need was between ProductName and ProductID. It worked and I couldn't be happier to finally get this sorted.

ewan

Have you tested to make sure all is good.

If it is good then best wishes with the balance of the project.

If you run into problems you may decide to open a new Thread.