Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    48

    Unanswered: Multiple Query question

    I want to know if there is a more direct way to do this?

    I have a form that includes multiple subforms. Some subforms may be empty for the record in the main form. I am trying to put everything on this main from into a query, where everything is displayed in a query.

    All the subforms are M:M from the Main table.

    1:What I have done is query the Main table by CustIdD to the Details Table by a Left Join.
    2:Then I took that query and queried it against the orders table. by order number, using a right join.
    3:I see all the Customers even though they may not have an order, which should be correct. That is 1 subform

    For the other subforms, I repeat steps 1-2.
    Finally, I query the all last queries or step 2 queries with one another. Near the end something looks right but it doesn't. Is this the correct way of doing this type of query?

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Subforms are usually based on a one-many relationship. if i'm not mistaken subforms can only be three levels deep. need more info on the tables.
    w

  3. #3
    Join Date
    Sep 2004
    Posts
    48

    Question

    I believe I explained it wrong. The Main form has 4 seperate subforms.
    Main Table
    CustID
    etc.,

    ProductDetails
    CustID
    ProductID

    Product
    ProductID
    ProductName

    The Main table has 4 subforms similiar to this table structure. What I want to do is query all these subforms and have them displayed i a query.

    What I am evenutally going to do is put the Query in the Report. What happens is that if a specific customer does not have a Product then that specific customers information is not displayed.
    Last edited by Ronbegone; 10-06-04 at 15:21.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Holy hell you're trying to make this hard...



    Only join the tables ONCE AND ONCE ONLY. Just because you have four subforms on the form doesn't mean you need four seperate queries. Subforms are basically just a join statement, that's it. If you give the FULL rundown of your table structure, we can help you with that.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2004
    Posts
    48

    it works, thanks

    Thanks Teddy,

    I was able to put everything on 1 query. I did a right join and left and it looks like I am getting the data that I was looking for. I had like 8 queries to get 1 output.

Posting Permissions

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