Stock levels

[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_Ord er (from "stock level Query")
Qty_in_Stock: Products.Qty_in _Stock-[SumOFQTY_on_Ord er]
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_Sto ck" (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_N ame, Products.Prod_T ype, [Customer Order].Cu_ID
FROM [Customer Order] INNER JOIN (Products INNER JOIN [Customer Order Details] ON Products.Prod_I D=[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_N ame, [stock level Query].SumOfQty_on_Or der, Products.Qty_in _Stock-[SumOFQTY_on_Ord er] AS Qty_in_Stock, Products.Re_Ord er_level
FROM (Products INNER JOIN [All customer order details] ON Products.Prod_I D = [All customer order details].Prod_ID) INNER JOIN [stock level Query] ON Products.Prod_I D = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID, Products.Prod_N ame, [stock level Query].SumOfQty_on_Or der, Products.Qty_in _Stock-[SumOFQTY_on_Ord er], Products.Re_Ord er_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_Sto ck" (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).