Ms Access And Ms Excel - What To Do When Sorting Does Not Work
In both Microsoft Access and Microsoft Excel it is very easy to sort columns of text or numbers. Both programs supply us with toolbar buttons that allow either ascending sorts (1 ... 999) or descending sorts (999 ... 1). The Ascending button has a blue "A" above a red "Z" with a downward pointing arrow to the right of the 2 letters. The Descending button has a red "Z" above a blue "A" with a downward pointing arrow to the right of the 2 letters. Simply put the cursor in the column you want sorted and click once on the appropriate button.
As I stated in that first paragraph, this works great for text or numbers. The challenges start when you want to sort mixed text and numbers. Here are 2 simple real life examples. (NOTE: the remainder of this article assumes we want ASCENDING sort order)
Example 1: Part Numbers. On a computer, which comes first?
- W4 - W39 - W212
Example 2: Addresses. On a computer, which comes first?
- 4 Main Street - 39 Main Street - 212 Main Street
In both cases, the computer sorts them opposite to the way a human would normally want them sorted. A human automatically looks at the entire string of characters and then sorts using a more complex algorithm than a basic computer program?s sort logic, like that found in Access and Excel. To a computer, 2 comes before 4, so W212 sorts before W4.
Let?s examine the Part Numbers problem first.
If possible, have the way parts are numbered standardized so that all part numbers have the same number of characters. In our example, use W000004 and W000039 and W000212. This scheme allows for 999,999 unique part numbers AND it sorts the way we would expect it to sort.
If it is too late, and you cannot create the required pattern, then create a new field in Access or a new column in Excel, and call it PartNumberSortable. Then, in Access, create a query that converts all the existing part numbers to a sortable version. It will be an update query with update logic like this placed under the PartNumberSortable column in the query:
left([PartNumber],1) & format(mid([PartNumber],2),"000000")
All reports and queries can now continue to list your traditional part numbers, but they can be sorted by the new field, PartNumberSortable, which would stay hidden. You would also have to modify any of your data entry forms and routines so that they generate the new field automatically.
In Excel you can create a formula for the new column. Here is one that works.
=LEFT(A1,1) & RIGHT("000000" & MID(A4,2,8),6)
This formula assumes that the original part number is in column A.
The address problem? Trickier. I?ll tackle that one in another article. In the meantime, for more MS Access tips like this, visit http://www.databaselessons.com
p.s. All the examples were processed on a computer with MS Office 2000; newer versions of Access and Excel may work slightly differently, or have more intelligent sort capabilities.
Richard Killey has been creating MS Access databases since 1996. His Access websites have been online, helping thousands of beginners learn VBA, since 1997. More MS Access tips can be found at http://www.databaselessons.com
2JobSearch.net - Jobs | Work From Home | All Your Employment Needs
2jobsearch.net has partnered with CareerBuilder.com and HomeBasedBusinessListings.com to bring you the best in job searching, online recruiting and work from home opportunities
Home Based Business Listings
Home based business listing is your best source for legitimate home based business and work from home opportunities.
Online Income
Internet Based Home Business - Find the Home Based Business You've Been Searching For
Home Based Business Opportunity
Work From Home Opportunity - Easy Home Based Opportunity
Home Based Business Listings Blog
Find The Right Home Based Business For You.
Work From Home Opportunity
Home Based Business Opportunity - Start Your Own Home Based Business
Work From Home Blog
Work From Home Blog - Great Source of Home Based Business Information
Related Articles:
Internet Based Jobs - Working From Home
There are thousands and thousands of internet based jobs on the market today waiting to be filled There is nothing like rolling out of bed and working from the comfort of your own home
Work From Home Jobs For Moms - Jobs You Can Do at Home
A Legitimate Work from home job is a desire that many moms have, and being able to generate a great income while working from home is very possible
I created this work from home jobs for moms lens to give you information about legitimate work from home jobs for moms, and for anyone looking to make part time to full time income
How To Get Results On A Work-At-Home Job Search
Employers with work-at-home jobs are notorious for not responding to applicants which is extremely frustrating to the job hunter Without feedback, applicants don't know why they weren't chosen or even if their resume was received
Internet Based Jobs - Working From Home Has Never Been So Easy
It is hard to believe but a person can easily make thousands of dollars each month while sitting in his own home Thanks to the World Wide Web, ecommerce businesses are on the boom
Being A Work At Home Business Opportunity Seeker And Finding Work From Home Jobs
The problem with work from home jobs is simply that so many people want to have them Because of this, there are lots of people who want to take advantage of people who truly want to be able to have the freedom of a home business
Work From Home - Online Resource Center for Home Based Online Jobs and Internet Based Business
Welcome to Work from Home?s ?an online guide and resource website to help you Start and grow your Home based Job and Business. Please note that we are only offering information on Work from Home Jobs and Business opportunities. We are not offering you a job or a business tie-up. Please read through the website as you will find valuable information and resources to start and grow your home based business. www.homebasedonlinejobs.com
Legitimate Work at Home Jobs Career - 4 Amazing Tips to Increase Efficiency
When you have picked one of the many legitimate work at home jobs career and are ready to settle down and make some money, it helps to make your home based business as efficient as possible
One of the most important benefits resulting from obtaining legitimate work at home jobs career is the ability to improve income levels through increased efficiency in performing the tasks associated with the jobs
Internet Home Based Business : Legitimate Work at Home Jobs Opportunities and Advantages
There are many advantages involved in choosing an internet home based business as your primary or secondary source of income Use these benefits to find a legitimate work at home jobs that suits your needs and desires
Work at Home Businesses Versus Work at Home Jobs
Is a work at home business better than a work at home job? You bet. Hands down. But each option has pros and cons. It may be that for right now you're just not ready to start a work at home business. Its a big committment. That's OK. But if you're dedicated, smart and willing to work harder than most, settling for a work at home job could be selling yourself short.
Work From Home - How To Prepare For A Work From Home Job
Surely, you must have already heard about the perks of working from home Not only do you get the opportunity of being with your loved ones, but also the chance of earning good money doing what you like
Legitimate Work From Home Jobs - 4 Important Things to Consider Before Starting This Career Path
When you are selecting legitimate work from home jobs career, money is important, but there are some other factors that should be considered before making the final decision
Before beginning even legitimate work from home jobs career, you should think about some of the factors that will help to make your venture succeed or fail
At Home Work - Find Your Next Work At Home Job
At home work is easy to find these days thanks to the internet Work from home jobs are a form of self employment, which is hitting today's society in huge numbers
Work At Home Job Versus Work At Home Business
I often hear people complain about how they shouldnt have to pay for a business opportunity because "you wouldnt have to pay to get a job outside the home." It irritates me when I read that. Not because I think you should have to pay for a job - even a work at home position - its because they think that their should be no investment to start up a work at home business.
Finding that Work at Home Job - A Job in Itself
The trend of corporate America creating work at home jobs for telecommuters is growing, which is good news for those of you who are looking to do your work in your pajamas every day. Technology has made the work at home job more appealing to both bosses and employees, though it does not mean the trend has hit the mainstream, so a work at home job can still be hard to find.
Work at Home Agent - Job Ideas For a Work at Home Agent
Are you interested in jobs that require you to spend hours on the phone talking to customers If you like personal relations work and have good interpersonal skills, then do consider the opportunity of being a work at home agent