darrenakin
asked on
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>
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>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://www.aadconsulting.com/dldopcde.html
>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.
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.
ASKER
Please Close this Question and remove
http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011366681033