Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Red face Unanswered: How to create report from 2 sorted queries

    I have an Access database containing 2 tables. Tbl1 consist of ref#s and topics. Tbl2 consist of ref#s, topics, comments and dates. From this I created 2 queries. Query #1 has records from the table (topics/dates) sorted by date. Query #2 has the rest of the records (topics/comments) sorted alphabetically. Im an SQL novice and cant get my report to combine both queries, with the appropriate sorting. Each query looks how I want it, but when I combine them into one master query, it gets unsorted. I need one report sorted by topics where certain topics are sorted alphabetically and other topics by date.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might make those queries into append queries and append them to a temp type table. Then open the temp table. Note: you'd want to 1st delete the values in the temp table, then run the 1st query to populate the values, then the 2nd (each query sorted how you want them to). Then when you open your temp table, you can leave the sorting based on how the records were appended or you can make a query from the Temp table which re-sorts them by 1 or more fields.

    If you're working off of a report, consider a sub-report which also works. Look at the Sorting/Grouping property box in report designer for sorting.
    Last edited by pkstormy; 02-13-10 at 22:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2010
    Posts
    3
    I hope I'm in the home stretch. As you suggested, I created the append queries and appended them to a temp table. I made a final query from the temp table and everything is sorted as I want.... but... when I bring the query into the report, I want to create a group header for the topics and when I preview the report, the desired sort goes awry. The comments become sorted alphabetically (when they were chronological in the final query) assuming because I was forced to sort in the creation of the group header? Argh! Is there another way to create the topics header without being forced to sort?
    Last edited by kulamelody; 02-14-10 at 19:33. Reason: Omitting comment

  4. #4
    Join Date
    Feb 2010
    Posts
    3

    Smile

    Okay, I figured it out (duhhh). I sorted the report first by topic, then by the idnumber, ascending (since all records were sorted in the temp table as was desired). Sometimes the simple answers take the longest to figure out.

    Thank you for your advice. You truly are an expert! Thanks again for your reply!

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Cheers. Thanks for the reply back. The sorting/grouping popup is sort of hidden in the report designer. It's a powerful popup though which allows you to group on certain fields as well as sorting. Look at things like (keep together, etc.. and other things in the sorting/grouping popup). You can also right-click on the section to setup things like "force new page after section/before section/etc...")
    Last edited by pkstormy; 02-17-10 at 00:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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