I am in a real pinch, I need a very simple Inventory Database. Here is what I need:
Table 1 - Products
"Fields" A.) Product ID - I want to enter it letters and numbers (PRIMARY KEY)
Field B.) Product
Field C.) Cost
Table 2 - Customers
Field A.) Customer ID - I want to enter it, letters and numbers (PRIMARY KEY)
Field B.) Customer
Field C.) Address
Field D.) City
Field E.) State
Field F.) ZIP
Firld G.) Phone
Field H.) Fax
Field I.) Cell
Field J.) Email
Field K.) Website
Table 3 - Orders (Heres what I cant figure out, here is what I need, and here is how it works)
We cut a PO (example PO SO-9876) Pick the product and Qty that we are selling to our customer.
Heres the crazy part, we want to keep track of the Qty of products that our customers have on hand. Lets say that we shipped 10 of Product A. Customer recieves it and they put it in stock, then they sell 3 and they issue a PO back to us (different from the PO that we issued, but we need to be able to enter there PO in the original order form. We then need to be able to enter they sold 3 to display a QTY on hand for them. Now, when they sell 2 more products on the original PO WE issued, we need to be able to attach that to the original PO to again update there QTY on Hand.
I will also need a report listed by Customers, and then another listed by Products Sold, and then finally a PO report.
<point reference removed jimhorn PE>