• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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>
0
darrenakin
Asked:
darrenakin
  • 3
  • 2
  • 2
2 Solutions
 
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.


0
 
puppydogbuddyCommented:
0
 
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
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
0
 
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.

0
 
darrenakinAuthor Commented:
Please Close this Question and remove
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now