Best way to prevent multiple postings

[HillTJ]

Hi friends, i am progressing well with my stock database & have elected to have a field in each product to retain the actual stock of an item. I know the purests disagree with this te hnique.l have a table that captures the new stock item then posts the quantity based upon an update query. I also have a "posted" flag field. I display the "stocked in " items in data sheet format. I currently do the post from the after update event of that flag. My intention is to only post the items once upon selection of the flag (tick box = yes). What's the best way of ensuring this?.

 

[Minty]

Seconding what June says.

Having created a variety of small stock management systems, calculating the stock on hand live is the only way to go, normally by simply adding all the ins and outs from the last stock check event.

It also makes you document in the transaction history any stock check corrections so you have a history, rather than arbitrarily "adjusting" the number and wondering why it's different.

 

[HillTJ]

Minty, thanks. This is the very reason i asked, before i getting in too deep. It just worries me that the "correct" method may be too complex for a novice. I work for a processing company where we buy in raw materials, convert it into products then dispatch. So it's not simply stock in, stock out. Appreciate your advice. Will study up on Allan Browns technique.

 

[Minty]

Without getting into the detail too heavily (I don't know your business model), you should have something like a top level Parts table, with PartID, PartNumber , PartDescription , Min and Max stock levels perhaps.

You transaction table is then something like PartTransID, PartID_FK, UsageTypeFK, UsageRef , TransQty , TransDate, Location (if you want parts stored in a location(s))

Your Usage type is another table where you set up your usage descriptors, maybe with some cost codes, and some reporting groups something like UsageID, UseDescTxt , ReportingOptId , CostCode , etc
A typical usage type would be Stock Replenish , Product Build , Write Off , Stock Take etc.

In the Trans table you record the usageIDFK, and in the UasgeRef you could record a batch or final product code to help track what you used it on.

This gives you some of the basics for ins / out and the ability to calculate the quantities on hand in a location.

Stock tracking is not as obviously simple as you might think, but if you get the basic structure correct initially , it makes it much easier process to grasp.

You may need to account for different manufacturers / suppliers of the same incoming parts, you may need to record dated costs. Start with a paper model of your inputs and outputs (reporting requirements) design the tables to accommodate all the information to produce those and you should be on your way. Pretty forms and other things should be the last thing you get into.

 

[HillTJ]

Hi, had a good look @Allen Browne's code. My complication is that we stock in bulk material into a number of silos then withdraw raw material from them for processing. When i stock in materials i assign a silo at that point. I also need to be mindful of each silo's capacity. To the best of my study, Allen does not stock into particular locations. How do i build in this functionality into Allens neat code?. I'm sure it can be done & i'd appreciate the guidance.

 

[MattBaldry]

I have actually designed a fully working manufacturing database with stock management, BOMs, PO's, SO's. I tried both methods, and the transaction table side of it was the method that worked most reliably. At work we use Sage Accounts, the stock there is managed by an updated field and goes out of sync at least once a day on a multiple number of products. Feel free to PM me if you need an advice.

~Matt

 

[HillTJ]

Hi, i took onboard the general consensus & started experimenting with a select query involving looking up the grade then summing the quantity. Worked fine. So..., is there any problem in converting this sql string to vba & including it in my code? That would capture the "stock in", then generate a similar query for "sales" & subtract one from the other? I could see this approach becomming slowed with lots of records. I Like the logic Allen Browne (hero) uses to commence the "count" from each stocktake to reduce the size of the data. Any suggestions on how to incorporate this?

 

[Minty]

If your transactions table has negative quantities for stock out and positive quantities for stock in then it's one sum based around the value of the last stock check (By Date).

First off, write a query to return just the records since the last stock check, then do the sums and groupings on that.

Make sure that if there is no stock check date you set the "initial value" to 0 rather than null.

 

[gemma-the-husky]

I haven't read the entire thread, but you really do not want to store the current stock.

Calculating the current stock by adding all transactions from the last stock-taking date is absolutley trivial, and can always be done at run time. Storing and managing a current stock value gets diffcult.

What if you delete some unwanted stock records? Insert a missing record? Adjust an old stock take count? If you just sum the transactions, there's no work to do. If you store the stock on hand you have complications.

 

[gemma-the-husky]

Out of interest MS used to do a sample inventory database that I used to call Dharamsala Tea - the first record you saw was for a product called Dharamsala Tea. The only thing I didn't like about this was that they stored inventory out as positive, rather than negative, but it's quite easy to change that. Otherwise it's a tremendous learning tool. Storing inventory out as negative makes it much easier to manage inventory quantities, as already pointed out in this thread.


The MS A97/A2003 samples were really good, but were all removed after A2007. I was able to find them on a paid-for site (1 free download per day). It was a great shame, IMO.


Anyway, here's the Tea database
View attachment Dharamsala Tea.mdb

 

[HillTJ]

Thanks very much. You have got me on the right path. What's the best way of checking that a record for an item stocktake has been added or not. I need to check whether to sum from the start of data collection or from the result of last stocktake. I'll have a look @ the example, but how do i open an .mdb file? I haven't encountered this format before? THANKS for your patience, i wish i knew enough to help others!

 

[gemma-the-husky]

Thanks very much. You have got me on the right path. What's the best way of checking that a record for an item stocktake has been added or not. I need to check whether to sum from the start of data collection or from the result of last stocktake. I'll have a look @ the example, but how do i open an .mdb file? I haven't encountered this format before? THANKS for your patience, i wish i knew enough to help others!

I would store the start date for stock calculations in each material record. Then you can do stock adjustments for individual products. Insert an "opening balance" stock record type for the appropriate date.

You could store a single date in a company settings table, as an overall value if that works better.

The Tea database is an A2003 database, and will work just like a accdb. You can just convert it to an accdb if you want.

 

[HillTJ]

I'm doing pretty much that & adopting modified Allen Browne code to generate current stock calcs. Have not got that working yet, it's such a spider web of relationships to work through but i'm encouraged by the support. I'd really like to have someone critique my project at some point.

 

[ssian]

Out of interest MS used to do a sample inventory database that I used to call Dharamsala Tea - the first record you saw was for a product called Dharamsala Tea. The only thing I didn't like about this was that they stored inventory out as positive, rather than negative, but it's quite easy to change that. Otherwise it's a tremendous learning tool. Storing inventory out as negative makes it much easier to manage inventory quantities, as already pointed out in this thread.


The MS A97/A2003 samples were really good, but were all removed after A2007. I was able to find them on a paid-for site (1 free download per day). It was a great shame, IMO.


Anyway, here's the Tea database
View attachment 74716

Thanks a lot for the sample of the database on the Dharamsala Tea.

What if i have another variable besides the category? Like incorporate Color say Dharamsala Tea, it comes with Black and Brown colors and i want to differentiate them.. So when i want to find out what's my over all inventory on Dharamsala Tea, the product it has say 100u, while showing Black 41u and Brown 59u?

 

[jdraw]

So this color attribute black/brown only applies to the Product Dharamsala Tea?
You could add a color field to the Product table. You would have to maintain the color value wherever applicable in your processes.
You could get inventory on Dharamsala Tea with a query that includes Color.
I downloaded the mdb provided by Dave (gemma-the-husky) and converted to accdb.

I have attached a png of the relationships
.

 

[zeroaccess]

Then there should be Dharamsala Black Tea and Dharamsala Brown Tea as separate items in your products table.

 

[gemma-the-husky]

Yes. Black Tea and Brown Tea are two different things, I presume. You probably don't want to know there are 100 packs of tea, some of which is black and some of which is brown. More likely you want to know there are 63 black teas and 37 brown teas. However all of this comes back to your data and your system. This is all part of the initial data analysis phase, deciding precisely which data you need your system to manage, and how that data is structured into different tables.

It might actually not matter whether the tea is black or brown, other than for tracing purposes, say, in which case you could just add the information to the table as "text notes". If you add a specific black/brown indicator, then the danger is you will start entering black or brown against other products where the distinction is meaningless