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. :eek:

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?

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

I would create two new tables - tblDispatch. This table would have an autonumber primary key. It would include information related to a particular dispatch. And tblDispatchOrders. This table would be a relation table and contain only the primary key of tblDispatch and the primary key of tblOrder. The two fields would form the compound primary key for this table.

I would use a mainform to enter the Dispatch info and a subform to select the Orders included in this dispatch. The subform would use a combo who's RowSource was filtered by the selection criteria entered on the main form.

This arrangement of tables is a typical many-to-many. If you don't know how to create the tables and forms and get them to populate properly, download my many-to-many sample from the samples section (you'll need to search for it).

Looks like Wayne types faster but at least we agree :)

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. :)