Three Quick Sorting Tips For Access Query Custom Sort In Microsoft Access

There may be times when you want to sort data, not by alphabetical or numerical order, but by user-defined order (also called custom or logical order). For example, sorting weekdays so that Monday appears first makes more sense than sorting in alphabetical order, where Friday appears first. You can sort data in the Datasheet view of a table or query, or in the Form view of a form.

Đang xem: Access query custom sort

What do you want to do?

Sort a small, finite number of unique field values

In this procedure, you will write an expression that uses the IIf function to rank the titles of employees.

First, create a table that contains three fields — Title, FirstName, and LastName. In the Title field, enter the following values for four new records:

Inside Sales Coordinator

Sales Manager

Sales Representative

Vice President, Sales

In the FirstName and LastName fields of the four records, enter any names you want.

Now, open the table in Datasheet view.

On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.

Add the fields you want, such as FirstName, LastName, and Title, to the grid.

Right-click the Field row in the first blank column, click Zoom, and then type the following expression:

Read More:  one piece unlimited world red trophy guide

IIf ( = “Vice President, Sales”, 1, </b> <b>IIf</b> <b>(<Title> = “Sales Manager”, 2, </b> <b>IIf</b> <b>(<Title> = “Sales Representative”, 3, </b> <b>IIf</b> <b>(<Title> = “Inside Sales Coordinator”, 4, 5))))</b> </p> <p>Title is the name of the field that contains the values that are being ranked. The values enclosed in quotation marks are the individual values stored in the field. The numeric value that follows is the rank assigned to it. The preceding expression assigns ranks 1 through 4 to four different titles, and assigns rank 5 to all others. When you perform the sort, the titles not mentioned in this expression are assigned the same rank (5).</p> <p>In the <b>Sort</b> row for the column containing the expression, click <b>Ascending</b>.</p> <p>On the <b>Home</b> tab, in the <b>Sort & Filter</b> group, click <b>Toggle Filter</b>.</p> <p>The <b>IIf</b> function returns the numeric value that corresponds to the value in the Title field. For example, if the value in the Title field is <b>Sales Manager</b>, the number assigned is <b>2</b>. If there is a value in the Title field not listed as an argument in the function — for example, <b>District Coordinator</b> — it is assigned the number <b>5</b>. The query then sorts these numbers in ascending order.</p> <p> Top of Page </p> <h2><span class="ez-toc-section" id="Sort_a_large_number_of_unique_field_values"></span>Sort a large number of unique field values <span class="ez-toc-section-end"></span></h2> <p>If you have a large number of values to rank, a better way to sort data is by creating a lookup table.</p> <p>Assume that the Employees table refers to more than 50 different titles. You will first need to create a lookup table and prepare the existing table to look up the new table. You then relate the two tables and create a query.</p> <div style="clear:both; margin-top:0em; margin-bottom:1em;"><a href="https://freewebsfarms.com/microsoft-project-vba-examples/" target="_blank" class="ue73cf8120ac785f1b6b2e7fcbced6351"><!-- INLINE RELATED POSTS 2/3 //--><style> .ue73cf8120ac785f1b6b2e7fcbced6351 { padding:0px; margin: 0; padding-top:1em!important; padding-bottom:1em!important; width:100%; display: block; font-weight:bold; background-color:inherit; border:0!important; border-left:4px solid inherit!important; box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -moz-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -o-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -webkit-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); text-decoration:none; } .ue73cf8120ac785f1b6b2e7fcbced6351:active, .ue73cf8120ac785f1b6b2e7fcbced6351:hover { opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; text-decoration:none; } .ue73cf8120ac785f1b6b2e7fcbced6351 { transition: background-color 250ms; webkit-transition: background-color 250ms; opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; } .ue73cf8120ac785f1b6b2e7fcbced6351 .ctaText { font-weight:bold; color:inherit; text-decoration:none; font-size: 16px; } .ue73cf8120ac785f1b6b2e7fcbced6351 .postTitle { color:inherit; text-decoration: underline!important; font-size: 16px; } .ue73cf8120ac785f1b6b2e7fcbced6351:hover .postTitle { text-decoration: underline!important; } </style><div style="padding-left:1em; padding-right:1em;"><span class="ctaText">Read More:</span>  <span class="postTitle">Microsoft Project Vba Examples, Automation Interface:</span></div></a></div><h3><span class="ez-toc-section" id="Create_a_lookup_table_and_modify_the_existing_table"></span>Create a lookup table and modify the existing table<span class="ez-toc-section-end"></span></h3> <p>Create a new lookup table that will store the titles. Name the table <b>tblTitles</b>, and set the fields to the data types indicated in parentheses, as shown in the following table:</p> <table> <tr> <p> <b>TitleID</b> <b> (AutoNumber)</b> </p> <p> <b>Title</b> <b> (Text)</b> </p> </tr> <tr> <td> <p>1</p> </td> <td> <p>Vice President, Sales</p> </td> </tr> <tr> <td> <p>2</p> </td> <td> <p>Sales Manager</p> </td> </tr> <tr> <td> <p>3</p> </td> <td> <p>Sales Representative</p> </td> </tr> <tr> <td> <p>4</p> </td> <td> <p>Inside Sales Coordinator</p> </td> </tr> </table> <p>Set the TitleID field as the primary key.</p> <p>Modify the Employee table so that it looks up the tblTitles table.</p> <p>In Design view, change the name of the <b>Title</b> field to <b>TitleID</b>.</p> <p>In the <b>Data Type</b> column, change the <b>Data Type</b> to <b>Number</b>.</p> <p>On the <b>General</b> tab, under <b>Field Properties</b>, change <b>Field Size</b> to <b>Long Integer</b>.</p> <p>Save the table and ignore the warning about the loss of data.</p> <p>Xem thêm: <a href="https://freewebsfarms.com/automatic-logon-windows-xp/" title="How To Turn On Automatic Logon Windows Xp To Automatically Login">How To Turn On Automatic Logon Windows Xp To Automatically Login</a></p> <p>Switch to Datasheet view.</p> <p>Populate the Titles column with the TitleID value that corresponds to the title value.</p> <p>For example, for records where the Title value is Sales Manager, type <b>2</b>. For Sales Representative, type <b>3</b>.</p> <p>Save and close the table.</p> <h3><span class="ez-toc-section" id="Relate_the_two_tables"></span>Relate the two tables<span class="ez-toc-section-end"></span></h3> <p>On the <b>Database Tools</b> tab, in the <b>Relationships</b> group, click <b>Relationships</b>.</p> <p>On the <b>Design</b> tab, in the <b>Relationships</b> group, click <b>Show Table</b>.</p> <p>Select <b>tblTitles</b> and <b>Employees</b>, and click <b>Add</b> to add them to the Relationships window. Close the <b>Show Table</b> dialog box.</p> <p>Drag the TitleID field from the tblTitles table to the TitleID field in the Employees table.</p> <p>The <b>Edit Relationships</b> dialog box opens.</p> <p>Ensure that the field names displayed in the two columns are both <b>TitleID</b>, and also that the <b>Relationship Type</b> box displays <b>One-To-Many</b>. You can change the field names, if necessary.</p> <div style="clear:both; margin-top:0em; margin-bottom:1em;"><a href="https://freewebsfarms.com/how-to-separate-the-layers-of-a-dvd/" target="_blank" class="u944a7d99b4af5c822ed9ffab0685c2cc"><!-- INLINE RELATED POSTS 3/3 //--><style> .u944a7d99b4af5c822ed9ffab0685c2cc { padding:0px; margin: 0; padding-top:1em!important; padding-bottom:1em!important; width:100%; display: block; font-weight:bold; background-color:inherit; border:0!important; border-left:4px solid inherit!important; box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -moz-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -o-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); -webkit-box-shadow: 0 1px 2px rgba(0, 0, 0, 0.17); text-decoration:none; } .u944a7d99b4af5c822ed9ffab0685c2cc:active, .u944a7d99b4af5c822ed9ffab0685c2cc:hover { opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; text-decoration:none; } .u944a7d99b4af5c822ed9ffab0685c2cc { transition: background-color 250ms; webkit-transition: background-color 250ms; opacity: 1; transition: opacity 250ms; webkit-transition: opacity 250ms; } .u944a7d99b4af5c822ed9ffab0685c2cc .ctaText { font-weight:bold; color:inherit; text-decoration:none; font-size: 16px; } .u944a7d99b4af5c822ed9ffab0685c2cc .postTitle { color:inherit; text-decoration: underline!important; font-size: 16px; } .u944a7d99b4af5c822ed9ffab0685c2cc:hover .postTitle { text-decoration: underline!important; } </style><div style="padding-left:1em; padding-right:1em;"><span class="ctaText">Read More:</span>  <span class="postTitle">How To Separate The Layers Of A Dvd Mosaic!!, Tips And Ideas For Crafting With Recycled Dvds</span></div></a></div><p>Select the <b>Enforce Referential Integrity</b> check box.</p> <p>Click <b>Create</b> to create the relationship.</p> <p>The <b>Edit Relationships</b> dialog box closes, and saves the changes.</p> <p>Close the <b>Relationships</b> tab.</p> <h3><span class="ez-toc-section" id="Create_a_query"></span>Create a query<span class="ez-toc-section-end"></span></h3> <p>On the <b>Create</b> tab, in the <b>Queries</b> group, click <b>Query Design</b>.</p> <p>In the <b>Show Table</b> dialog box, click the <b>Tables</b> tab, double-click <b>Employees</b>, and then double-click <b>tblTitles</b>.</p> <p>Close the <b>Show Table</b> dialog box.</p> <p>Xem thêm: <a href="https://freewebsfarms.com/how-often-does-the-reliability-monitor-gather-data-for-the-stability-index/" title="how often does the reliability monitor gather data for the stability index?">how often does the reliability monitor gather data for the stability index?</a></p> <p>Add the EmployeeID, LastName, and FirstName fields from the Employees table and the Title field from the tblTitles table to the query design grid.</p> </div><!-- .entry-content --> </div> </div> </div> </article><!-- #post-## --> <div class="news-col-3 related-posts"> <h3 class="related-posts-title">Related Posts</h3> <div class="inner-wrapper"> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/esxi-active-directory-authentication/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/esxi-active-directory-authentication/">Esxi Active Directory Authentication, Configure A Host To Use Active Directory</a></h3> <span class="posted-date">April 9, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/information-technology-on-the-job-training/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/information-technology-on-the-job-training/">Information Technology On The Job Training, Information Technology Course Washington Dc</a></h3> <span class="posted-date">April 9, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/google-phone-vs-android/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/google-phone-vs-android/">Google Phone Vs Android – Whats The Difference Between Android And Google</a></h3> <span class="posted-date">April 9, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/allow-write-system-settings/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/allow-write-system-settings/">How To Secure Your Android App Permissions: Allow Write System Settings</a></h3> <span class="posted-date">April 8, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/25-best-cities-for-jobs/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/25-best-cities-for-jobs/">25 best cities for jobs</a></h3> <span class="posted-date">April 8, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> <div class="news-item three-column-item"> <div class="news-thumb"> <a href="https://freewebsfarms.com/download-adobe-flash-player-for-mac/"></a> </div><!-- .news-thumb --> <div class="news-text-wrap"> <h3><a href="https://freewebsfarms.com/download-adobe-flash-player-for-mac/">Download Adobe Flash Player For Mac 32, How To Install Adobe Flash Player On Mac</a></h3> <span class="posted-date">April 8, 2021</span> </div><!-- .news-text-wrap --> </div><!-- .news-item --> </div> </div> <nav class="navigation post-navigation" role="navigation" aria-label="Posts"> <h2 class="screen-reader-text">Post navigation</h2> <div class="nav-links"><div class="nav-previous"><a href="https://freewebsfarms.com/cell-phones-making-us-less-social/" rel="prev">Cell Phones Making Us Less Social Networking Is Making Us Less Social</a></div><div class="nav-next"><a href="https://freewebsfarms.com/how-to-prevent-software-piracy/" rel="next">How To Prevent Software Piracy, 6 Tips For Preventing Software Piracy</a></div></div> </nav> <div id="comments" class="comments-area"> <div id="respond" class="comment-respond"> <h3 id="reply-title" class="comment-reply-title">Leave a Reply <small><a rel="nofollow" id="cancel-comment-reply-link" href="/access-query-custom-sort/#respond" style="display:none;">Cancel reply</a></small></h3><form action="https://freewebsfarms.com/wp-comments-post.php" method="post" id="commentform" class="comment-form" novalidate><p class="comment-notes"><span id="email-notes">Your email address will not be published.</span> Required fields are marked <span class="required">*</span></p><p class="comment-form-comment"><label for="comment">Comment</label> <textarea id="comment" name="comment" cols="45" rows="8" maxlength="65525" required="required"></textarea></p><p class="comment-form-author"><label for="author">Name <span class="required">*</span></label> <input id="author" name="author" type="text" value="" size="30" maxlength="245" required='required' /></p> <p class="comment-form-email"><label for="email">Email <span class="required">*</span></label> <input id="email" name="email" type="email" value="" size="30" maxlength="100" aria-describedby="email-notes" required='required' /></p> <p class="comment-form-url"><label for="url">Website</label> <input id="url" name="url" type="url" value="" size="30" maxlength="200" /></p> <p class="comment-form-cookies-consent"><input id="wp-comment-cookies-consent" name="wp-comment-cookies-consent" type="checkbox" value="yes" /> <label for="wp-comment-cookies-consent">Save my name, email, and website in this browser for the next time I comment.</label></p> <p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Post Comment" /> <input type='hidden' name='comment_post_ID' value='2416' id='comment_post_ID' /> <input type='hidden' name='comment_parent' id='comment_parent' value='0' /> </p></form> </div><!-- #respond --> </div><!-- #comments --> </main><!-- #main --> </div><!-- #primary --> <div id="sidebar-primary" class="widget-area sidebar" role="complementary"> <aside id="search-2" class="widget widget_search"> <form role="search" method="get" class="search-form" action="https://freewebsfarms.com/"> <span class="screen-reader-text">Search for:</span> <input type="search" class="search-field" placeholder="Search …" value="" name="s" /> <button type="submit" class="search-submit"><span class="screen-reader-text">Search</span><i class="fa fa-search" aria-hidden="true"></i></button> </form></aside> <aside id="recent-posts-2" class="widget widget_recent_entries"> <div class="section-title"><h2 class="widget-title">Recent Posts</h2></div> <ul> <li> <a href="https://freewebsfarms.com/how-to-create-rainbow-text-on-instagram-story/">How to create rainbow text on Instagram Story</a> </li> <li> <a href="https://freewebsfarms.com/how-to-lock-folders-with-secure-folder/">How to lock folders with Secure Folder</a> </li> <li> <a href="https://freewebsfarms.com/install-and-use-kaspersky-anti-virus-effectively-to-remove-viruses/">Install and use Kaspersky Anti-Virus effectively to remove Viruses</a> </li> <li> <a href="https://freewebsfarms.com/how-to-remove-malicious-program-using-yet-another-cleaner/">How to remove malicious program using Yet Another Cleaner</a> </li> <li> <a href="https://freewebsfarms.com/enable-this-feature-to-increase-security-for-windows-10/">Enable this feature to increase security for Windows 10</a> </li> </ul> </aside><aside id="categories-2" class="widget widget_categories"><div class="section-title"><h2 class="widget-title">Categories</h2></div> <ul> <li class="cat-item cat-item-13373"><a href="https://freewebsfarms.com/emulator-software/">Emulator Software</a> </li> <li class="cat-item cat-item-13383"><a href="https://freewebsfarms.com/social/">Social</a> </li> <li class="cat-item cat-item-13858"><a href="https://freewebsfarms.com/system-security/">System Security</a> </li> <li class="cat-item cat-item-1"><a href="https://freewebsfarms.com/tech-tips/">Tech Tips</a> </li> <li class="cat-item cat-item-13834"><a href="https://freewebsfarms.com/telephone/">Telephone</a> </li> <li class="cat-item cat-item-13389"><a href="https://freewebsfarms.com/video-audio-editing/">Video Audio Editing</a> </li> <li class="cat-item cat-item-13365"><a href="https://freewebsfarms.com/words/">Words</a> </li> </ul> </aside></div><!-- #secondary --> </div><!-- .inner-wrapper --></div><!-- .container --></div><!-- #content --> <footer id="colophon" class="site-footer" role="contentinfo"> <div class="container"> <div class="site-info-holder"> <div class="copyright"> Copyright Freewebsfarms.com © All rights reserved. </div><!-- .copyright --> <div class="site-info"> PT Magazine by <a href="https://www.prodesigns.com" rel="designer">ProDesigns</a> </div><!-- .site-info --> </div> </div><!-- .container --> </footer><!-- #colophon --> </div><!-- #page --> <a href="#page" class="scrollup" id="btn-scrollup"><i class="fa fa-angle-up"></i></a><script type='text/javascript' src='https://freewebsfarms.com/wp-content/themes/pt-magazine/assets/js/navigation.js?ver=20151215' id='pt-magazine-navigation-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/themes/pt-magazine/assets/js/skip-link-focus-fix.js?ver=20151215' id='pt-magazine-skip-link-focus-fix-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/themes/pt-magazine/assets/third-party/slick/slick.js?ver=1.6.0' id='jquery-slick-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/themes/pt-magazine/assets/third-party/meanmenu/jquery.meanmenu.js?ver=2.0.2' id='jquery-meanmenu-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/themes/pt-magazine/assets/js/custom.js?ver=1.1.7' id='pt-magazine-custom-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-includes/js/comment-reply.min.js?ver=5.7.1' id='comment-reply-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-includes/js/wp-embed.min.js?ver=5.7.1' id='wp-embed-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/plugins/easy-table-of-contents/vendor/smooth-scroll/jquery.smooth-scroll.min.js?ver=2.2.0' id='jquery-smooth-scroll-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/plugins/easy-table-of-contents/vendor/js-cookie/js.cookie.min.js?ver=2.2.1' id='js-cookie-js'></script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/plugins/easy-table-of-contents/vendor/sticky-kit/jquery.sticky-kit.min.js?ver=1.9.2' id='jquery-sticky-kit-js'></script> <script type='text/javascript' id='ez-toc-js-js-extra'> /* <![CDATA[ */ var ezTOC = {"smooth_scroll":"1","visibility_hide_by_default":"","width":"auto","scroll_offset":"30"}; /* ]]> */ </script> <script type='text/javascript' src='https://freewebsfarms.com/wp-content/plugins/easy-table-of-contents/assets/js/front.min.js?ver=2.0.11-1618210839' id='ez-toc-js-js'></script> </body> </html>