Solved

Sorting in a Report

Posted on 2014-02-13
12
236 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 500 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 500 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record with #Error 8 33
Pivot a table with 3 coulmns with two layers of header columns 3 35
Access join syntax when converting to T-SQL query 4 34
Problem with vba code 4 42
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

828 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