Sorting in Reports

Hello kristenbednarz,

I would include a table in the db that defines the sort order:

tblChronological
----------------------------------
Phase            (ie Beginning, Middle, etc)
SortOrder        (ie 1,2,3, etc)

Now, in the query underlying your report, join to that table, and use the SortOrder column instead of the phase
description in the ORDER BY clause.

If you decide not to make that table, you could always use this in the ORDER BY clause:

ORDER BY Switch([Phase] = "Beginning", 1, [Phase] = "Middle", 2, [Phase] = "End", 3, [Phase] = "Other", 4, True, 5)

Regards,

Patrick

You need to perform the sort using a real column to order, such as a date, number, or text field. I do not think you can custom sort like that.
You could trick it by changing the text to 1Beginning, 2Middle, 3End, 4Other, 5 . ;-)
You could also add an invisible field to your data that will sort but not display.
Lee

I think I understand what you are saying, but here is where it gets complicated - The text originally comes from an options group on a form.  So I had to put a formula in my query to change the options  group from numbers to text.  Originally the field displayed a value 1 through 9 which I then had to convert into text so that the names would show up on the report and not the numbers.  Now I have no idea how to keep the text and try to sort.

On a side note this is a form that will continually have new entries.  So although a short term solution like adding an invisible field might make sense for the data I already have, I am not sure that this will ensure that the Report will continue to update properly

Is there a way to do this and still use the options group I implemented since that is where all the data came from?  

There is one column in my query that contains the actual numeric values of the options and there is another one with a formula that created the text names.  Since this column was created in the query I know I can't link it to a table since it does not originate from a table.  

The example you attached is really helpful and is what I need to do, just not sure how to get there.

kristenbednarz,

The best part about the table-driven approach, and then changing out the option group for the combobox, is
that you could add 20 new items tomorrow and not have to touch the SQL for the query or the report design--
you'd just update the values in the SortOrder table :)

Patrick

I agree with you.  Unfortunately, the people I am making it for like the look of the option group on the form.  Can I use a combox type approach and still have it layed out like the options?  Or is there anyway to make this work with the options?

Hello kristenbednarz,

» There is one column in my query that contains the actual numeric values of the options

Is that numeric value usable for sorting? I meant does an ascending sort on that column provide the correct order? If so, just use that field in the report's “sorting and grouping” panel, and display your calculated field.

By the way, how do you calculate the text version of your field? This would help us understand if we can work with the base field directly.

If you don't calculate it in the query, one simple solution is to create a combo box in the report. The arrow will not print, so it will be used only to translate a number into readable text:

    row source type: value list
    row source: 1;Beginning;2;End;3;Middle;4;Other
    column count: 2
    column widths: 0

A report combo box is a great tool to “decode” information.

(°v°)

Here is calculation: DealProgress: Switch([Deal Progress]="1","Commited",[Deal Progress]="2","Under Application",[Deal Progress]="3","Engaged and Marketing",[Deal Progress]="4","Engaged and Packaging",[Deal Progress]="5","Reviewing and do not Control Business",[Deal Progress]="6","Closed Business",[Deal Progress]="7","Dead",[Deal Progress]="8","Pending")

if I sorted ascending based on the original #'s it would not turn out correctly, but I guess that is easy to change on the form.  This is how I would want it to sort on a Report:

Pending
Commited
Under Application
Engaged and Marketing
Engaged and Packaging
Reviewing and do not Control Business
Closed Business
Dead

With a little rearranging on my end this completelty helped with what I needed.  Thank you so much for the help!!!

kristenbednarz,

Glad to help, and as always it's a pleasure collaborating with harfang.

Patrick

Thanks! — (^v°)

I signed up as a trial member of experts-exchange yesterday.  I had two very time sensitive questions that I needed help on and received thorough answers on both of them in a very timely manner. I was skeptical before signing up becasue you never know how/if these things will work but I am definitely going to join as a member now.  Thanks again I wish I would have known about this site sooner!!

kristenbednarz,

Welcome to EE!  I had not even noticed that you were a trial member.  Just keep those questions coming :)

Patrick