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. I’m an SQL novice and can’t 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.
-
-
Sorting in reports is done using the grouping button.
In your case all needed is the second query with all the information.
In the grouping you place the fields ref#s and topics and for the topics you add a header by setting the option to "Yes"
In the additional section of the report you place the ref#s and topics fields and in the detail section the other fields.
That's all, Access will take care of the sorting.
Nic;o) -
My report is more complicated than that. Here's how far I got. Using 2 append queries, I finally have an appended table consisting of topics and comments. All the topics with chronological comments are sorted and listed before the remainder of the topics with alphabetical comments like this:
Table:
topics Comments (chronologicall y)
topics Comments (alphabetically )
When I create the report based on this table, I grouped the topics sorting ascending and create a header for the topics. The report does list the topics alphabetically, but now all of the comments are unsorted.
Report:
topics Comments (unsorted)
topics Comments (unsorted)
topics Comments (unsorted)
topics Comments (unsorted)
topics Comments (unsorted)
topics Comments (unsorted)
How do I create a report with topics sorted alphabetically, where the comments are sorted alphabetically for certain topics and chronological for the other topics.
What I want is the following:
Report:
topics Comments (chronologicall y)
topics Comments (alphabetically )
topics Comments (chronologicall y)
topics Comments (alphabetically )
topics Comments (chronologicall y)
topics Comments (alphabetically )Comment
-
Can you create a small sample .mdb with the report and a (partially) filled table, then zip it and attach that to your comment so I can have a look ?
Nic;o)Comment
-
You were right! I now see how powerful a simple thing like grouping/sorting can be. 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 help!Comment
-
Glad I could help.
Nice to know that someone learned the value of the grouping / sorting thanks to my cryptic directions :-)
Nic;o)Comment
Comment