Inventory (?) Issues (1 Viewer)

AlexN · Jul 25, 2015

I’m trying to design a database that will keep track of the value of products in stock, for several stores.

So we have days with buys, days with sells, days with buys/sells, and days with nothing.

1st issue is that, for those fine days of no transaction, there will be no record, none, nowhere in the database. So, after several weeks, months or years someone wants to see what the total product value was for such a day it would be a problem.

2nd issue is that, having a bunch of transactions, the DSum in a query will only aggregate the products referred in each transaction, and there’s no way of bringing up the stock already existing.
In simple words, let’s say we bought 20 units of Product A on Monday, 30 units of Product A on Tuesday, and then on Thursday we bought 40 units of Product B. On the aggregate query I’ll have a column that will show that I have 20 units of Product A on Monday, 50 units of Product A on Tuesday, and 40 units of product B on Thursday. But on Thursday I also have the 50 units of Product A, and they won’t show.

I’ve been searching everywhere in the web to find some guide of how to overcome this, I carefully examined Northwind database which almost everyone suggested I should study, but came with no solution (at least I didn’t recognize one).
So, if anyone of you kind gentlemen had some enlightening idea it would be much appreciated.

Thank you.

Nick

pbaldy · Jul 25, 2015

Generally speaking, you store transactions. Your "day with no transactions" isn't an issue. The product value on any given day is the sum total of buys less sells for all transactions up to and including that day.

I'm not really following the second issue, but my gut is that the same answer applies. Maybe Allen's general discussion helps:

http://allenbrowne.com/AppInventory.html

AlexN · Jul 25, 2015

pbaldy said:
Generally speaking, you store transactions. Your "day with no transactions" isn't an issue. The product value on any given day is the sum total of buys less sells for all transactions up to and including that day.

I'm not really following the second issue, but my gut is that the same answer applies. Maybe Allen's general discussion helps:

http://allenbrowne.com/AppInventory.html

Thank you for your answer pbaldy
As I said, I searched a lot, and it's impossible not to run into Allen's page.
My structure is almost identical. Since you mentioned it, I would like to know where should I put this code he provides to get results, although I'd rather use the second alternative (of storing the data) in order to be able to use it for charting and presentation.
Don't understand how though.

Thanks again for dealing with it!

pbaldy · Jul 25, 2015

Typically if you save anything, it's the quantity on hand. You update it with each transaction, but as Allen discusses you have to make sure you cover every possible way the user can add/edit/delete data. Having quantity on hand stored wouldn't help you determine what was on hand last week/month/year.

I've done inventory apps, and I have never tried to store quantity on hand. It's easy enough to calculate from transactions, and also easy to calculate inventory at any given point in time. I've never tried to implement the "stock take" functionality that Allen described. That said, I've never been involved in a really heavy-duty warehouse situation where it might be required.

pbaldy · Jul 25, 2015

Oh, the code he provided would go in a standard module so it could be called from anywhere in the app.

AlexN · Jul 25, 2015

pbaldy said:
Typically if you save anything, it's the quantity on hand. You update it with each transaction, but as Allen discusses you have to make sure you cover every possible way the user can add/edit/delete data. Having quantity on hand stored wouldn't help you determine what was on hand last week/month/year.

I've done inventory apps, and I have never tried to store quantity on hand. It's easy enough to calculate from transactions, and also easy to calculate inventory at any given point in time. I've never tried to implement the "stock take" functionality that Allen described. That said, I've never been involved in a really heavy-duty warehouse situation where it might be required.

Well I really wouldn't want to implement the "stock take" functionality either. And handling edits/additions/deletes doesn't seem to be an issue for the time being.

But, I still can't figure out how I get to know which and how many products I have in hand, each calendar day. I want to store these values, for future presentation and charting of stock value along time. But aggregate query won’t show all products in hands, only these referred to each transaction, and further more won’t show products in hands for the days there are no transactions.
So I attached a little sample database, showing pretty much the table structure I’ll use. If you’d be kind enough to give me some leads how to proceed I would be grateful.

Thank you.

jdraw · Jul 25, 2015

Attached is an alternate model for consideration. TransType values Buy/Sell.
If you don't have a "stock take" functionality somewhere, how will you know what stock you have?
I agree with PBaldy that working with transactions is the common/proven approach with stock control. The AllenBrowne material is often referenced and is considered an authority.
If you want to separately store Stock On Hand by Date for charting etc, then experiment a little and see what evolves. But it seems it may be doubling the effort --you can calculate from transactions when needed, or you calculate from transactions and store the result for subsequent use.
As for editing and deleting of any data, make sure you account for any and all saved data (stored values) in your processes.

Good luck with whatever approach you decide upon.

AlexN · Jul 25, 2015

Thank you for your contribution jdraw

Of course your model is under consideration but, whatever the approach shall be, it’ll come a time that I’ll need to create a query with a DSum column. That column only shows the running total in hands of the product specified in each row (transaction) for that very date. It doesn’t show rest of products for that day, and I don’t know how to accomplish this.
In addition to this, there we’ll be some time periods with no transaction at all, or no transaction for certain products. I can’t figure out how I can calculate products (about 200 of them) in hand for that specific period, since aggregate query only handles transactions.

Let’s say, he have a store that works for 6 months per year (not consecutive), but we want to know the daily value of stock in hand for the whole year on a daily basis, because product prices change on a daily basis. And we want it stored in some table/query in order to present it (in a chart mode or something) sometime.

I don’t know an alternative way to explain this. Sorry my English isn’t so good, but I think I made myself almost clear.
Thank you again for dealing with it.

Nick

jdraw · Jul 25, 2015

Your English is very good. My recommendation is to do some trial and error for a proof of concept.

Take some sample data (concoct what ever you need), and identify some specific charting, reporting, or querying scenarios. Then, test your model and database. Identify any issues, resolve or seek alternatives. Do this before setting your design --it could save you time in the longer term.

If you do not have transactions for a specific product on a specific date, then its StockOnHand value will be based on the latest date on which there was a transaction. If you have 195 widgets on July 15 and there are no transactions since, the stockOnHand for widgets would still be 195 whether you run your query/report on July 19 or 24.

AlexN · Jul 25, 2015

Thanks again!

Take some sample data (concoct what ever you need), and identify some specific charting, reporting, or querying scenarios. Then, test your model and database. Identify any issues, resolve or seek alternatives. Do this before setting your design --it could save you time in the longer term.

I've already done that. And that's how I stumbled on the aggregate query. Still no solution in mind.
I thought that if I run Allen's code somehow everyday (product prices are updated every day) I would create a table with StockonHand values on a daily basis. But Allen's code is single dimensioned. I want to have that information for four(4) or more stores (in the same table of course) and that makes it multidimensional. And I don't know how to modify it.
(Well I've got big plans with poor knowledge, I know).

I know it's gonna take some time (months) but I'll figure out something.

Thanks again!

jdraw · Jul 25, 2015

If you look at the transaction table in the model I proposed, you can handle any number of Stores (in fact Stores, Products, Dates.....).
If your specific issue is getting numbers for specific charting/reporting, then define that requirement and let's see how it goes.

Minty · Jul 25, 2015

I'm with Jdraw on this one - If you have 4 or 20 or 200 stores it shouldn't matter. The store is simply(should be) a criteria in your queries.

I suspect you may find that you can avoid the DSum with either some custom functions or more simply some properly designed grouped queries.

The general rule of thumb is if you can calculate it you shouldn't store it. There are some exceptions e.g. maybe an invoice total where the tax rate may change over time, but if you store the correct information initially you should be able to always calculate the desired report. (Even in the above example if you store the tax rate/code you don't need the total)

AlexN · Jul 26, 2015

Thank you Minty for your contribution!

I’m not against jdraw. There are some ‘business rules’ though (every store has its own system of invoicing, an invoice or delivery receipt could contain multiple different products, etc.) and for user interface reasons I’d prefer the table structure I attached here.

After all, later on, when you reach the point of combining transactions, you’ll have to create a union query that’ll be pretty much the same as jdraw’s main transaction table.

Problem still remains though. You see there’s already a table with every day product prices for the last two years, that’s being updated daily. I don’t know how to combine stores’ everyday StockOnHand (for all products) with this table, in order to have total stock value of the store on a daily basis (a total value history that is), specially for days where there’s no transaction.

Thanks!

AlexN · Jul 28, 2015

No ideas uh? Me neither!

jdraw · Jul 28, 2015

Alex,

We gave you our ideas.

If you look at your latest testdb.jpg, can you write down the business rules based on your relationships. If you have already done that, can you post those rules?

AlexN · Jul 28, 2015

jdraw said:
Alex,

We gave you our ideas.

If you look at your latest testdb.jpg, can you write down the business rules based on your relationships. If you have already done that, can you post those rules?

These are the basic business rules :

Every store handles all products
Every store does 4 kinds of transactions, buys, sells, money transfers to, and money transfers from (we don’t care for last two for the time being).
Every store has its own vouchers and makes its own stock report
Vouchers can only be Invoices (Sells) or Delivery receipts (Buys)
Stores can’t sell quantities bigger that they have in stock
Every voucher can have multiple products, and multiple unit prices for the same product.
Transactions for all stores are entered by a single user according to vouchers.

jdraw · Jul 28, 2015

What exactly is a voucher? The definition I found shows it having 2 purposes.

A voucher is a certificate or document that either allows you
to purchase something or
proves that you paid for something.

Click to expand...

Do you have to account for
ReOrderPoints
ReOrderQuantity
BackOrders
PartialOrders??

What process/logic is involved if Customer A orders(buys) 20 units of X, but the store only has 12 units on hand?

Here is a link to free generic data model dealing with Customers and Invoices. It may be useful to see some relationships typically involved.

AlexN · Jul 28, 2015

jdraw said:
What exactly is a voucher? The definition I found shows it having 2 purposes.

That's the exact definition, a documented proof of Purchase or Sell.

Do you have to account for
ReOrderPoints
ReOrderQuantity
BackOrders
PartialOrders??

No there aren't such issues.

What process/logic is involved if Customer A orders(buys) 20 units of X, but the store only has 12 units on hand?

Customer buys only 12 or buys from another store.

jdraw · Jul 28, 2015

So, your business rule is
A store orders more Product when Stock onHand of that Product reaches 0.

AlexN · Jul 28, 2015

jdraw said:
So, your business rule is
A store orders more Product when Stock onHand of that Product reaches 0.

It could be, yes, but since there are other stores, or there’s no obligation for any store to retain high availability for all products all the time, it isn’t for the time being.
But….
What’s that got to do with my goal? I just want to able to know each day’s stock value for all products, in all stores in a way I can present it.

To specify a little bit more, let’s say that for January we have 10 transactions (buys or sells it doesn’t matter) for all stores, for 10 products (out of 200).
Aggregate query will show ten records, for these specific transactions, with a column showing totals for these specific products on these specific dates. But what about the other (190) products? What about the other days?
In addition we have a table with everyday prices for all products, for whole January. How do we get to combine quantity in hands, for all products, with prices to get values? Every attempt resulted nulls and errors for the dates with no transaction.
It’s obvious I got the whole thing the wrong way but I don’t know the right one.

Inventory (?) Issues (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Wino Moderator

Registered User.

Attachments

Super Moderator

Attachments

Registered User.

Super Moderator

Registered User.

Super Moderator

AWF VIP

Registered User.

Attachments

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread