Running Totals Several Products

[websightdesign]

Hi Guys

I am having a little trouble with being able to resolve this problem i was wondering if anyone here could help me if possible.

I am currently trying to design a table where i can enter approximately 15 different products, each of these products will have input stock which will be when each of the items is delivered and we know how many have been received this can be entered in to the table. This information is entered at least once a week and so this total will change constantly, we then enter individual customers each day to either this table or another table and put down which of the products each customer takes so this information will be deducted from the running total of only the one product that the customer had taken.

Each transaction must record the date either via current date or enter the date manually aswell as what they purchased and the amount taken, i can reference the customer, there address, and customer number but i would like a little help on how i am best to structure the table to have the correct running totals once the stock has been reduced.

Any help would be much appreciated.

Thanks
Brad

 

[NickH1]

You should keep each bit of data in a separate table. In this way you retain a complete picture of what has happened. So you might have:-
1. Customers table (Customer ID, Name, address etc)
2. Product table (Product code, description, price etc)
3. Sales table (Date, Customer ID, Product, qty etc)
4. Receipts table (Date, Supplier ID, Product, qty etc)

If you start with zero stock then the sum of all the receipts, less the sum of all the sales for each product is the current stock. Since this probably isn't the case you will also want a stock file

5. Stock File (Stock date, Product, Qty)

So the current stock is the qty in the Stock file plus the sum of all the receipts, less the sum of all the sales for each product, restricting you query to those records after the Stock Date. You may want to generate a new stock record every day.

You might want a Corrections table if the actual stock does not match what the calculation says it is (breakage, shrinkage) but you could put this in the Sales table and give it some special code.

You mention 15 products. The above structure lets you have as many products as you like. You don't want to create a table with one field for each product. Use queries on these separate tables to produce results in the format you want. Check out some of the Access samples for examples of this sort of thing.

 

[gemma-the-husky]

personally i would have a single table for stock movements rather than a separate one for sales/receipts/adjustments, with a transaction type to identify what sort of movement it is. - and for sales this table would include the date and customer etc. I would include stock balances here, rather than in the products table, but you would typically only store the stock balance when you do a stock take, replacing the previous stock movement history. That way you only have to add (live) items from 1 table to caclulate the stock balance.

the point is, though that you shouldnt normally store an actual stock balance - just calculate it when you need it, from the base tranactions. if you want to see the detail for any stock item, its very easy to display all the transactions

 

[NickH1]

I see the Husky is on my trail! I know this could develop into a long thread but... whether you have a single file for stock movements or separate files depends on the level of detail you want. E.g. a Sales movement relates to a customer, a Receipt movement relates to a supplier and a stock correction relates to neither. Your customer might buy single items, your supplier might supply case quantities and for your stock correction you might want a reason and an account code. You would not want fields for all these different things in one table where half the fields weren't used depending on the transaction type.

So, yes, put them all in one table to keep it simple, separate tables if you want to develop a stock management/order processing system.

It can be convenient to be able to directly access a 'current stock' total. This would mean updating a stock figure every time any sale/receipt or change was made. Business systems would do this as e.g. you don't want to do long calculations for every product on your web site, every time a page is visited, to work out the current stock. But I am getting way out of line here as I guess Brad is not looking at doing anything beyond tables, queries and forms i.e. not code.