Multiple Orders (97/2K)

Topic

New Reply

I have a db used to store data about orders taken by a home delivery chinese restaurant.
I have a main form displaying customer info and a subform showing details of a customer order. While testing it, I realised that I can only store one dish per customer when in reality a customer may order several dishes and multiples of the same dish. I realise that I can save each customer order one at a time, reselecting the same customer until the order is complete, but this seems inefficient.
What options have I to resolve this issue?

Cheers

Rob

Reply | Quote

Replies

Rob,

Your options are many. So, for the time being, I’d suggest that you take a look at the Northwind database – a sample that comes with Access – and look at how the Orders form is put together. Remember to look at the table structure, including Relationships, as the table structure will determine how successful your db will be.

Good Luck!

Tom

Reply | Quote

Tom,

Thanks for the suggestion. My list of sample databases does not include ‘Northwind’. I’ve looked at an Orders and Invoices db which has been useful but not exactly what I had in mind.
My idea is for a new form to open when a Customer is selected. The form shows a number of check boxes (each with a quantity drop down list) that corresponds to the available dishes. The appropriate selections are made and the order saved.
Is it possible for each checked box to be saved as individual records for a particular customer?

Many thanks

Rob

Reply | Quote

You should have that one as a part of the normal install for either version of Access. If you don’t then someone has deleted them. To get it back try uninstalling just the sample databases, and then reinstalling them.

Reply | Quote

Rob,

Northwind – the database – is available online here for Access 97.

For Access 2000, try this link.

HTH,

Tom

Reply | Quote

You indicate that you are using Access 97 and 2000. An observation (this is not the only solution, and might not even be the best solution) – you have an option in A2000 that you don’t have in A97; sub-sub-forms. In your subform showing details of the customer order, you could have a sub-sub-form indicating the dishes.

Reply | Quote

Douglas

Thanks for the suggestion. Would you elaborate a little.

Cheers

Rob

Reply | Quote

As Tom has suggested, look carefully at your table structure.
As Douglas has mentioned A2000 being a better solution than A97, this is generally correct. However, A97 can handle a Mainform, a Subform and a further Subform linked to the first Subform via an extra control on the Mainform, so A97 can handle this solution.

Tell us about your table structure and we can help guide you.

I would have a table structure like:

tblCustomer would hold:
CustomerID (autonumber)
CustName
CustAddress
etc..

tblCustomerOrder would hold:
CustomerID
OrderNo (Autonumber)
DateofOrder
TimeofOrder
etc…

tblCustomerOrderDetails which holds:
CustomerID
OrderNo
LineNo (Autonumber)
Line (Line number from Menu)
Quantity
UnitCost
ExtendedCost
etc…

HTH
Pat

Reply | Quote

Tom and Patt,

I’ve looked both at the Northwind db and Patt’s suggested table structure. I now see that I’ll need to include another table along the lines of the tblCustomerOrderDetails. What I will need your help with is:

Patt writes

Would you kindly give more instuctions.

Many thanks to all

Rob

Reply | Quote

Have a look at the Sales Totals form in the Solutions.mdb database that comes with A97.
Although this is a read-only type of Mainform, Subform, Sub Subform you should still get the idea on how it works.

I have used this technique a number of times.
Also notice on the Mainform the hidden control T-Year, this is used to do the linking between the Subforms (SalesTotalsSubform2 & SalesTotalsSubform1).
Pat

Reply | Quote

Access 97 had sub-subforms, but you couldn’t nest subforms as deeply as you can in 2000 and later. I believe the nesting limit in 97 was 3 levels: form, subform, sub-subform.

Reply | Quote