Help displaying low stock items

[nicksource]

Hi guys,

I've made a stock control system, which basically has a "Stock In" and "Stock Out" tables, then using a DSum it calculates the stock level. So it isn't actually storing the stock figure.

I have a field for the products that's called "Minimum Stock" - which is simply the level the stock should stay above.

What I want, is a form that lists all the stock that is near or below this level but since I use a DSum (I'm not a VBa programmer) I'm not sure how to do this?

Thanks in advance,
Nick.

 

[neileg]

If you had all the ins and outs in the same table and made the outs negative, a simple totals query would give you the balance. In the query you could create a calculated field that subtracted the minimum stock level from the calculated level and test for whatever you think of as 'near', say less than 2 or whatever.

 

[nicksource]

Ahh, yeah, that's easy enough.

What would the calculated field look like? How would I set that up?

 

[neileg]

Create a totals query to calculate the balances. Create a new query and add the balance query and your product table where you hold the minimum order level. Join the balance query and the product table on product ID. Then create a calculated field that subtracts the MOL from the balance. Add a criterion to this field of <-1.

 

[nicksource]

I've nearly got it, I've done this query to list low stock items which works but... it doesn't group the products (by Product.ID). I've tried doing GROUP BY Products.ID but it doesn't work, comes up with an error about an aggregated function.

SELECT Products.ID, Products.Product, Products.Description, Products.Price, Products.Category, Products.Supplier, [Goods].[In]-[Goods].[Out] AS Stock
FROM Products INNER JOIN Goods ON Products.ID=Goods.ProdID
WHERE ((([Goods].[In]-[Goods].[Out])<[Products].[MinStock]))
ORDER BY Goods.[In]-Goods.[Out];

Any ideas?

 

[neileg]

What is Goods? Is it a query or a table? What is held in the fields in and out?

 

[nicksource]

Goods is a table. In and Out is a number field to store the stock out/in.

 

[neileg]

Don't use separate In and Out fields. Use one field and make it +ve for in and -ve for out.

Then you need two queries, one to calculate the balances and then a second one that compares the balances with the MOL.

 

[The_Doc_Man]

Alternative: If you really, really, REALLY want two tables for in and out, make the summation query based on a UNION of the two tables, for which you can put a formula in the "legs" of the union

SELECT PartID, InQty as PQty, etc. etc. FROM PARTSIN UNION
SELECT PartID, -OutQty as PQty, etc. etc. FROM PARTSOUT ;

Then do a summation group by PartID, sum of PQty (because to do the formula mapping, you have to give the "mapped" fields a synthetic common name).

SELECT Sum(PQty) as SumPQty FROM {name of the union query} GROUP BY PartID;

(with more details added as appropriate, of course)

Now you can do a third query based on parts thresholds, even to the point of having individual parts thresholds by PartID

SELECT PartID, SumPQty, ThresQty FROM SumQuery, Thresholds JOIN ON PartID WHERE SumPQty <= ThresQty;

This is the rough idea and you would have to play with it. But it is doable like this if you really want the tables separate.

To be honest, though, I would put all my transactions in one table and add a field that is the transaction corrected for being an add or draw, call it EffQty, and have it + for stock add, - for stock draw. There are other ways to do this as well.

 

[nicksource]

Thanks a lot guys. I want to do it the easiest way possible, which I think is a single field like you both described.

But how do I do the +/- of a field? Is it with a SQL query or something?

 

[neileg]

Thanks a lot guys. I want to do it the easiest way possible, which I think is a single field like you both described.

But how do I do the +/- of a field? Is it with a SQL query or something?

Eh? As I already said, store the ins as a +ve value and the outs as negative.

 

[nicksource]

Eh? As I already said, store the ins as a +ve value and the outs as negative.

But how do I make it a negative?

When I enter a number, it's always positive, I guess I have to put the minus in before the number but is there anyway to automate this?

I have 2 forms, 1 for Goods In and one for Out.

 

[neileg]

Several ways. Probably the easiest is to create two text boxes on your form, say txtInput and txtQuantity. Bind txtQuantity to your underlying field and leave txtInput unbound. In the AfterUpdate event of txtInput add the following code:
Me.txtQuantity = Me.txtInput*-1
Then make txtQuantity invisible.

 

[gemma-the-husky]

lets go back a step

you will have a transactions table

now in this table there will be a field for movements in (ie increasing the balance) and another for movements out (ie decreasing the balance)

you may even have two separate transaction tables - one for ins and one for outs

so if you want to evaluate the total, you need to
a) sum the ins
b) sum the outs
c) find the difference

so to make this easier, you can
a) store the outs as negatives - so now you sum the ins, sum the outs, and sum both of them, to get the movement - so now, take this further, and
b) instead of having two separate columns for ins and outs, you can store ins and outs in the same column, which means you dont need to do any adding/subtracting, just sums


so these last two ideas really come back to database design.

eg to have the outs as negative, enter them as positive, maybe in a temp field, and revese the sign before storing them in the true field.

its a matter of taste as to whether you do the hard work at the start - ie when you enter the data, or at the end, when you are using the data. for a variety of reasons its generally better to do it at the start - you are only doing it once then.

 

[nicksource]

Thanks Neil & Dave, all I want to do is make it as simple as possible.

The most important feature would be the end user being alerted when stocks are low. I was wanting to have on the startup screen a list of products that were near or below the minimum stock level field.

Hopefully this new way of 1 column makes the maths simple. I'm just redoing it now, so will soon find out, thanks for the help so far guys!

 

[nicksource]

Several ways. Probably the easiest is to create two text boxes on your form, say txtInput and txtQuantity. Bind txtQuantity to your underlying field and leave txtInput unbound. In the AfterUpdate event of txtInput add the following code:
Me.txtQuantity = Me.txtInput*-1
Then make txtQuantity invisible.

What are the other methods? I can't get this to work.

Me.txtQuantity = Me.txtInput*-1
Also tried:
[txtQuantity] = [txtInput]*-1

Just doesn't like it in After Update (doesn't update the field).

 

[gemma-the-husky]

well the easiest way is to have in your products table a minimum stock qty

now have query that evaluates "ins" per product , and a query that evaluates "outs". if you can have a single query that does on "net" it helps but it isnt critical -maybe you have a starting stock as well.

now, have a query that joins the product table, to the ins query and the outs query, and brings in the opening stock also.

so now you calculate the existing stock with "opening stock" + "ins" - "outs"

now compare this with the minium reorder quantity, and report any that have gone below.

Rather than calculating a percentage variance for stuff getting "near" the minimum qty though, its surely easier just to store a working minimum stock level that builds this in.

 

[The_Doc_Man]

Another way of thinking. Stock transactions typically come in flavors such as a Stock In for a "Purchase Delivery" or a "Sales Return" and a stock Out for "Sales" or "Shrinkage." There is also the "Inventory Correction (Add)" and "Inventory Correction (Remove)", and finally the "Stock On Hand after Inventory Archive." These will be adds or removes. But the way I handled this the last time I needed to, I built a table that let me define all positive numbers for the quantities and I looked up the transaction type with a Combo Box to store the code in the table.

When it came time to do the inventory, I joined the transaction table to the table that defined +1 for all adds and -1 for all removes as the field "Movement". This table essentially translated a code number in the transaction to a longer name for formal report grouping and summaries. Then in that query, I had something like:

SELECT A.PartNo, A.XACTDate, B.Movement*A.Quantity AS QtyMoved, B.XACTType, etc. etc. from XACT A, XACTLKUP B LEFT INNER JOIN ON A.XACTCODE=B.XACTCODE etc etc.

If you pre-define the relationship correctly, you can even do this as a query-grid thing and forget about the direction of the join.

Use this query as the base for a summation that gives you your inventory as of any given date. Note that you can go back in time because in this design, the date is part of the transaction (as well it should be).

Just another viewpoint on how to manage that particular problem.

 

[nicksource]

Thanks guys, I'm pretty much fixed with the DB design, having stock in/out in one field then (out being negative, in being positive) then running a SUM.

But I'd like it so in the "Stock Out" form, when I enter it as a number, it converts it to a negative, so I don't have to put the minus sign in all the time.

How can this be achieved? I'm very confident I've done what Neil suggested correctly but it doesn't work for me.

What are the other methods? I can't get this to work.

Me.txtQuantity = Me.txtInput*-1
Also tried:
[txtQuantity] = [txtInput]*-1

Just doesn't like it in After Update (doesn't update the field).

Thanks in advance,
Nick.

 

[nicksource]

I've tried in the "Qty" form field on After Update to do:
[Qty]*-1

but it doesn't change it.

I've also tried just
*-1

But it comes up with an error, basically just want to to turn the value I've entered in that form field into a negative for when it inserts into the table.