I'm fairly proficient with Access, but I can't seem to wrap my head around the best way to set this one up. Basically, I want to track milk milk sales for a small store. Main features would be as follows:
Chart current inventory
Track sales and losses (both recent and long term) by date
Suggest Order Amounts (Use current sales and inventory levels to determine minimum to order)
I've been looking at different inventory management databases, but none seem to quite do what I want. I don't need to track customers and all milk comes from a single supplier. I'm not trying to track costs, invoices or anything to do with billing. All I want is the inventory sales, orders, and losses.
Would it be best to have three seperate tables--a Sales Table, Inventory Table and Losses Table? Each table would be basically:
(PK) ID
(FK) ProductCode (Link a Products table listing the different types of milk.)
Date
Quantity
Would it be better to have a single table (instead of the three) that has a field to specify if the record is a sale, inventory count, or loss.
I just can't seem to wrap my head around how to set this up and generally that means that I'm looking at it wrong.