- Local time
- Today, 11:06
- Joined
- Feb 28, 2001
- Messages
- 27,223
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.
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.