Help - I can't design PO system to save my life...

[TheAxe]

I'm having a real problem trying to make an inventory, purchase order, invoicing, payment system.

So far I got form to enter products.
I got a form to order those products from our only supplier.
This tracks the onorder and onhand.
It all comes apart when I do the purchase orders from our customers.

I made this form to put info in the following tables:
Orders: OrderID Orderdate CustomerID
InventoryOrder: InventoryID OrderID QtyOrdered

I made this interesting form to enter the data into those two tables. Now I want to ship the orders. Only problem is I need to be able to list all the unshipped orders and then have a field on that form to put in how much is being shipped. We don't always ship the full amount and often have backorders.

After that I need to make invoices. Well the invoices may cover more than one shipment. Then payments which is the same. Im in way over my head here design wise so I would appreciate any advice on how to design this thing.

What shocks me the most is every company under the sun must have a system like this made but I can't find anything on the net describing how to build one.

Any advice?

 

[EPT]

Start access and select File-->New Database, choose the wizard for creating an inventory control database and you might be home free.
HTH
Ed

 

[JoanneM]

Good afternoon


hope you are sitting comfortably I believe what you may need is the database wizard for Order entry and working every thing from there.

Try it
Just go File , New click the databases tab, select Order Entry and make your selections as required this should get you started


regards


Jo

 

[TheAxe]

Thanx but unfortunately those wizards fail to take into consideration two major truths of buisness:

1] There are multiple POs under one invoice.
2] There are multiple invoices under one payment.

Those are the problems that I can't solve. The rest is quite easy. I looked at the wizards but it seems to me that they got stuck exactly where I did.

 

[UBNConsulting]

You are dealing with several entities that must be recognized and represented by tables. Some of the entities (tables) and attributes (fields) are :

Inventory
----------
InventoryID, InventoryName, InventoryPrice, InventorySaleQuantity

Order
-----
You already have that one.

InventoryOrder
--------------

You already have that one. Add Price to this table. Although you have Price in the Inventory table, you may change that from time to time but the price at which you have already sold an item to a customer must stay frozen at that point in time with that specific order and cannot fluctuate.

Customer
--------
CustomerID, CustomerName, Address, etc. etc.

Shipment
---------
ShipmentID, ShipmentDate, InventoryID, OrderID, InvoiceID, CustomerID, ShipmentQuantity etc. etc.

Invoice
-------
InvoiceID, InvoiceDate, InvoiceAmount, CustomerID etc.

OrderInvoice
------------

OrderID, InvoiceID

Payments
---------

PaymentID, PaymentDate, CustomerID, OrderID, InvoiceID, PaymentAmount, PaymentMethod etc.

Hope this helps you go off in the right direction.

RK
UBN Consulting

http://www.ubnconsulting.com