Query Nightmares

[olorin]

I know nothing of SQL, the following code is what appears after I made the query in design view then switched to SQL view; (I named the query "qryProductsShipped")
SELECT DISTINCT tblShipments.Product, tblShipments.NumberOfPallets, tblShipments.PartsPerPallet, [NumberOfPallets]*[PartsPerPallet] AS Shipped
FROM tblShipments
GROUP BY tblShipments.Product, tblShipments.NumberOfPallets, tblShipments.PartsPerPallet

That works fine in as much as it gives me totals shipped for each ProductID.
So if a product has shipped a dozen times, it will appear 12 times in the Datasheet View along with the other shipped products.
I then did this;
SELECT qryProductsShipped.Product, Sum(qryProductsShipped.shipped) AS SumOfshipped
FROM qryProductsShipped
GROUP BY qryProductsShipped.Product;

Which is even better, as it then Totals all shipments per product and each ProductID has the total shipped alongside it.

My Problem (and has been for the past week), is how to get the results from the second query ("qryShippedProducts.SumOfShipped") to update to a particular field named "NumShipped" in "tblProducts".

I have tried Inner joins Outer Joins Right Joins Left Joins until I am blue in the face. All to no avail.

Your guidance in this is greatly needed and any advice much appreciated.

If my table design would help in your response, you may view a jpeg of it here

 

[Banana]

I don't think you want a calculated value in your table...

Allen Browne's take on the Inventory

If that wasn't what you are trying, explain more what you want to accomplish.

 

[olorin]

I see now that I was trying to store a calculated result. Silly me.
Being new to this I am trying to get the current Product Stock figures after Adding "tblProducts.LMStocks" to the sum of "tblProduction.Quantity" (per Product) and then Subtracting "tblShipments.NumOfPallets" x "tblShipments.PartsPerPallet" per Product.
I will look at the link you kindly included in your last post.
Thanx for your time

 

[olorin]

Having studied the link that you kindly included in your last post, I will have to do some serious thinking as to substituting the tables Allen Browne has and mine.
Also, do you think I should change the field "Product" in the "tblShipments" to a foriegn key linking it to "tblProducts.pkProductID" ?
I already have it linked using a combo box on the input form so that it finds the Product from the Products table and is bound to column 1 so that only the Primary Key for the product shows in the Shipments table.
Is this enough? Or should I make it a link with referential integrity?
Would this help with calculations?
I presume it's best to show Current Product Stocks on a report.
I'm very new at this and any pointers would be appreciated.
Thanx

I have also re-jigged the design of my table structure so that it may be easier to understand.
You may view it here