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_Sto ck", "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_Ord er
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_Ord er]

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

i also tried
New_Level: Sum[Qty_in_Stock]-[SumOFQTY_on_Ord er]

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_Ord er
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_Ord er]

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

i also tried
New_Level: Sum[Qty_in_Stock]-[SumOFQTY_on_Ord er]

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_Ord er] 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_Ord er] 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_N ame, [stock level Query].SumOfQty_on_Or der, Products.Qty_in _Stock, Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Ord er] AS ew_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, Sum([Products].[Qty_in_Stock])-[stock level Query].[SumOfQty_on_Ord er];

[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_Ord er 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_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?