Stock levels

[MMcCarthy]

[Hello here are the new results

Qty_in_Stock - 60 for all

SumOfQty_on_Ord er
19
23
22
2
2
7
3
the ORIGINAL "Qty_in_Sto ck" were all 60


"ewlevel" (calculated field)
401
397
398
58
58
73
57

what could be the problem?

Copy and paste the last query I gave you in exactly.

Mary

[NeoPa]

This isn't really a progression as such. I just thought re-presenting the SQL may help to spot any potential problems.

SELECT A.Prod_ID,P.Prod_Name,
       S.SumOfQty_on_Order As QtyOnOrder,
       Sum(P.Qty_in_Stock) As QtyInStock,
       [QtyInStock]-[QtyOnOrder] AS New_Level
FROM (Products AS P
    INNER JOIN [All customer order details] AS A
    ON P.Prod_ID=A.Prod_ID)
    INNER JOIN [stock level Query] AS S
    ON P.Prod_ID = S.Prod_ID
GROUP BY A.Prod_ID,P.Prod_Name,
         S.SumOfQty_on_Order;

[panjap]

Try this ...

SELECT [All customer order details].Prod_ID, Products.Prod_Name, 
[stock level Query].SumOfQty_on_Order As QtyOnOrder, Sum(Products.Qty_in_Stock) As QtyInStock, 
QtyInStock-[stock level Query].[SumOfQty_on_Order] AS New_Level
FROM (Products INNER JOIN [All customer order details] 
ON Products.Prod_ID = [All customer order details].Prod_ID) 
INNER JOIN [stock level Query] 
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID, 
Products.Prod_Name, [stock level Query].SumOfQty_on_Order;

sorry this did not work as the
this worked however this alterd the Qtyinstock of the product 1, 2 and 3 to "420" and product 6 to "180". and still carried out the correct calculations

[panjap]

all the other products worked fine

[NeoPa]

sorry this did not work as the
this worked however this alterd the Qtyinstock of the product 1, 2 and 3 to "420" and product 6 to "180". and still carried out the correct calculations

Can you explain this?
I'm not sure what you're trying to say here.

[panjap]

Can you explain this?
I'm not sure what you're trying to say here.

for all the qty in stock i entered 60, and this came up in the query forr all prodcuts except
for the product ID 1, and product id 2 and 3
here for some reason thi came up as the reading 420.
While for product id 7 this came up as 180

and then it carried out the normal function of qty in stock - qty on order

[MMcCarthy]

sorry this did not work as the
this worked however this alterd the Qtyinstock of the product 1, 2 and 3 to "420" and product 6 to "180". and still carried out the correct calculations

OK there are two things going on here. You can't represent the Qty_In_Stock as an individual figure and a sum of Qty_In_Stock in the calculated field at the same time. So I've changed the Qty_In_Stock to only represent the total sum figure required for the calculation.

[panjap]

OK there are two things going on here. You can't represent the Qty_In_Stock as an individual figure and a sum of Qty_In_Stock in the calculated field at the same time. So I've changed the Qty_In_Stock to only represent the total sum figure required for the calculation.

do i now need to chane the query, sorry i do not follow you

[MMcCarthy]

SELECT 
[All customer order details].Prod_ID, 
Products.Prod_Name, 
[stock level Query].SumOfQty_on_Order As QtyOnOrder, Sum(Products.Qty_in_Stock) As QtyInStock, 
(QtyInStock-QtyOnOrder) AS New_Level
FROM (Products INNER JOIN [All customer order details] 
ON Products.Prod_ID = [All customer order details].Prod_ID) 
INNER JOIN [stock level Query] 
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID, 
Products.Prod_Name, [stock level Query].SumOfQty_on_Order;

This query will represent each product and the total qty of that product on order, the qty of the product in stock and the new balance of qty in stock.

There must be more than one customer order of a product in this query if it's multiplying the qty on order figure.

Mary

[panjap]

SELECT 
[All customer order details].Prod_ID, 
Products.Prod_Name, 
[stock level Query].SumOfQty_on_Order As QtyOnOrder, Sum(Products.Qty_in_Stock) As QtyInStock, 
(QtyInStock-QtyOnOrder) AS New_Level
FROM (Products INNER JOIN [All customer order details] 
ON Products.Prod_ID = [All customer order details].Prod_ID) 
INNER JOIN [stock level Query] 
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID, 
Products.Prod_Name, [stock level Query].SumOfQty_on_Order;

This query will represent each product and the total qty of that product on order, the qty of the product in stock and the new balance of qty in stock.

There must be more than one customer order of a product in this query if it's multiplying the qty on order figure.

Mary

for some reason this still does not work
do you have any ideas
thank sooo much for all your help so far

[MMcCarthy]

for some reason this still does not work
do you have any ideas
thank sooo much for all your help so far

You will need to give a breakdown of the tables involved, the relevant fields and what they are storing. The relationships between those tables and the subsequent queries that form the basis for this query. Explain again in plain english rather that computer speak exactly what you are trying to do.

[panjap]

You will need to give a breakdown of the tables involved, the relevant fields and what they are storing. The relationships between those tables and the subsequent queries that form the basis for this query. Explain again in plain english rather that computer speak exactly what you are trying to do.

Here is a break down of my original tables

CUSTOMER

Cu ID
And all their personal details such as contact details


CUSTOMER ORDER
This is where the order takes place and contains the following information


Order_ID
Cu_ID
Staff_ID
Order_Date
Required_Date
Recievied_Date
Order_Total
Delivery
Delivery costs
Overall discount
Type-Of_Purchase
Paid
Money_Owed
Final Costs


CUSTOMER ORDER DETAILS
Here is the specific details of one order.

Order_ID
Prod_ID
Qty_on_Order
Discount
Line_Total

PRODUCTS

Prod_ID
Supp_ID
Product_Name
Product_Type
Prod_ Unit_Details
Qty_in_Stock
Re_Order_level
Price
Unit_Cost
Manufacturer_ID
Shelf_Life

SUPPLIER TABLE – with detaisl e.g Supp Id and account number

SUPPLIER ORDER
Supp_OrderID
Supp_ID

Date_Ordered
Date_Required
S_Delivery_Date
S_Order_Total
Money_Owed Delivery
Driver
Supp_Credit_ Limit
Overall discount
VAT
Type-Of_Purchase

S_Paid
Supp_Final_Cost s

SUPPLIER ORDER DETAILS

Supp_OrderID
Prod_ID
Qty
Unit_Detials
Qty_Recieved
Discount
Line_Total


QUERIES

• In the query “All customer order details”, I have worked out the line total for each prodcut with in one order. I have timed the Qty_on_Order (customer order details table) by price (in the produt table) and by the discount(custom er order details) this then gives the line total
• I then made a new query called “Customer Query making order” where by I added up all the line totals for one orders and called this “Order_Total”, I inserted here the delivery cost (customer order) and overall discount(custom er order)
• I than made a new query called “FINAL COSTINGS”, where I the order total was added to the delivery cost and times by the overall discount.

Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”

I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Or der” of each product, from “Qty_in_Stock” to produce “Qty_in_Stock”









I am trying to produce a system which controls the stock control.
So far the system can add up the total number of products sold, for all the orders, and then automatically calculate the Qty_ in_ stock by taking away the “SumOfQty_on_Or der” once a new order is made. This was the initial problem which I have now sorted thanks to all your help.
However I can not make the “Qty_in_Stock”, to appear in the actual table “Products”.
At the moment I can only update this in the query “stock level 2”. I need this as I can then set re order level which then comes up with an error message telling me when the re order level has been reached.

As later on I will need to add on any products brought in from the suppliers to the Qty_in_Stock”, in the products table to be automatically updated.

[panjap]

could this work instead
if i make a new query containing product ID, and name, and also the Re_Order_level, and Qty_in_Stock(up dated)
could i then use some formula to work out with a mesage or in another format that the Qty_in_Stock has reached the Re_Order_level, and to warn that it needs reordering.
this could be either a message and/or a filed called 'Need_Reorderin g' which is then ticked.
also is it possible when putting in a new order to say that there is not enough when over ordering?
thank you for your help
it has been great

[MMcCarthy]

Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”

I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Or der” of each product, from “Qty_in_Stock” to produce “Qty_in_Stock”.

Can you post the SQL of the following queries

“stock level Query”
“All customer order details”

Mary

[panjap]

Can you post the SQL of the following queries

“stock level Query”
“All customer order details”

Mary

Here is
“stock level Query”

SELECT [All customer order details].Prod_ID,
       Sum([All customer order details].Qty_on_Order) AS SumOfQty_on_Order,
       Products.Prod_Name
FROM Products INNER JOIN [All customer order details]
  ON Products.Prod_ID = [All customer order details].Prod_ID
GROUP BY [All customer order details].Prod_ID,
         Products.Prod_Name;

here is “All customer order details”

SELECT [Customer Order Details].Prod_ID,
       Products.Price,
       [Customer Order Details].Qty_on_Order,
       [Customer Order Details].Discount,
       (Products.Price*[Qty_on_Order]*(1-[Discount])/100)*100 AS Line_Total,
       [Customer Order Details].Order_ID,
       Products.Prod_Name,
       Products.Prod_Type,
       [Customer Order].Cu_ID
FROM [Customer Order] INNER JOIN
     (Products INNER JOIN
     [Customer Order Details]
     ON Products.Prod_ID=[Customer Order Details].Prod_ID)
     ON [Customer Order].Order_ID=[Customer Order Details].Order_ID
ORDER BY [Customer Order Details].Order_ID;

and here is