Report Sorting (XP)

Topic

New Reply

I am new to Access (but not new to relational databases).

I am trying to build a report based on a query, however, the query is sorted on columns that are not being returned by the query. The query sorting works fine, but when I try to create a report from that query the sort order appears to be overridden by the report in some manner. Is there anyway to retain the sort order returned by the query?

Reply | Quote

Replies

In the design view of the report, select the menu View | Sort and Grouping.
Enter there your sorting field(s) or remove each line to have the query sorting.

Reply | Quote

This one gets all Access newbies (myself included). Access reports totally ignore any sorting you have in the underlying query; so don’t even bother to put anything there. They strictly use the Sorting And Grouping option within the report, selectable when in design mode for the report by a button on the toolbar, or by selecting “View” from the menu bar.

Reply | Quote

I think that my twist here is that I am using Grouping, but I want to override the natural sort order of the Group columns, that’s why I am actually sorting on another column that is NOT being displayed by the Query, and in turn by the report. The sort order has been customized by a separate field. Therefore, this field is not being seen by the Report and is not available as a sort column.

Do I need to return this column from the query and then hide it on the Report? If so, how? Or is there another approach?

Reply | Quote

You can’t “override” the sort order done by the grouping. Grouping does sorting. What you need to do is make sure your query shows that field that you want to sort on, then make this field the first field in your sorting/grouping list. It won’t show on the report.

Reply | Quote

All right, then let me restate the problem with an example. Suppose you have a database of issues and want to create a report where the issues are group by priority in the order of High, Medium, then Low. An alphabetic sort wouldn’t work (“Low” would sort before “Medium”).

How do you do that?

Of course you could change to numeric priorities, or add a “1”. “2” and “3′ prefix to the priorities, but there are other reasons why that is not optimal.

Any ideas?

Reply | Quote

I’d add an expression to my query like this:
PriorityNo: =Switch(“High”,1,”Medium”,2,”Low”,3)

Then sort it ascending. Then in your report, PriorityNo would be the first field in your sorting/grouping list.

Reply | Quote

Right, I already have a solution for getting the correct sort order (I used a join with a table that contains the sort orders I want), but I don’t want the sort order number to appear on the report, I want the word to appear without the associated number.

Is that possible?

Two additional notes:

First, I don’t think the syntax you provided is quite correct. I had to make modifications to get the query to run at all, so it’s possible that I am missing the point.

Second, where can I find a list of these functions? I can’t find them anywhere in the Access Help files.

Reply | Quote

Add the sort number to the sort window.
If you don’t want a header, add the “word” field to the detail section.
If you want a header, create it by setting yes to group header in the sort window.
In the header you can put the field with the “word”.
What is displayed in the header do not affect the sorting.

Reply | Quote

Oops. slight error in use of switch. Let’s assume you already have a Priority field (with values, “High”, etc.). You want that field to show in the query. Additionally, you will create another field which contains this expression:

PriorityNo: Switch(Priority=”High”,1,Priority=”Medium”,2,Priority=”Low”,3)

In your report’s sorting/grouping, you want to specify “PriorityNo” as your first entry. You can then put a textbox anywhere (in header or detail) that has “Priority” as its controlsource.

Reply | Quote

JFord,
You can’t find ANY real help in the Access Help files. This made me nuts a few years ago when I upgraded since I was used to everything being in one place. What I’ve discovered is that you actually need to be in the code area to get any real help. I always keep a blank, dummy form open just so I can open it in Design view, and click on the “Code” button. From there you can access the VB Help file. I like the “dummy” form because it doubles as a place to test code that you’re just learning.

Reply | Quote