Stock levels

[panjap]

hello I am currently working on a project for college, based on a shop and have created querues which allow me to total up new customer orders
however i am currently sruggling on how to create a system which will update the stock level one a customer pruchases an item in the product table, and then a warning message should appear when the stock reaches the re order level
do i create a new query for this or use the following below
below are my tables
Customer
Customer Order
Customer Order Details
Products -- includes fileds "Product ID", "Qty_in_Stock", "Re_Order_level" and "Need re ordering" the need re ordering is yes/ no format
Suppliers

here are the queries i have created


"All customer order details" (this includes Product ID, Qty_on_Order, and product name) here is where orders are made and where line totals are added.

so i want ot be able to link the fields to make the field Qty_in_Stock go down automatically after an order and once the Re_Order_level is met for the field Need re ordering to be ticked.
i would be grateful for any help what soever and wil be really thankful!

[NeoPa]

Basically, you need a form on which you would enter the orders (including all the details). This form would arrange to update the Products.Qty_in_Stock whenever an order is created or modified in the form for the relevant product. It could also update Products.[Need re ordering] if required.
If you are designing a system like this, I would consider reading this link (Normalisation and Table structures) first. Products.[Need re ordering] should probably not be held as stored data.

[panjap]

Basically, you need a form on which you would enter the orders (including all the details). This form would arrange to update the Products.Qty_in_Stock whenever an order is created or modified in the form for the relevant product. It could also update Products.[Need re ordering] if required.
If you are designing a system like this, I would consider reading this link (Normalisation and Table structures) first. Products.[Need re ordering] should probably not be held as stored data.

thank you for your help but oi am sorry i do not understand what you mean, i have read the normalisation, and feel i have carried this out, however, in my query customer making order (e.g. where line total * qty), do i then add the field qty in stock and apply some sort of formula
i would really be appreciative of your help as i am really stcuk by this problem
thank you

[NeoPa]

Reading that is a good first step.
Be aware :- I am not about to design or implement the whole project for you. That said I think I can still help, but you need to make the journey with me at least.
For updating the [Qty_in_Stock] field (Let's be consistent in our references or we'll certainly get more confused than we need) I suggest that the form gets bound to an updatable query linking the [Customer Order] & [Customer Order Details] tables. When you update or add an Order line, then you would need to have some code in your SubForm's AfterUpdate event that updates the [Products] table depending on the new value and, if updated, the old value as well.

If you don't understand any of this (and as you are a student), I suggest that you take this with your current understanding to your tutor and get him to explain the basic concepts we'll be dealing with. If you're doing this project at school or college then you should be expected to be able to handle it. If not, my doing it for you will not help you to learn anything. My helping you to do it, on the other hand, is another matter ;)

[maxamis4]

I too was once a student and saw how diffcult things were. I am also not going to create your project for you but here is a tip.

Create a table that you might call par. For each item give it a unique id, a description, the par, and the cost of the item.

 
TBL_PAR
UID PK
DESCRIPTION
PAR
COST
 
 

Your second table should be called sales. This is where you track customer purchase history. The table should include something like so;

TBL_SALES
QUANTITY (HOW MANY WERE SOLD)
ITEM FK (FORIEGN KEY) = UID
DOS (DATE OF SALE)
CUSID (IF YOU HAVE A CUSTOMER UNIQUIE ID REFERENCE HERE)
 
 
 

With an update query you could have the par subtract the quantity of the sale.

good luck and let me know if you need any more help

[NeoPa]

Nice one Maxamis.
It's always nice to see a Junior Member contributing to others' threads.

[panjap]

I too was once a student and saw how diffcult things were. I am also not going to create your project for you but here is a tip.

Create a table that you might call par. For each item give it a unique id, a description, the par, and the cost of the item.

 
TBL_PAR
UID PK
DESCRIPTION
PAR
COST
 
 

Your second table should be called sales. This is where you track customer purchase history. The table should include something like so;

TBL_SALES
QUANTITY (HOW MANY WERE SOLD)
ITEM FK (FORIEGN KEY) = UID
DOS (DATE OF SALE)
CUSID (IF YOU HAVE A CUSTOMER UNIQUIE ID REFERENCE HERE)
 
 
 

With an update query you could have the par subtract the quantity of the sale.

good luck and let me know if you need any more help

cheers for your help in a new query (sotck level query) I have added up the total products sold for all orders taken palce by adding up the Qty_on_Order. this gave me the new automatic field SumOfQty_on_Order
i then have the field Qty_in_Stock in the products table and in a new query i have attempted this formula

New_Level: Sum([Products].Qty_in_Stock-([stock level Query].[SumOfQty_on_Order]

but says error message of error in parenthesis oreither bracket and vertical bar

i also tried
New_Level: Sum[Qty_in_Stock]-[SumOFQTY_on_Order]

but failed again. i would be most grateful for any tips and thank you for both your help earlier on.
i decided to leave date out of this at the moment could that be a problem?
Thank you

[MMcCarthy]

cheers for your help in a new query (sotck level query) I have added up the total products sold for all orders taken palce by adding up the Qty_on_Order. this gave me the new automatic field SumOfQty_on_Order
i then have the field Qty_in_Stock in the products table and in a new query i have attempted this formula

New_Level: Sum([Products].Qty_in_Stock-([stock level Query].[SumOfQty_on_Order]

but says error message of error in parenthesis oreither bracket and vertical bar

i also tried
New_Level: Sum[Qty_in_Stock]-[SumOFQTY_on_Order]

but failed again. i would be most grateful for any tips and thank you for both your help earlier on.
i decided to leave date out of this at the moment could that be a problem?
Thank you

You're not closing your parentheses ..

 
New_Level: Sum([Products].[Qty_in_Stock]-[stock level Query].[SumOfQty_on_Order])

This will sum [Qty_in_Stock] less the [SumOfQty_on_Order] for each [Qty_in_Stock] value

However if you are trying to sum the quantity in stock and then remove the total quantity on order you will need the following.

 
New_Level: Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order]
 

[panjap]

You're not closing your parentheses ..

 
New_Level: Sum([Products].[Qty_in_Stock]-[stock level Query].[SumOfQty_on_Order])

This will sum [Qty_in_Stock] less the [SumOfQty_on_Order] for each [Qty_in_Stock] value

However if you are trying to sum the quantity in stock and then remove the total quantity on order you will need the following.

 
New_Level: Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order]
 

thankyou for all your help but for some rason after tyoing in the sepcified query this error message came uo

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.
hane you any ideas on why this happens. i do noy see any problem with your code. IS it my Query set up?
thankyou again for your help

[MMcCarthy]

thankyou for all your help but for some rason after tyoing in the sepcified query this error message came uo

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function.
hane you any ideas on why this happens. i do noy see any problem with your code. IS it my Query set up?
thankyou again for your help

Yes the problem is in the query. Can you change the query view to sql and copy and paste the full query in here.

Mary

[panjap]

Yes the problem is in the query. Can you change the query view to sql and copy and paste the full query in here.

Mary

here is is below
thankyou so much


SELECT [All customer order details].Prod_ID, Products.Prod_Name, [stock level Query].SumOfQty_on_Order, Products.Qty_in_Stock, Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order] AS ew_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, Products.Qty_in_Stock, Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order];

[MMcCarthy]

Try this ...

SELECT [All customer order details].Prod_ID, Products.Prod_Name, 
[stock level Query].SumOfQty_on_Order, Products.Qty_in_Stock, 
Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order] AS ew_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,
Products.Qty_in_Stock;

[panjap]

Try this ...

SELECT [All customer order details].Prod_ID, Products.Prod_Name, 
[stock level Query].SumOfQty_on_Order, Products.Qty_in_Stock, 
Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Order] AS ew_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,
Products.Qty_in_Stock;

thanyou again for all your help
this woiurks except for some products whhich come out with unusual results

here are the results

SumOfQty_on_Order       Qty_in_Stock     New level
19                       60               401
23                       60               397  
22                       60               398
2                        60                58
2                        60                58  
7                        60                73
3                        60                57

what could be the problem?

[MMcCarthy]

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;

[panjap]

thanyou again for all your help
this woiurks except for some products whhich come out with unusual results

here are the results

SumOfQty_on_Order Qty_in_Stock New level
19 60 401
23 60 397
22 60 398
2 60 58
2 60 58
7 60 73
3 60 57


what could be the problem?

[Hello here are the new results

Qty_in_Stock - 60 for all

SumOfQty_on_Order
19
23
22
2
2
7
3
the ORIGINAL "Qty_in_Stock" were all 60


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

what could be the problem?

[MMcCarthy]

[Hello here are the new results

Qty_in_Stock - 60 for all

SumOfQty_on_Order
19
23
22
2
2
7
3
the ORIGINAL "Qty_in_Stock" 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_Costs

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(customer 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(customer 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_Order” 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_Order” 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(updated)
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_Reordering' 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_Order” 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

[NeoPa]

It seems to me, that you don't want to store this value at all.
If you store it you will need to implement all sorts of checks and code to make sure that it is maintained accurately and stays in step with the related data (orders; purchases etc).
Whereas, if you calculate it on the fly (as you already know how to do), then it will always be accurate automatically and will also save you the extra head-ache of maintaining the data.
To better understand this concept have a look through Normalisation and Table structures which explains it quite well.

[nico5038]

Have to point to NeoPa's normalization, as that's the way to go.
I've developed such a system and used a table with purchases and a table with supplier deliveries to calculatethe stock. I just worked on a yearly basis and started the year with a dummy supplier record holding the start values of the stock for that year.

Nic;o)

[panjap]

i have now been able to work out how to work out the quantity left in the stock after taking it away from the new qty in stock in a query called stock level 3.
there are the fields

Prod_ID (Products)
Prod_Name (Products)
SumOfQty_on_Order (from "stock level Query")
Qty_in_Stock: Products.Qty_in_Stock-[SumOFQTY_on_Order]
Re_Order_level (Products)

However i a have been really struggling on how to make the database to tell me i am ordering mre stock then there is.
how can i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), that i am unable to do this an error message appears

here are both sql form both tables

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;



stock level 3

SELECT [All customer order details].Prod_ID, Products.Prod_Name, [stock level Query].SumOfQty_on_Order, Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock, Products.Re_Order_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, Products.Qty_in_Stock-[SumOFQTY_on_Order], Products.Re_Order_level;


I would be greatful for any help, as i am really stuggling
Thank you

[NeoPa]

I can't help you with this any more I'm afraid.
You may want to consider following the guidelines about posting if you want the other experts to help (POSTING GUIDELINES: Please read carefully before posting to a forum).
I tried adding code tags to your post again, only to find the lines were way to long to be read.
In case you're not aware, SQL is not affected by line breaks, so can quite easily be posted on here in a readable format, simply by breaking the lines.

[panjap]

have now been able to work out how to work out the quantity left in the stock after taking it away from the new qty in stock in a query called stock level 3.
there are the fields

Prod_ID (Products)
Prod_Name (Products)
SumOfQty_on_Order (from "stock level Query")
Qty_in_Stock: Products.Qty_in_Stock-[SumOFQTY_on_Order]
Re_Order_level (Products)

However i a have been really struggling on how to make
the database to tell me i am ordering mre stock then there is.
how can i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), that i am unable to do this an error message appears

here are both sql form both tables

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;

stock level 3

SELECT [All customer order details].Prod_ID, 
  Products.Prod_Name, [stock level Query].
  SumOfQty_on_Order,
  Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock,
  Products.Re_Order_level,Products.Prod_Name,
  [stock level Query].
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,
  SumOfQty_on_Order,
  Products.Qty_in_Stock-[SumOFQTY_on_Order],
  Products.Re_Order_level;

[NeoPa]

Although you made an effort to follow the posting instructions (well done - I'm happy with that), I tidied them up a little further and added the code tags (That's the most important bit btw - Select code to surround in Tags and click on the control marked with the # character when posting).
After doing that, I can see that there is an error in the SQL you posted on the sixth line of your last (Stock Level 3) query. You have no field specified but just a queryname with a trailing (.).

BTW I should just clarify what I said about SQL not being effected by Line Breaks. This is true only where white space is expected. In other words, anywhere you can put a space you can put a line break; a tab or multiples of any mixture of these characters.

[panjap]

Although you made an effort to follow the posting instructions (well done - I'm happy with that), I tidied them up a little further and added the code tags (That's the most important bit btw - Select code to surround in Tags and click on the control marked with the # character when posting).
After doing that, I can see that there is an error in the SQL you posted on the sixth line of your last (Stock Level 3) query. You have no field specified but just a queryname with a trailing (.).

BTW I should just clarify what I said about SQL not being effected by Line Breaks. This is true only where white space is expected. In other words, anywhere you can put a space you can put a line break; a tab or multiples of any mixture of these characters.

the code is slightly different
the query for stock level 3 is

SELECT [All customer order details].Prod_ID,
       Products.Prod_Name,
       [stock level Query].SumOfQty_on_Order,
       Products.Qty_in_Stock-[SumOFQTY_on_Order] AS [New Qty_in_Stock],
       Products.Re_Order_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,
         Products.Qty_in_Stock-[SumOFQTY_on_Order],
         Products.Re_Order_level;

i do not know whether this would work or not -
if i inserted vaslidation within the form, so that the qty on order is less then or equal to the qty in stock
have you any advice
thank you so much for your help.

[NeoPa]

To be honest, I cannot, after 38 posts, understand the whole of this thread and answer general questions like (Will this work?)
The idea is to ask manageable questions rather than try to get a whole project sorted out for you. I still find I'm running around after you, adding code tags and tidying up your posts so that they can be read. I don't want to spend even more time, on top of this, rereading all the posts to understand what you're trying to ask me.
It is down to you to ask your question in such a way that it is understandable and of limited scope.

In as much as I have checked over the query, it should work, but could probably be better written as :

SELECT DISTINCT [All customer order details].Prod_ID,
       Products.Prod_Name,
       [stock level Query].SumOfQty_on_Order,
       Products.Qty_in_Stock-[SumOFQTY_on_Order] AS [New Qty_in_Stock],
       Products.Re_Order_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;

I say this because, although it was GROUPing, there were no aggregate functions used and it seemed it was merely an attempt to lose the duplicates (which DISTINCT does better anyway).