By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

How can I create one report from 2 sorted queries?

P: 3
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.
Feb 14 '10 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Feb 14 '10 #2

P: 3
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 (chronologically)
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 (chronologically)
topics Comments (alphabetically)
topics Comments (chronologically)
topics Comments (alphabetically)
topics Comments (chronologically)
topics Comments (alphabetically)
Feb 15 '10 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Feb 15 '10 #4

P: 3
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!
Feb 16 '10 #5

nico5038
Expert 2.5K+
P: 3,072
Glad I could help.
Nice to know that someone learned the value of the grouping / sorting thanks to my cryptic directions :-)

Nic;o)
Feb 16 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.