Common record for multiple items

[martinhough]

I have a db with various tables. The main table 'ProductOrders' holds the majority of info (there are others linked to this but for the purposes of this question I doubt have too much relevance).

The db allows users to place orders for items to be purchased; these can be one item or anything up to 20. I need to assign a common number (or id) to any group of orders, i.e if someone orders two items, these need to be grouped; 20 items, these would be another group and so on. I have set up a table called 'Group' with a primary key autonumber field and a field in 'ProductOrders' called 'ProductGrroupOrders' with the intention to create this link and record an 'instance' in the 'Group' table but not sure how to proceed from here.

The user places items onto a pad from a form (merely an aesthetic aspect) and then once finished clicks a button 'Place Order' which whilst the order has already been entered into the table performs additional operations such as firing off email notifications to the respective people who need to administer the orders etc. Is it at this point I need to assign a number/unique ID for the preceding items? Or is there a way of simply linking these two tables (one to many from the 'Group' table) which will automatically provide a unique reference?

I'm sure it should be more simple than I'm thinking it is. Anyone able to point me in the right direction please?

 

[gemma-the-husky]

i would store the working order in a temporary table, with similar fields to the real order table

when you accept it, get the next order number, and then append the working table items to the real table - and at this point you know the order number, so its not a problem.

it also makes it simple to cancel the entry - just clear the temporary table.

 

[martinhough]

Thanks Dave - yes, like your thinking there and that may actually help me with another issue (getting this info in a report consistently!) but not sure how I get one record for these potentially numerous orders - the duplicate table idea would surely just give me another run of separate records? I need one unique ref for each 'batch' of orders.