Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sorting in a Report

Posted on 2014-02-13
12
Medium Priority
?
251 Views
Last Modified: 2014-02-13
Experts,

I have a report with a  text Grouping
I need to sort based on my own criteria.  
I can not use the built in sorting.

How can I do this?
0
Comment
Question by:pdvsa
  • 6
  • 6
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39856602
Depends on your criteria for sorting :-)


A common approach is to add a "Dummy Column" to your query, with data formatted the way you need it sorted, and then sort on the Dummy Column rather than the raw data.
0
 

Author Comment

by:pdvsa
ID: 39856657
ok lets say if I have the following:

Won
Lost
Dead
Bid Submitted
Proposal
Possible

Proposal needs to be at the top
Possible second
Won Lost Dead at the bottom and in that order.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 39856721
Okay - that is a little more complex.

One way to handle that is to have a lookup table for those items... make them selectable from a combo box on your forms.

The table would contain a text field for the selections, and a numeric field (NOT an autonumber) for the sort-order which you define by numbering those items in a lookup table.

Do you currently have a lookup table for this or is the user entering it as free text?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pdvsa
ID: 39856749
mbizup:  there is no lookup table (its free text).  I am linking to an excel report from a dl from another db and I am making a report based on that data.  

What do you think now?  (might not respond til later in day)
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 2000 total points
ID: 39856778
I still would use a table to define the sort order.  Same structure as I described earlier,


If your query looks like this:

SELECT Something, ProposalStatus,SomethingElse
FROM YourTable

Open in new window



Change it to this:

SELECT t1.Something, t1.ProposalStatus, t2.ProposalSortOrder, t1.SomethingElse
FROM YourTable t1 INNER JOIN tblLookupProposalStatus t2 ON t1.ProposalStatus = t2.ProposalStatus
ORDER BY t2.ProposalSortOrder

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 39856801
BTW, You CAN do this with an expression such as an IIF , CHoose, or switch statement or some other variation of that in code in your query -- but those expressions tend to get convoluted.

My own preferred approach is the table method I suggested earlier, which is cleaner and easier to maintain.

That said, The code for a sort order column might look something like this in the query designer (you can extend it as needed)


SortOrder: Switch([ProposalStatus]="Won",1,[ProposalStatus]="Lost",2,[ProposalStatus]="Dead",3 .... etc)

Open in new window

0
 

Author Comment

by:pdvsa
ID: 39856950
Ahhh... I think I remeber that IIF now.  Will try it later today.  Not at computer now.  Thank you.
0
 

Author Comment

by:pdvsa
ID: 39857094
changed my mind....doing the lookup table idea

does the t2.ProposalSortOrder (the numeric field) need to be a PK?

If there is a new record in the excel data with a [proposalstatus] not in the lookup table then wouldnt there be an issue if I set ON t1.ProposalStatus = t2.ProposalStatus.  That record in question would not be shown?  

thank you
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39857160
<<
does the t2.ProposalSortOrder (the numeric field) need to be a PK?
>>

It can be, but doesn't need to be.  It just should *not* be an autonumber.


<<
If there is a new record in the excel data with a [proposalstatus] not in the lookup table then wouldnt there be an issue if I set ON t1.ProposalStatus = t2.ProposalStatus.  That record in question would not be shown?  
>>

With either method, you would have to manually adjust for new proposal statuses (no way around that).  With the table method, you can make it a little easier with a query to detect new statuses and insert them into the table... but you would still have to manually assign a sort order.

This is why I like the table method better.  With the code, you would have to add another criteria to the switch statement -- which can get pretty ugly.

With the table, just add the new status and its numerical order to the table, renumbering the other statuses if needed.
0
 

Author Comment

by:pdvsa
ID: 39857205
Ok I now see advantages of the table method.  I have made the table and the sort works perfectly as planned.  

<With the table method, you can make it a little easier with a query to detect new statuses and insert them into the table
==>How would an expert do this?   Run the query before the report?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39857400
Yea.  Run the query against the spreadsheet data, and update the lookup table so that the sort order is ready for the report.
0
 

Author Closing Comment

by:pdvsa
ID: 39857475
Thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question