As we know, records are usually shown in the same order as they were entered in the database in the beginning.
For example, the entire data coming from the Registrar’s Office is always placed in ‘Student Number’ order. This is the reason why it often becomes difficult to search for a particular name by simply scrolling through the whole records. It is like searching a big library to find a specific book when all the books were listed according to their date of purchase!
However, if the whole data is properly sorted, you can easily find a particular book by scrolling through the records. You need to learn how to do sorting in Access 2007.
Here is how to do it:
Quick Sort
Ascending: Descending: Clear All Sorts: We can use the “Quick Sort” feature to sort just any field into an alphabetical or numerical order. The first step involves moving to the column on which you want to base the sort.
• Click on the “Ascending” option in the “Sort & Filter” group on the “Home” tab of the Ribbon. Now, student names appear in alphabetic order.
• Click on the “Clear All Sorts” button in order to reset the whole data in its original, unsorted order.
• As you can see in the screenshot below, all the names are now arranged in ascending order.
How to Permanently Change the Display Order in Access 2007
If it is compulsory to keep the new display order until you open the table the next time, you can close the table and save all the changes to its design. The next steps are as following:
1. Move to the field that you want to sort. Middle name, for example.
2. Click on the “Ascending” or “Descending” option if you want to keep the data in reverse order.
3. Click on the “Close” button to close the table.
4. When asked, save all the changes to the table. Now, click on the “Yes” button or press Enter.
5. Next, press “Enter” again to reopen the table. Here, you will find it in the new order.
We may accidentally save unwanted changes to the table design, especially when performing a quick sort on some other field. Sort the correct field again and save to retrieve your original table order.
Sorting in a Query
We can also carry out and store sorts in a query. A query is particularly useful when we have to sort more than one field. Using a query, we can generate a display sorted on any field, as well as create complex sorts within sorts.
Indexes
In a book, you can link an index to another field also. This feature speeds up the searching, grouping and sorting process in a database. Another useful function of an Index is that you can use it to prevent duplicate data entry.
Here is how to link an index to another field:
1. Click on the “View” button in order to switch back to the Design View.
2. The “Student No.” field has an index set to yes. This setting must be there as this field is to be used as the Primary Key.
3. The middle name field that already has an index of “Yes” is to be frequently used to sort and duplicate values.
4. Click on the “Hall” field and set Index to “Yes”using the list arrow.
5. Now click on the “User ID” field where index is set to Yes.
6. Click on the “View” button one more time to switch back to Datasheet View. Press Enter to save all the changes to the table design when asked.
Adding, Editing and Deleting Records
New Record: Delete Record: Every time we make changes to a table, we alter the original data file. And since a database doesn’t make a functioning copy of the file first, it remains important to keep a file’s back-up copy. This can be helpful in case any mistakes are made while carrying out amendments.
Press the “New” button to add a new record to the database. With the existing data, new records are always placed in the end. Access produces a new empty record when you start to type. This is when the existing record indicator changes to a pencil from an arrow:
• Click on the “New” button.
• Now press “Enter” to move ahead.
• Type your own name and move to the “Title” option. Now press “Tab” to move ahead.
• Repeat the 3rd step until the most of the record has been filled out.
You can also add a picture to the “Photograph” field. Use the Clipboard to make it a simple process. It may also appear as an icon as soon as you right-click on the field. Now double click on it to open.
• Press the “Delete” key and choose the “Delete Record” option. Here, you will be given one more chance to reconsider your decision, since it can be potentially very damaging to delete data forever.
• Press “Enter” to confirm the deletion.
Note: You cannot undo to recover the record now. And if you want to delete multiple records:
• Use mouse to point to the left-hand edge on the first selected record to delete. Here, the cursor will change into an arrow.
• Mark the record, hold the mouse button, and drag through the required records.
• Press “Delete” to delete selected records.
• When you click on the “No” button, the records will reappear.
Note: Since records are lying next to each other, you can’t use “Ctrl” to delete them. But you can select non-contiguous records and delete them.
Selecting Records
Database has a feature that allows you to extract sub-sets of records according to pre-set conditions. For example, you can find books written on a specific topic by a particular author in the Library. Also, Access has two methods for selection – Select using a QueryQuick and Select using a Filter.
Quick Select
Using a filter, we can make simple selections on the table. Access 2007 has a proper mechanisms for this.
• Press the “Ctrl+Home” key to move to the first record.
• Click on the “Selection” button and choose the “Equals” option.
• Move to the “UserID” field, find a record, and select it by dragging through the figures.
• Click on the “Selection” button again and move to the “Tutor” field
• No, click on the “Filter” button to check other methods of filtration.
• Uncheck the “Select All” box and tick the tutor required. Now press Enter.
• Click on the “Filter” button one last time and tick a second tutor. Finally, click on the “OK” button.
If you find any difficulty in sorting with Access 2007, drop your queries in the comment section below. Our experts will post out the solutions at the earliest and help you with any sorting issue in Access 2007, simple or complex.