Using cal;culation

[scottappleford]

Hi

I am trying to add a field to the following calculation:


=[Products Subform].[Form]![UnitsOnHand]-nz(DSum("[QuauntityOut]","IntStockControl","Forms!Products.[ProductID] = [IntStockControl]![ProductID] and isnull([IntStockControl]![DateIn]) = true "))

The new field is in the (IntstockControl) and is a quantity back in field. The calc above is a units on hand field. The calc works fine but i need to add the quantity back in.

This calc was done by someone else - do i need the 'date in' is null?

Thanks

Scott

 

[ScottGem]

First, As a general rule we do not store calculated values. Its not clear whether the UnitsOnHand control is bound to a field or not, but it shouldn't be.

Second, the consensus method for doing Inventory centers around a transactions table. Such a table will store all movement of stock, either in or out. Stock on hand is claculated by adding all the Ins and subtracting the Outs. It seems like you are trying to do that, but its not clear.

finally, you have a repeating group in your IntStockControl table. instead of having separate fields for QuantityIn, QuantityOut, QuantityBack, etc, you should have ONE field named TransactionType which holds a value indicating the type of transaction.

Also I would not query the entire table in your Dsum, Instead I would have a stored query that lists all Out transactions (another for Ins) filtered by the product selected on the form. You can then use that quert as the domain in your DSum. You won't need criteria then and your DSums will run more quickly.

 

[scottappleford]

to clarify

HI

Thanks for the reply.

The field is not bound to a table or query it is unbound with the calc in it.

I have three a number of tables.

Purchase Order Table
Transaction Table
Product Table
Stock Control Table

Once products have been added i put the main order info in tblPurchase Order e.g. PO nr, ship date etc. This main form has a sub form tbl transactions where the product is enetered, # ordered etc.

I then have some other forms with combo boxes bound to a query for goods rec'd that is related to the transaction table. So i know have my goods in stock. And from this i have unbound calculated field for units on order and units in stock.

Then on the main product form i have the tbltransaction as a sub form so users can see the tranasaction history of the product. And another subform that controls the stock going in and sometimes coming back (stock control table), which has #out, date out, a tick box to indicate whether it was an item not coming back and #in, date in.

All subforms are datasheets.

So thats how it works at the moment.

I basically need a calculation to do all the add and deducts to show me goods on order (got that one) ordered minus received. Now I need to work out the rest and it works upto until the #back in.

I am not sure what you mean by the repeating group and how this would work, plus some of the calcs etc were not done by me so my understanding is limited.

Thanks for your help.

Scott

 

[ScottGem]

First, a Repeating group is a group of fields in a table that contain similar information. Usually repeating groups will have names like Contact1, Contact2, etc. In your case, you are using separate quantity fields for the different types of transactions. Instead, you should use ONE quantity field and ONE field to identify the type of transaction.

From there its very easy to create a query that calculates the stock on hand, using a GroupBy query grouping by product and summing the Quantity field. To sum the different types correctly, set the Code for Transaction Type so that the Out and In transactions are bunched. For example, all the outs have a code less than 10, all the Ins greater than 10. You can then use the following to adjust:

NetQuantity: IIF(TransType<10,[Quantity] * -1, [Quantity])

Sum that column and you have your totals.

 

[scottappleford]

I have the db alreadys set up so i am going to do what you say, however i would like to add a hidden text box on the sub form. I have added trasn type control, with Stock In, Stock Out.

What i would like it to do is work out. (like you said) add up all the stock in, add up all the stock out then take the in from out to give me the result. PLEASE could you show me what i need to put as the formula??

I will then use the answer in the main form. e.g. take stock info from transaction info.

Also is there a way to stop the stock going out falling below zero and giving a message?

thanks in advance

scott

 

[ScottGem]

Go over what I've said before. I've already explained what you need to do.