Inventory (?) Issues (1 Viewer)

I'm going to take a shot at adding some value to this, but no guarantees because I can't open your attachments from work. (Our firewall is hair-trigger on such things.)

Rhetorical question number one... What do you do about stuff on hand on a given date when no transactions occurred on that date?

You first recognize that the amount on hand in a transactional inventory is ALWAYS the DSum of the appropriate records on or before a given date. If you have a pot-load of transactions on the 1st and nothing on the 2nd, the DSum of all transactions on/before the 1st should equal the DSum of all transactions on/before the 2nd. I do not see where a day with no transactions makes one iota of difference. The DSum criteria simply needs to do something with "[XActDate] <= [TgtDate]" ( <= rather than =).

Sub question: How do you tell that you had no transactions on a given day?

By stepping through the dates in VBA code and doing DSum one day at a time, you get the incrementals. OR you can do a DSum for a given date in a single-step type of scan where (in this case) you DO use the = as your date criteria. The output of that query would be easy enough to chart.

Rhetorical question number two... What do you do about stock previously on hand?

If the only transactions you allowed were STOCK ADD (due to shipments from your vendor) and STOCK DRAW (due to customer purchases) then you have insufficient transaction types. You also would need a STOCK RETURN (an ADD due to a customer returning a purchase, if that could happen). You need STOCK SHRINKAGE (a DRAW due to breakage, pilferage, or disaster damage). You might need a STOCK ADJUST (due to a sight inventory overriding the DSum inventory) or a STOCK SUMMARY (due to having decided to archive everything before DATE X).

The STOCK SUMMARY would work like this: Pick your archive date limit. Then compute the stock on-hand at that time into a separate table. Then remove all transactions on/before your summary date. Then go back to that separate table and generate the STOCK SUMMARY transaction (that looks like an ADD starting from 0 on-hand.) By "remove" I mean - copy the old data into an archive table and then erase the records that were archived.

The question that you have to ask your self is... "Self, how long must I keep a record before I can archive it?" Which leads to a question of how far back can someone ask the question of what was on hand on a given date?

If you use the SUMMARY method I described earlier, you can ALSO do a test to see the earliest transaction in the main table. If your date of interest is in the main table, you don't care about the archive table. And if your date of interest is NOT in the main table, run the identical query against the archive table. This way you keep your tables manageable. By the way, if you take this approach, you DON'T archive the SUMMARY transaction itself because it would lead to "double-dipping" on stock-on-hand numbers.

Rhetorical question number three... What about store-specific stock?

That is covered by making a more complex DSum for which the Criteria clause contains a reference to the store ID as well as the stock inventory number.

Well now it seems we're getting somewhere

Thanks for your contribution Doc, I'll answer to all your questions (rhetorical or not) as soon as possible....(sorry I'm doing something else more urgent right now).

Rhetorical question number one... What do you do about stuff on hand on a given date when no transactions occurred on that date?

Click to expand...

Nothing, they are sitting there waiting to be sold, but I want to know their everyday value, in a way I can present. (Value changes almost every day).

You first recognize that the amount on hand in a transactional inventory is ALWAYS the DSum of the appropriate records on or before a given date. If you have a pot-load of transactions on the 1st and nothing on the 2nd, the DSum of all transactions on/before the 1st should equal the DSum of all transactions on/before the 2nd. I do not see where a day with no transactions makes one iota of difference. The DSum criteria simply needs to do something with "[XActDate] <= [TgtDate]" ( <= rather than =).

Click to expand...

Suppose he have a transaction (no matter what) for productA on 1/7/2015 and then a transaction for productB on 1/20/2015 and then two transactions for productA and productC on 1/29/2015. The aggregate query will show only these three dates (1/7/2015, 1/20/2015, and 1/29/2015), products A , B, and C and the DSum column will sum quantities of only these three products (of course with <= operators in Date criteria). What about all other products that remain in stock in a given quantity? What’s the image we have for 1/10/2015 or 1/30/2015 since they are not included in the query because there’s no transaction these days?
Since I have a table with all product prices for everyday of the last two years, in a consecutive way, how do I combine them to get quantity * priceperunit = value for every day?
Am I designing the wrong query by querying only transactions? And which is the right way?

Sub question: How do you tell that you had no transactions on a given day?

Click to expand...

There are simply no transactions, no movement, nothing. No data entry.

Rhetorical question number two... What do you do about stock previously on hand?

If the only transactions you allowed were STOCK ADD (due to shipments from your vendor) and STOCK DRAW (due to customer purchases) then you have insufficient transaction types. You also would need a STOCK RETURN (an ADD due to a customer returning a purchase, if that could happen). You need STOCK SHRINKAGE (a DRAW due to breakage, pilferage, or disaster damage). You might need a STOCK ADJUST (due to a sight inventory overriding the DSum inventory) or a STOCK SUMMARY (due to having decided to archive everything before DATE X).

The STOCK SUMMARY would work like this: Pick your archive date limit. Then compute the stock on-hand at that time into a separate table. Then remove all transactions on/before your summary date. Then go back to that separate table and generate the STOCK SUMMARY transaction (that looks like an ADD starting from 0 on-hand.) By "remove" I mean - copy the old data into an archive table and then erase the records that were archived.

The question that you have to ask your self is... "Self, how long must I keep a record before I can archive it?" Which leads to a question of how far back can someone ask the question of what was on hand on a given date?

If you use the SUMMARY method I described earlier, you can ALSO do a test to see the earliest transaction in the main table. If your date of interest is in the main table, you don't care about the archive table. And if your date of interest is NOT in the main table, run the identical query against the archive table. This way you keep your tables manageable. By the way, if you take this approach, you DON'T archive the SUMMARY transaction itself because it would lead to "double-dipping" on stock-on-hand numbers.

Click to expand...

I’ll continuously need to have information for all stores value formation through long of time periods. I won’t adapt the Stock Summary method, if possible.

Rhetorical question number three... What about store-specific stock?


That is covered by making a more complex DSum for which the Criteria clause contains a reference to the store ID as well as the stock inventory number.

Click to expand...

This is already taken in mind. Criteria for DSum are more than productID and VoucherDate

Problem is not solved yet and won't be, but I mark it as solved as I abandoned it.

Thanks to everyone who tried to help.

Since the question is abandoned, there is nothing really to add, but I will belabor one point for subsequent readers:

Since I have a table with all product prices for everyday of the last two years, in a consecutive way, how do I combine them to get quantity * priceperunit = value for every day?

Click to expand...

Unless the price is fluctuating while products stay in inventory, what I said works for every day whether a transaction occurs or not on that day. If you wanted a bar-chart, say, you would at worst have to build a temporary table for charting in which you picked the date range and then ran the DSUM for each day in the range, storing that in the temp table. Then use the chart wizard to build your bar-chart. Or you could do a report.

When we say you NEVER EVER store computed data, that isn't exactly true. You never KEEP computed data - but if you have special presentation requirements, you can store it, build your presentation, and then discard the computed temporary stuff later. That doesn't imply any violations of normalization rules.

The_Doc_Man said:

Since the question is abandoned, there is nothing really to add, but I will belabor one point for subsequent readers:



Unless the price is fluctuating while products stay in inventory, what I said works for every day whether a transaction occurs or not on that day. If you wanted a bar-chart, say, you would at worst have to build a temporary table for charting in which you picked the date range and then ran the DSUM for each day in the range, storing that in the temp table. Then use the chart wizard to build your bar-chart. Or you could do a report.

When we say you NEVER EVER store computed data, that isn't exactly true. You never KEEP computed data - but if you have special presentation requirements, you can store it, build your presentation, and then discard the computed temporary stuff later. That doesn't imply any violations of normalization rules.

Click to expand...

Thank you Doc,

As I mentioned before, prices change almost every day, and I want to know each repository's whole value, every day, whether there is a transaction or not.
I never said I have an issue on storing calculated data. The fact that stored calculations will never change makes it even easier.
I know there has to be a temp (or not temp) table of storing this data but I keep saying since I first brought up the issue, that I don't how to do it. I don't know how to combine in a table, given dates (last two years), with ongoing scattered transactions, and do it in an automated way. I didn't understand why and how this conversation has been misdirected to other theories.

Well, I think 3 weeks is enough time of trying, it seems I don't have it with inventory databases, I'll stick with contacts and recipes.