When we use Microsoft Access or Microsoft Excel, it is easy and common to sort text or number columns. There are special buttons in both programs that allow us to sort in ascending or descending way. The button for ascending sorting has a blue A above the red Z and an arrow pointing down. And on the button for descending sorting we see a red Z above the blue A with the same arrow. All you have to do is to put the cursor in the column you want to be sorted and click on the needed button.

This function is great for text and number columns. But, when you need to sort the mixed types of columns with both text and numbers, the challenges start. The simplest examples would be something like this, keeping in mind that we want to achieve the ascending sort. The first example is the part numbers such as W4, W39 and W212. And the second example, the addresses, 4 Main Street, 39 Main street and 212 Main street. Which would come first on the computer?

We would normally sort them differently from the way computer does it. People look at the whole string of characters, whereas Access or Excel would sort them on a different logic, because to a computer, 4 comes after 2, that is why W212 would be before W4.

In order to solve the Part Numbers problem, try to number the parts in a way, so that all numbers had the same amount of characters, for example W000004, W000039, etc. In this case the program would sort it the way we expect. If it is impossible to change the pattern, you should create a new Access field or Excel column and name it PartNumberSortable. Then create a query in Access that will convert all the numbers to a sortable type. A logic like left([PartNumber],1)$ format(mid([PartNumber], 2),”000000”) can be placed under the new field in the query. Your traditional part numbers will continue to list in all reports and queries but they will be sorted by this new field, which will stay hidden. It is also important to modify all your data entry forms, for them to automatically generate the new field.

As for those, who work with Excel, you can also create a new formula for the column. Something like this: =Left(A1,1)&Right(“000000”&Mid(A4,2,8),6). It is assumed in this formula that the original number is in column A.

It is just one of the problems that you can encounter working with your database. Access is a very complex database, so it will always bring you pleasant or unpleasant surprises. If you have an address problem, or something bigger, you should know that there is always a way to solve it.

Anything about microsoft access database: great training materials, all sorts of business products (that can be tailored for the specifics of your business), special microsoft access database blog with hints, tips and tricks about MA databases.

All this and more from a trustworthy UK based company which specializes in microsoft access database and is assisting clients all over the world.

Tags: microsoft access, microsoft access database

Microsoft Access application is the one that requires the most training out of all Microsoft products. It is much easier to learn Word or Excel, and special Access training is important even for computer professionals. Sometimes it is not enough to learn only the basics. Advanced training course is mandatory to most beginner Access users and it shouldn’t be a substitute for database experience. This article will open up some of the tips that not even all of the experienced Access users know.

While designing a database, starting with planning it on paper can solve so many problems in the future. When you start designing sitting at the computer and keyboard without a proper plan, this project is doomed. It may lead to some mistakes that you will try to find and fix for hours, when those mistakes could have been avoided during the short planning. Do not work on the production database. It is very important to work with a prototype first, so that if you make a critical mistake you will not destroy all company’s data. Only after you complete the prototype and properly test it, you could put it out to other users. Another important thing to remember from your Access training is to know all the fields and relationships, properly select names for the elements and commit to them as well as never let users edit the fields manually but use forms instead.

People also learn how to write VBA and SQL code during the advanced Access training. It allows them create strong databases. But it is also the way to make a lot of mistakes. You should document comments to all codes. Don’t keep it outside of the application to avoid them being lost. Always write an understandable code, don’t hope that other developers will be able to ask you what the code is about. You might not be available by that time. Get inspiration from other people’s codes. You can even use the code if it is allowed by the author. But first you have to understand how it works. This will let you code better and avoid some mistakes. It is also advisable to write error handlers, so that your users didn’t get frustrated when getting error messages. And avoid using GoTo, Smart Codes or SendKey, which will lead to future problems, sometimes even if it is used by very experienced programmers.

And the most important rule, which you might have heard during your introductory, advanced training or simply learned it the hard way, after doing your personal mistakes, is to make backups. One of the most frustrating things that can happen with database is when all your data disappears, so it is much better to prevent this unpleasant surprise.

The world of computers and computer applications expands all the time. But you can turn this problem into your advantage by catching up with this knowledge and becoming a pro, for example in microsoft access database sphere.

Please visit this web site that is dedicated to microsoft access database topic and has a nice line of microsoft access database products available.

Tags: microsoft access, microsoft access database

All Microsoft Office products are very useful and it is definitely worth gaining skills in all of them. It is important for your career too. And if you need to learn how to store and manage the information, it is good to learn how to use MS Access. This program can help you landing a job, because many companies need a method of storing and extracting data. All the paper based data storing systems are now replaced by technology, like databases. MS Access is the bestseller among other desktop databases. It was a huge development in database market, when Access occurred and many companies realized that they cannot compete with it.

Access is a perfect utility for developing databases for a small amount of users. And in the whole Microsoft Office package, Access is the most challenging for the beginners to learn. The reason is the amount of components and functions there is in Access, compared to other applications. So, there are different levels of Access learning. You can create some simple systems using the wizard, which will guide you through the process. If you need to perform more substantial things, you will need more than just a wizard, but also some reputable Access sources.

When you learn Access, there are many objects for you to consider learning. You will be able to learn some relational database theory, which explains how data relates to each other. It is the most important step of learning the database, because everything else you learn will be based on this feature.

Before getting to design of screen interface, you need to create data storage areas. You will learn how to build tables that contain the data. The amount of tables you have will depend on the type of data you plan on holding. Then you will be able to build screens around tables. Designing screens comes with more practice and you will find out for yourself the good and bad ways to do it.

You will also wish to extract information from your database and ask questions. You will learn how to create a query and ask your database about any information you need to get out of it. Queries will instantly show you the data you required. With this option you will be able to forecast and spot trends.

With the help of report, you will be able to satisfy another requirement – to present data in a neat format. It is quite easy to create simple reports but with more learning and practice you can create something more dynamic. It is a great feature to be good in. And while more companies switch to using Access database, your knowledge will useful in any of these companies.

Anything about microsoft access database: great training information, all sorts of business products (that can be tailored for the specifics of your business), special microsoft access database blog with tips and advice about MA databases.

All this and more from a trustworthy UK based company which specializes in microsoft access database and is helping clients all over the world.

Tags: microsoft access, microsoft access database