Sorting in Reports

kristenbednarz
kristenbednarz used Ask the Experts™
on
I want to sort a report based on criteria in a Query  

If the Text in the sorting column of the Query is either Beginning, Middle, End, Other or a blank space.  How can I sort the Report in Ascending chronological order: Beginning, Middle, End, Other, blank space as opposed to in Ascending in alphabetical order: blank space, Beginning, End, Middle, Other which is how it is now?

Does this make sense?  I am lost!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
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

Commented:
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

Author

Commented:
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
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Top Expert 2010
Commented:
kristenbednarz,

I would suggest using the table approach I described above, which would also allow you to ditch the option
group and move to a combobox.

Please see the attached file, in particular the query qryUsingSortOrderTable and report rptUsingSortOrderTable.

Patrick
Q-26285482.mdb

Author

Commented:
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.
Top Expert 2010

Commented:
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

Author

Commented:
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°)

Author

Commented:
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
Yes, it would be meaningful to change it on the form. But please also remember to recode any existing data, which can be tricky (do it in a second field, and only delete the old field when you are absolutely certain that the conversion succeeded).

matthewspatrick's idea to use an actual table to translate from the numbers to the text should not be dismissed. The option group can be replaced by a list box (or a combo box if screen real estate is scarce); the same combo box (based on the lookup table instead of a value list) would work directly on the report.

Incidentally, your Switch expression can be rewritten using Choose, which is perfect for small numeric codes starting with 1:

DealProgress: Choose([Deal Progress], "Commited", "Under Application", "Engaged and Marketing", "Engaged and Packaging", "Reviewing and do not Control Business", "Closed Business", "Dead", "Pending")

Change the order if you change the option group and the existing data, naturally.

(°v°)

Author

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

Commented:
kristenbednarz,

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

Patrick
Thanks! — (^v°)

Author

Commented:
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!!
Top Expert 2010

Commented:
kristenbednarz,

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

Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial