Help with concept needed!

[jagilman]

I have been working on a re-write of a customers order entry and shipping program in Access 97. The old programmer had designed the order table to include the products.(the company only had 2 items to sell)

Now they have many items to sell and needed the addition of an order details table. So far no problem, but where I am running into questions is in the shipping area. They want the ability to ship some of the order items now and some later. The customers credit card will only be charged for what is shipped. I am wondering if the best way to handle the situation is to generate a new order for the items that did not ship (the credit card transaction has about 5 fields that need to be stored in addition to shipping info)

Has anybody worked with somthing similar? The customer has no idea how they want to do it. I would just like to find an effective process that makes sense.

Thanks for the help!

John A. Gilman [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[kctaylordotcodotuk]

I would create an Order shipping table
OrderID
DeliveryID (could be autonumber)
Date
CreditCard
ValueCharged

and an Order Item Shipping table
OrderID
DeliveryID
ProductID
Qty

This allows you to create a query showing the balance outstanding, and also query the Credit Card history, irrespective of the number of deliveries made on an order.

Your suggestion of creating a new order is equally valid, and I suppose you could easily incorporate an option to create the new order or not. I.e. If order was 99% complete then user might choose not to re-create the next order.

The answer may lie in what historic enquiry / reports you need, and how easy it would be to code them in each case.
[sig]<p>Keith C Taylor<br><a href=mailto:TekTips@kctaylor.co.uk>TekTips@kctaylor.co.uk</a><br><a href=

http://www.kctaylor.co.uk>The

Information Gardener</a><br>The C stands for Computer![/sig]

 

[PaulF]

The Military uses a Suffix Code when more than one shipment is required to fill an order, something you might consider.

PaulF [sig][/sig]