Inventory query

[janeyd]

Hi
I have created a 'stock database' for yarns.
I have a table of yarns with a connected table where I can assign a colour and an initial stock amount.
I have then three tables with stock adjustments - an In table, a Sales table and a general Out table. For these three tables I have created individual queries with a running total for all yarns/colours.
Finally I have created a query to show - Yarn, Colour, Initial Stock, In, Sales, Out, and a calculated field (Initial stock + In - Sales - Out) to give me current stock position.
I have also set the criteria of the calculated field to <>0 so it doesn't show zeros.
It all seemed to be working well until I started getting amounts like -8.88178419700125E-16 in the calculated field and I can't work out why or how to not have them displayed.
Any help much appreciated

 

[RuralGuy]

All of your fields should be Integers (probably Long) as you can't have a partial skein. The number showing is a *very* small floating point number.

 

[Mihail]

Calculate the field using this (or something like this):
IIF(ABS(Initial stock + In - Sales - Out) < 1e-5;0;Initial stock + In - Sales - Out)

 

[janeyd]

Thank you both. I shall give these a go.

 

[Pat Hartman]

To avoid floating point errors, use Currency as the data type. Currency is good to 4 decimal places or if you don't need any decimal places, use Integer. Currency as a data type can be formatted any way you want since Format controls how data is displayed and the DataType controls how it is stored.

I prefer to use a single table for all transactions with a transaction type. That simplifies my queries and avoids the need for union queries.

Once you put everything where it belongs, you would use a totals query to Sum the transaction amount. The easiest way to make this work is to store the data with the "natural" sign so you can just sum everything and not worry about what should be added and what should be subtracted. To facilitate that, I store a "sign" field in my transaction type table. The value is either +1 or -1 and I use that field to multiply with the quantity entered so Sales is defined with a -1 sign and so quantity * sign results in a negative number which subtracts the sold quantity.

 

[Galaxiom]

Calculate the field using this (or something like this):
IIF(ABS(Initial stock + In - Sales - Out) < 1e-5;0;Initial stock + In - Sales - Out)

Often the initial stock is simply the first adjustment record so there is no need to have an Initial Stock field in the yarn table.

In and Out tables may be able to be combined too. Use a negative or positive value to show the difference or include a field that designates in or out.

Sometimes these can even be included in the Sales table by designating a fake customer. For example a range of negative customer numbers that are ignored when calculating sales.

 

[janeyd]

Sorry haven't responded earlier I have been trying to work this out plus other things!

I need to show decimals so can't use Integer. (Yarn is on cones and we wind to order per g).

I don't want to have one table for all my ins and outs as I want to make it user friendly for other people and have nice little forms depending on why stock is moving. I also like to run reports at the end of each month showing exactly where stock has gone or come from. I also supply our own manufacturing process and get back the returns so is a little more complicated.

I also have over 2,000 products with transactions against them so don't really want to have to start again.

I tried using the 'ABS' formula but couldn't get it to fit my needs. (sure it's just me being a little thick). The code I originally typed was simplified - what I actually have is: Total: Nz([Initial Stock],0)+Nz([Yarn In Query]![Sum Of Kg],0)-Nz([Total Stock Sold]![Sum Of Kg],0)-Nz([Yarn Out Query]![Sum Of Kg],0).
The criteria is set to <>0. The stock is actually showing as zero using this, but what I want is the whole line not to show at all.

Thanks for any more help!!

 

[Galaxiom]

I need to show decimals so can't use Integer. (Yarn is on cones and we wind to order per g).

That is why Pat suggested Currency. It is a scaled integer variable and is not subject to rounding errors. That will solve your problem.

You might as well continue with the structure as you are but for future reference:

I don't want to have one table for all my ins and outs as I want to make it user friendly for other people and have nice little forms depending on why stock is moving.

The table structure does not dicate the form design. The forms' RecordSource properties can easily be adjusted to the InitialStock/In/Out data being held in a single table. The single table greatly simplifies the queries.

I also have over 2,000 products with transactions against them so don't really want to have to start again.

It is simple to build queries to write the existing data to new table structures.