Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple tables on 1 form (Access 2003)

    To all,
    Is there a way without using a combo box to display information from two tables on 1 form? I'm putting together a small application for a haircutter. On the entry form where the various services are selected, I'd like to display the price and description for each service. The price and description information is being stored in a separate table from the actual Orders table. There is a fixed number of services.

    A subform won't work because the cashier wants to see the price for each service next to the service - in other words, information from TWO tables has to be displayed next to each other. I'm using a textbox for the information from the tables. The Control Source defaults to the Form Control Source, I've tried entering a Select statement in the Control Source and Default Value places, but I just get the #name? message. I've also tried Dlookup but that doesn't work either.

    How can I do this? What's the correct syntax?

    Please help!! Thanks.
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Have you tried creating a query based on the orders table and the services table, joined on the service id field, and using that as record source?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Hans,
    Thanks for your reply. I tried your suggestion, but I didn't get the results I wanted. I think there is a design problem I need to resolve first. There are two tables, a sales and a service table.There will also be a Products table for the products sold at the salon. In the Sales table I want to have a new record for each sale at the haircutter, which may include a mixture of services and retail products.
    In the Services table there is information on the service, its description and price. My current design has a new record in the Services table for each service, i.e. haircut, haircut with special price, etc. I am planning on having a separate record in the Products table for each product, shampoo, creams, etc.
    I'd like to have one data entry screen with all the services and products so the cashier can enter a number (1 or more) for the services and products for a particular order and the program would calculate the serivces total, product total, service tax, product tax and total order.
    There are a known number of services and products which are not expected to change, but I want to give the user the ability to change the descriptions and prices, i.e. modify the information in the services and products table.
    So if I do the query as you suggested, I'll only get 1 record from the services table to display on the sales screen. I guess I can change the structure of the services table to include 3 fields for each service - short code, description and price, but that seems like bad design.
    How can I include information from all the records from the Services and Products table in one query so the order screen shows all available services and prodcuts?
    And I'd like the description and price information to appear on a blank record that I am adding to the Sales table.
    Sorry for the long description, but what seems easy to say and specify seems difficult to design and code.
    Mark

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple tables on 1 form (Access 2003)

    You need some extra tables. See the picture below. The field names in bold are the primary keys of the tables. The Price fields in tblProductSales and tblServiceSales are optional, but you'll probably want to include them. This way, the price for products and services belonging to a specific sale will not change if the prices in tblProducts and tblServices are changed. (Prices will probably be higher next year, but that shouldn't affect this year's sales.

    Your form would be based on tblSales, with two subforms, one based on tblProductSales and one based on tblServiceSales. The total price for a sale can be calculated from the subforms.

    See the database attached to Re: Many-to-many relationships (XP, 2000) for a simple example of a main form with a subform.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Multiple tables on 1 form (Access 2003)

    If you are going to include borh products and services in the one sale, I think it would be easier to merge the products and services table into a single table. (tblproductsandservices)
    You could include an extra field that designates whether a particular item is a product or service.

    This way you can use queries to see each list separately when you want.
    If you keep them as a separate tables, you will probably find yourself having to use union queries a lot, and these are somewhat cumbersome.
    Regards
    John



  6. #6
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Hans,
    Thank you. I will try this. I need to go over to be sure I've got it straight. (I had trouble accessing WOPR since yesterday, until I tried another computer now, which unfortunately doesn't have a printer so I can't print your diagram. I did copy it by hand.)
    I can't believe how much effort you put into this. Thanks again.
    Mark

  7. #7
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Hans,
    Thank you. I'm not sure I see yet how to get this to have multiple products or services for a single sale, but I'll try. I had trouble getting into WOPR on my regular computer since Sunday night. I'm now on a laptop with no printer but wireless access. It's amazing how much work you put into this. Thanks again.

    Mark

  8. #8
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    John,
    I'm definitely not looking to make my life any more complicated. Thanks for your post.

    Mark

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple tables on 1 form (Access 2003)

    It's not your computer - the Lounge was not accessible for 24 hours due to a technical problem. it should be OK now.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple tables on 1 form (Access 2003)

    The purpose of the intermediate tables tblProductSales and tblServiceSales is to allow multiple products and services per sale. For example, tblProductSales will contain a separate record for each product belonging to a particular sale. In this table

    <table border=1><td>SalesID</td><td>ProductID</td><td>Quantity</td><td>Price</td><td align=right>3</td><td align=right>12</td><td align=right>1</td><td align=right>39.99</td><td align=right>3</td><td align=right>27</td><td align=right>3</td><td align=right>19.99</td><td align=right>5</td><td align=right>9</td><td align=right>2</td><td align=right>14.99</td><td align=right>5</td><td align=right>27</td><td align=right>1</td><td align=right>19.99</td><td align=right>5</td><td align=right>34</td><td align=right>4</td><td align=right>24.99</td></table>
    Sale # 3 involves products 12 and 27, and sale #5 involves products 9, 27 and 34.

  11. #11
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Hans,
    OK. That's clearer. A picture truly is worth 1,000 words.
    Thank you.

    Mark

  12. #12
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    Hans,
    That was very scary. I didn't realize how much I had come to depend on WOPR. Do you have an e-mail I could use in cases of emergency?

    Mark

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple tables on 1 form (Access 2003)

    > Do you have an e-mail I could use in cases of emergency?

    I'm sorry, I prefer to keep my e-mail address private. Let's just hope that Lounge outages will be infrequent.

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

    Re: Multiple tables on 1 form (Access 2003)

    Please re-read <!rule=10>Rule 10<!/rule> and <!rule=17>Rule 17<!/rule>. <small>Sometimes people forget that Hans is a volunteer.<small> <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>
    Charlotte

  15. #15
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple tables on 1 form (Access 2003)

    To all,
    No offense intended. Nor was I making a demand on Hans. It was just very unusual and a little frigthening not to have access (that's a pun, son) to WOPR. I remember one other time it went down late Sunday - I thought it had to do with backup. That's why I was especially surprised when the outage continued well into the next day.
    Are there any other Access or MS boards that people use for support and conversation?
    Hans, I fully recognize and have acknowledged the GREAT work you do. Please excuse any untoward request.
    Mark

Page 1 of 2 12 LastLast

Posting Permissions

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