HELP: how do you get a query to add properly

  • Thread starter Thread starter Stressed_fi
  • Start date Start date
S

Stressed_fi

Hey, I was given a formula on how to add everything together, and it is
working it out. but say if I have 3 different customers ordering 10 of each
product so in total its 30, it it reaches the stock limit show below of 30.
The query is working out 10 and takin it away from 30 for each one insted ov
grouping it together

[Order number] [ProductNo] [QuanitityOrdered] [QtyInStock] [ReOrder]
.1 .3 .10
.30 .10
.2 .3 .10
.30 .10
.3 .3 .10
.30 .10
.4 .3 .5
.30

The query is recognising these as seperate enitities insted of one product.
Even if i put a different number in example if a customer orders 5 items
from product no. 3 it would remove the 5 from the qtyinstock but wouldd group
it with the other 10.
Do you know how to get the query to update? or to stop this from happening?

Thank you
 
Hey, I was given a formula on how to add everything together, and it is
working it out. but say if I have 3 different customers ordering 10 of each
product so in total its 30, it it reaches the stock limit show below of 30.
The query is working out 10 and takin it away from 30 for each one insted ov
grouping it together

[Order number] [ProductNo] [QuanitityOrdered] [QtyInStock] [ReOrder]
.1 .3 .10
.30 .10
.2 .3 .10
.30 .10
.3 .3 .10
.30 .10
.4 .3 .5
.30

The query is recognising these as seperate enitities insted of one product.
Even if i put a different number in example if a customer orders 5 items
from product no. 3 it would remove the 5 from the qtyinstock but wouldd group
it with the other 10.
Do you know how to get the query to update? or to stop this from happening?

Thank you
Click to expand...

Correct the error in your query.

We'll be glad to help you, but to do so we'll need to know what query you're
using. Please open it in SQL view and post the SQL text here.
 
SELECT Product.ProductID, Product.Title, Product.QtyInStock,
OrderLine.Quantity, Product.ReOrderLevel, Min([QtyInStock]-[Quantity]) AS
TotalStock, ([TotalStock]<=10) AS ReOrder
FROM Product INNER JOIN OrderLine ON Product.ProductID=OrderLine.ProductID
GROUP BY Product.ProductID, Product.Title, Product.QtyInStock,
OrderLine.Quantity, Product.ReOrderLevel;
 
Hey, I was given a formula on how to add everything together, and it is
working it out. but say if I have 3 different customers ordering 10 of each
product so in total its 30, it it reaches the stock limit show below of 30.
The query is working out 10 and takin it away from 30 for each one insted ov
grouping it together

[Order number] [ProductNo] [QuanitityOrdered] [QtyInStock] [ReOrder]
.1 .3 .10
.30 .10
.2 .3 .10
.30 .10
.3 .3 .10
.30 .10
.4 .3 .5
.30

The query is recognising these as seperate enitities insted of one product.
Even if i put a different number in example if a customer orders 5 items
from product no. 3 it would remove the 5 from the qtyinstock but wouldd group
it with the other 10.
Do you know how to get the query to update? or to stop this from happening?

Thank you
Click to expand...

Your query is:

SELECT Product.ProductID, Product.Title, Product.QtyInStock,
OrderLine.Quantity, Product.ReOrderLevel, Min([QtyInStock]-[Quantity]) AS
TotalStock, ([TotalStock]<=10) AS ReOrder
FROM Product INNER JOIN OrderLine ON Product.ProductID=OrderLine.ProductID
GROUP BY Product.ProductID, Product.Title, Product.QtyInStock,
OrderLine.Quantity, Product.ReOrderLevel;

This doesn't "update" anything, nor should it. You're Grouping by all the
fields, so naturally it gives you every row. Try taking out the group by the
OrderLine.Quantity and sum it instead:

SELECT Product.ProductID, Product.Title, Product.QtyInStock,
Sum(OrderLine.Quantity), Product.ReOrderLevel, Min([QtyInStock]-[Quantity]) AS
TotalStock, ([TotalStock]<=10) AS ReOrder
FROM Product INNER JOIN OrderLine ON Product.ProductID=OrderLine.ProductID
GROUP BY Product.ProductID, Product.Title, Product.QtyInStock,
Product.ReOrderLevel;

I'm not certain that's what you want, your question was a bit confusing (or I
need more coffee... now that's a good idea...)
 
Because you are storing the QtyInStock per product in the Product table you
will need to include some mechanism for updating this value both when
products are removed from stock and added to stock, in the former case by
reducing the QtyInStock value, in the latter by increasing it. Taking the
former, this can be done in the AfterInsert event procedure of a form or
subform bound to the OrderLine table:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE OderLine " & _
"SET QtyInStock = QtyInStock – " & Me.Quantity & _
" WHERE ProductID = " & Me.productID

cmd.CommandText = strSQL
cmd.Execute

You'd do similarly when stock is received, but add rather than subtract the
quantity.

However, this takes no account of a sales order or purchase order being
amended or deleted, so to cover all bases you'd need to also update the
product table in those situations.

Even if you do this there is still redundancy in the table as the QtyInStock
per product can at any time be computed from the sales and purchase order
data; it’s the sum of quantities received less the sum of quantities removed
from stock per product. To remove the redundancy and guard against
inconsistent data therefore, it would be better to compute the stock in hand
in this way rather than to store it in a column in the Product table. You
would of course need to include stock written off or returned as well as
simple purchases and sales.

Ken Sheridan
Stafford, England
 
Back
Top