Storing all the stock items everyday?

[Faisel]

Hi,
I have a developed a POS application using MS Access. I have say, 200 items. I want to store stock at hand of the 200 items everyday in a table.

example

stock_Date|prod_code|stock_hand
12/12/2010|001 |2
12/12/2010|002 |5

If I store like this, everyday I will get 200 rows and for an year, I cant imagine. Is there a way out to store everyday's stock in single column and retrieve it ?

Thanks in advance.
Faisel

[jimatqsi]

Please explain your question or concern more clearly. It sounds like you are concerned you will get "too many" rows and columns if you do as you described. Storing all the data in a single column would not help one bit with that, it might even make the same data require more storage space than storing it appropriately in a correctly designed table.

If you are truly storing around 200 rows daily, one doesn't need to imagine the result for a year, one only has to calculate to know you'll store around 7,500 rows or less in a year. That's a mere pittance, nothing at all. I'm doing the same thing with 5,500 rows and many more columns and the table is approaching 2 million rows. (and it saved my butt just the other day when somebody questioned the accuracy of the stock level).

What problem are you trying to solve?

Jim

[nico5038]

The general approach is to have your stock "controlled" by deliveries (= +) and sales (= -).
So just record the product deliveries and the individual sales in separate tables.

Once in a period (week/month/year) you sync the administrative qty with the actual quantity and the difference ( + or -) will have to be accounted for.

Idea ?

Nic;o)

[esperance]

Does knowing the amount of stock each day matter? Because you could remove the date field, add a "new_stock" field, and use it to make a calculated field that gives a new total.

Also, POS means point of sale, right? Not piece of sh**? :D

[Faisel]

actually my concern is this.

I will tell you an incident.

yesterday my QOH for a particular item is 5 and today i have sold 3 and my QOH at EOD is 2. I want this balance 5=3+2. If the balance is not there, then I can trace back there is a problem today. To do this, I want to store QOH everyday and check on someother day when there is a stock error.

QOH - quantity on hand

[nico5038]

So what's the difference between a stock error on the 12th or the 15th?

Dutch stores/factories check in general their inventory against their administration once a year, giving them the "leakage" what's needed for the accountant.

Nic;o)

[jimatqsi]

Relevant war story from last week ... customer called to ask why his inventory was off. He counted item X last week and found 11, but the computer said 3. So he did an adjustment of 8. Several days later he noticed that the count of that item was again off, this time by 2. Computer said he had 7 but he could find only 5. Here's how it happened, in sequence:

UPS delivered two units of item X.
They did a stock count of Item X, finding 11, and making the adjustment of +8
They sold 6 units on 2 or 3 different sales orders over a few days, bringing them to 5 on hand.
They finally got around to entering the P/O receipt of 2 units. When they entered the P/O receipt they used the actual date of receipt, not the entry/posting date.

So at this point the computer has a quantity of 7 for that item but there are only 5 in the warehouse. So they call Jim and bark about the computer not counting right. Fortunately, I had an on hand stock tracking table like Faisel is setting up AND time stamps on the P/O and General Ledger entries, so I was able to reconstruct events in correct order.

Now when they back date a P/O receipt I'm going to check for stock adjustments done since the receipt date and give them a message warning about this scenario causing a stock error.

Jim

.

[nico5038]

The problem here is the fact that not all receipts have been booked when doing the stock count. As demonstrated, only logging the changes will enable the reconstuction.
Daily stock counts won't solve this problem however. The advise should be to keep periodic stock counts after processing all receipts and sales.

Nic;o)