Automatically generated Number

[Smilie]

I'm hoping someone will be able to help me get my brain around this problem I'm having! I think its probably simple but I'm just too stuck in it to see out.

So, I have a database (big shock, I know) that is tracking order dispatches. Usually, there is just one order per dispatch and I can therefore use the order # as my tracking device.

But, occasionally we consolidate orders that are coming and going from the same locations at the same time. When this happens, I need the database to automatically create a # to assign to all of the orders that are being consolidated into one dispatch. I still need to track these orders on an individual basis. So, I just want to store this # in the order table for every order that it belongs to. Most of the time this would be a null field except when consolidation occurs.

Can anyone help me with how to make and store a consolidation # that will do this?

 

[WayneRyan]

smilie,

You probably didn't want to have more tables, but here
they are:

***** New tables to handle consolidation *****

Either put all orders into these tables, OR
put only the consolidated orders.

tblSuperOrders:
SuperOrderID
ConsolidationReason

tblSuperOrderDetails:
SuperOrderID
OrderID

***** Regular Order Tables *****

tblOrderMain:
OrderID
CustomerID
OrderDate
OrderStatus
OtherStuff

tblOrderDetails:
OrderID
ItemID
Quantity

tblItems:
ItemID
ItemDescription
ItemCost
OtherStuff

tblCustomers:
CustomerID
CustomerName
OtherStuff

Wayne

 

[Smilie]

Thanks!

Thanks guys. I was on the right track, but I just couldn't complete the thought. Thanks for getting me out of my rut. For some reason I couldn't think about how to join the order table and the dispatch table. But now I think I got it.