Question Worth 1000

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>
LVL 5
darrenakinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:

PurchaseOrder
----------------
POID (PK)
CustomerID (FK)
DateIssued
Status
etc.


PurchaseOrderItem
----------------------
POID  (PK,FK))  
LineNo (PK)
ProductID(FK)
QuantitySold                *see note 1
UnitSalePrice
etc.

CustomerTransaction
----------------
TransactionID(PK)
ProductID(FK)
TransactionType    (Purchase, Sale)
CustomerReferenceNo    (PO#, if you like)
CustomerID or POID   *see note 2  
QuantityOnHand         *see note 3
ProductQuantity    
TransactionDate
etc.

note 1: Assumes quantity sold and quantity shipped are always the same.  Otherwise, you need more tables to track shipments and shipped items, by PO.

note 2: Depends on whether you want to track sales by Customer or by their purchasing PO.  To do it by PO is a little over the top. When your Customer sells an item, do they really report back to you which PO they purchased it on??? If not, then you are only guessing...so what's the point--just track by Customer.

note 3: This column is derivable, but to derive it means aggregating the transaction history since the beginning of time.  Maybe that's OK, but sometimes it's a good idea to store the running balance.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
puppydogbuddyCommented:
darrenakinAuthor Commented:
Puppydogbuddy,
I have that template, This template does partially what I want it to. I think My Brain is stuck. I know to you guys this is so simple. On that template I need for it to have another place for the customers PO, and then I also need to be able if the customer sells 3 of the 10 that I sent him it needs to show he has 7 on hand, but next week he sells 3 more. I need to be able to add another record for his NEW PO but that new PO needs to edit the OLD record of MY original PO.
10 Holiday Gifts Perfect for Your Favorite Geeks

Still have some holiday shopping to do for the geeks in your life? While toys, clothing, games, and gift cards are still viable options for your friends and family, there’s more reason than ever to consider gadgets and software.

puppydogbuddyCommented:
Sounds like the template is a good start.
1. Go into the relationship window, show all tables.
2. Compare table layout to what you need
3. add any missing tables and/or fields
5. look in other templates and see if missing functionality exists there
4. try and use wizards to generate missing queries, forms, and reports
5. anything you can't do with wizards,etc. will have to be done the hard way.
puppydogbuddyCommented:
Here is another link to free template db's....you should find useful functional components in the  service call management db.

                  http://www.aadconsulting.com/dldopcde.html
dqmqCommented:
>On that template I need for it to have another place for the customers PO

Well, you need a child table (CustomerTransaction) to record the customer's PO/quantity sold because the customer can have more than one PO/Sale for each of your PO's.  The question is whether it is a child of customer table or a child of your PO table. Either way, you can figure out how many the customer has on hand.  The difference is that as child of your PO,  it's a lot more record keeping because the customer must report back the sale, his PO and your PO.  Do you think the customer will keep track of your PO#'s for his inventory?  What happens if the customer sells 2 items on one of his PO's, each originating from a different one of your PO's?  What happens when the customer reports he sold 6 items on his PO 5, your PO 59, but you only show 5 items on PO 59? I digress.

>and then I also need to be able if the customer sells 3 of the 10 that I sent him it needs to show he has 7 on hand, but next week he sells 3 more. I need to be able to add another record for his NEW PO but that new PO needs to edit the OLD record of MY original PO.

NO!  Every time the customer reports a sale, you add another row to the customer transaction table and reduce the on-hand quanity for that item.  Every time the customer reports a return, you add another row to the child table and increase the on-hand quantity for that item.  Every time the customer buys more product, you add another row and increase the on-hand quantity.  Some times when the customer takes inventory, you add another row and adjust the on-hand quantity.  Each row has a place for the customer's PO #, if you want to track that.   Each row can also have a place for your PO#, too.  I just think that except possibly for sales to the customer, that will be a nightmare.

darrenakinAuthor Commented:
Please Close this Question and remove
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.