[magusbuckley]

Hello:

I created an Access dbase program for my wife and feel there is probably a more efficient way to store the data. If you're good with Access and are willing to shoot me some pointers, please read on.




The process?

My wife sells merchandise at booths. For simplicity and secrecy, we'll say she's selling beverages. So basically, this is the process. She purchases drinks (Coke, Sprite, Dr. Pepper, etc.) in bulk online. The products are shipped to our house. At a later date, we set up a booth at a local festival and sell said beverages. Of course, whatever is left over at the end of the event comes home with us and gets stored for later use. Simple.



The problem?

The problem is that the wife wants to know how much inventory she has at any given time, what it's worth, etc. She was tryhing to keep track of it all on paper initially. She would, no joke, go into the storage room and count the products once a week or so because she would lose the paper with the totals or she would think she had done it wrong or she would think the number didn't appear to be accurate, etc., etc.



The fix?

I decided to build her an Access dbase program to keep track of inventory. I purchased a barcode scanner that she now uses to scan the products into the program. As an added bonus, I added reports that let her see what products were sold and when so she can use deductive reasoning to determine which products she should stock up on before a particular type of booth or certain time of year.



The problem?

Actually, the program works, but it's a little squirly how I programmed it. I realized it was going to be a little more challenging than I had originally expected because we don't scan inventory "out" as it's being sold at the booth. I'm not scanning merchandise at the booth. How then, can we keep track of it? Well, I have a table that stores current inventory. When we return from the booth, we scan the leftover items and those are stored in a separate table for booth sales. Basically we scan current inventory into the program and when we get back from a booth, we tell the program "OK, we're back now and are going to scan what's left over. If anything is missing, we sold it." When that process begins, the current inventory data is copied into the booth sales table and it ALL gets flagged as sold. Then, as she scans the leftovers, it puts the newly scanned items (the ones leftover from the booth) back into the current inventory table. When she finishes scanning all of the leftover items, it copies the current inventory over to the booth sales table but flags the merchandise as not sold. (Quick example of that process so you can understand it. We have three items - 2 cokes and 1 sprite on hand and in the current inventory table. We sell the sprite and one of the two cokes at the booth and bring the leftover coke home. When we open the program and tell it we have booth sales, it moves current inventory (2 cokes and 1 sprite) to the booth sales table and flag all as sold. Now, the form appears and we scan the leftover coke which gets stored in the current inventory table. Then, when we close the form, that coke (from current inventory), get's copied to the booth sales table...but as a negative. So now, in the booth sales table, I have the following data - (minus coke, minus coke, minus sprite, plus coke). If we totaled that up, it would show that we sold one sprite and one coke.)



The conclusion?

So we've been using this and it works, but I'm thinking there must be a better way to store this data.

I have scanned data stored in the system three times! - once in the current inventory table and twice in the booth sales table. As we move forward in time, I feel like this duplicate/triplicate recording of data is going to slow the program down as it isn't an efficient way to store the information.


In this particular scenario, how would you store the data? Any pointers will be greatly appreciated.

Thanks,

Magus