Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine query results for report (A2002)

    Here' s yet another query question for you...

    I need to create a report that pulls the results of two queries together. The queries involved are a fixed bag: 1 is a summary query and 1 is not. The first returns between 5 and 12 records, the second only 1. They share 1 parameter [enter program].

    Given the report structure requested, I was thinking a merge with Word would simplify the report layout process considerably, but I

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

    Re: Combine query results for report (A2002)

    Create a third query that will draw both queries together. Since there is only 1 record returned from one of the queries you don't have to worry about a "cartesian Product".
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine query results for report (A2002)

    OK, that works for these two queries. So far so good.

    Now step two: There are 7 more queries I need to include in this report. I had thought I could take whatever strategy I used for the first two queries and extend it to these 7. Unfortunately, 6 of these are summary queries each returning 5-15 records. So, it gets cartesian in a hurry.

    Have you had to deal with anything like this?

    Elizabeth

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

    Re: Combine query results for report (A2002)

    It depends on how do you want to combine the results of the queries in the report.

    If you want to display the results of the first query, then below that the results of the second query, and below that those of the third query, etc., create a report for each of the queries, and include all these as subreports in a main report without record source. Since the subreports are independent of the main report and of each other, you don't need to set master/child fields.

    If the results of the queries are to be related to each other, you need one or more fields on which you can link them in a "master" query like the one you already created for the first two. If you have no way to join them, you'll end up with a large cartesian product.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine query results for report (A2002)

    By jove, I think you've got it! Listing the reports one after the other will work just fine. I'll follow-up if I run into any road blocks.
    Thank you Hans, you're a lifesaver. None of the 6 Access books I consulted where of much use on this. Do you have a favorite resource that could get me a little more up to speed on "higher end" queries? I've not found anything that really fits the bill yet.

    e

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

    Re: Combine query results for report (A2002)

    Elizabeth,

    The answer is obvious: this forum in Woody's Lounge is your one-stop shop for all your Access questions! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    But seriously, you can learn a lot from browsing around here, and searching for posts related to questions you have. I have learned a lot this way (and still learn something new every day).

    Other good resources are the Microsoft Knowledge Base - it has lots of examples. I usually use http://www.freeanswers.com to search the Knowledge Base - the interface is easier, and it's fast, although I must say that searching the MSKB directly seems to have become more efficient recently.

    Microsoft has a nice sample database - it's for Access 97, but most or all of it applies to Access 2002: ACC97: Microsoft Access 97 Sample Queries Available in Download Center.

    Another useful site is Dev Ashish's Access Web in the MVPS site. The part dealing with queries is http://www.mvps.org/access/queries/index.html.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine query results for report (A2002)

    Yeah, I know I can get all my questions answered here <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>. But, I always feel a little guilty for taking up your time with something I think I ought to be able to figure out myself. I feel like one of those pesky little puppies always nipping at your heels...gets annoying after awhile. You'll notice, however, that I've not let it stop me from taking advantage.

    Thanks for the sources,

    Elizabeth

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

    Re: Combine query results for report (A2002)

    Not to worry - we were all there once. I don't know what books you are using, but if you don't have the Access Developer's Handbook you should seriously consider adding it to your library. Another book I've gotten to like alot, and is more tutorial in nature, is Alison Balter's Mastering Access 2002 Desktop Development .
    Wendell

Posting Permissions

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