Alright.
First, your price and price2 are doing what I suggested in terms of structuring. However, I would suggest that your PRICEID field not be user entered. That can lead to too many problems. All fields that link tables should not be subject to human intervention. If you want an ID that is a visual reminder you can let them type something in but hide the actual linking field from them. In your case have a PRICEID field that's an autonumber in your tblPRICE table but still allow them to enter the AAA1, AAAAA etc. And you still need a junction table that, or alternately an additional field in your tblPRICE2 that tells how many units are in the larger item. For instance you currently have two entries for PRICEID AAAAA and AAAAB but there's no indication how many sticks are in a carton on of item AAAAA, nor is there an indication of how many packet are in a pack.
So let's say you have iphones
You will sell an individual Iphone for 400$
You will sell a case of 10 iphones for 3000$
You will sell a pallet of iphones for 25000$
There are 10 phones per case
there are 10 cases per pallet
So your table would look more like:
Code:
tblUnit
UnitID UnitName
1 Pack
2 Case
3 Pallet
tblPrice2
EntryID Unit UP PriceID Qty SecurityID DOE
1 1 500 1 1
2 2 300 1 10
3 3 250 1 100
So if you were to sell a pallet of iphones and you stored the quantity of individual items per pallet in your tblPrice2 you would be able to correctly add and subtract items from your inventory accurately. The way you currently have it set up you can not unless you are never breaking a pack to get an individual item.
The only other thing I would be curious about is whether or not it's important for you/your company to know that on 7/1/2012 the price of Product X was Y amount, or whether you only care about what the cost is NOW. It would be far easier for financial reporting if you stored the price at the time of purchase in your purchase order database (if you're doing financial reporting) but would not be necessary if your tblPRICE2 table kept a history of unit costs. If it is important there are two things you can do.
1. Keep only one pricing record for each item for each unit of sale (individual item, pack, pallet, case, whatever) and store the cost of that item at the time of receipt/sale (this may be more difficult if you purchase or sell an item at a discount)
2. Keep multiple pricing records for each item and store the starting date and ending date of that item at that price. This is going to be a lot clunkier for a beginner to handle but it would allow a lot more flexibility in your application over time.
I can't really offer you a suggestion as to which would be better to you because I don't know your business or what your requirements are (simply producing a PO/bill from a printout is an entirely different matter than being able to produce monthly/weekly/annual financial reporting for instance)
Your tblPRICE should also include a BRANDID (foreign key to your tblBRANDS) so you could narrow your searches where necessary.
Next, you are recording sales and receipts on four tables. You don't need four, you just need 2. If you're recording incoming and outgoing P.O's and the detail (line items) you can do it with a 2 table structure like you have with tblSales (main PO information) and tblSales2 (detail of PO). You would just need 1 additional field that would indicate whether this was a receipt or sale which you could easily do by forcing data entry on a single, unified, purchase order form.
Windows 7 32bit 
Access 2007
Reply With Quote
