Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Orders (97/2K)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    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

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    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.

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    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
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    Douglas

    Thanks for the suggestion. Would you elaborate a little.

    Cheers

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multiple Orders (97/2K)

    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.
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multiple Orders (97/2K)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    Rob,

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

    For Access 2000, try this link.

    HTH,

    Tom

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Orders (97/2K)

    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

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

    Would you kindly give more instuctions.

    Many thanks to all

    Rob
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multiple Orders (97/2K)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple Orders (97/2K)

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •