using the sum function

[scottappleford]

hi

i have a stock control db - when placing an order in the purchase orders from - i would like to use the sum function to work out how many units i have on order in the products form then when goods are recieved have this work out units in stock - and then when goods are taken out have this re-calculate the units on hand etc.

thanks

scott

 

[ColinEssex]

The units in stock should change automatically when you make an order and issue the items, you can then build in a "re-order" amount so that when the item stock reaches that number it posts it to a re-order table so you can print off what needs to be ordered.

Then when its delivered from outside, you then enter the detail (via a form to the StockReceived table) like Delivery date, order No etc and the amount you receive. This amount is then automatically added to the current stock level.

Col

 

[scottappleford]

A liitle more explaination

I have subform within the main products from that shows all the trasnactions. in the manin form i have a 'units on hand' and 'units on order' control.

Main Form looks like this in the properties

=[Products Subform].[Form]![UnitsOnHand]

=[Products Subform].[Form]![UnitsOnOrder]

Sub form in the form header

=Sum(nz([UnitsOrdered])-nz([UnitsReceived]))

=Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

sub form detail

i have the following controls

units ordered - this value comes from the transaction taken place in the purchase order form

units received - this gets updated when the goods arrive

units shrinkage - gets updated if stock is damaged etc

units sold - not using this at present so it is hidden

I then have another subform for stock going out.

and i would like it to feed back into the main products form e.g. units on hand - so when goods go out it updates this field.

thanks