Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Access makes it easy to search your tables for exactly the data that you're looking for using three techniques: finding, sorting, and filtering. Double-click on your SalesReps table to open it. Now, first, let's look at how to navigate around on our data. At the bottom, down here is the navigator. You can see what record you're on as you click around. Now remember, if you are using an AutoNumber field, that AutoNumber is not a counter. When you delete a record, these numbers don't change, but the total down here will.
Now there are Previous and Next arrows to scroll through your data. You can go back to your first record or to your last record. This arrow with a spark right here will take you to the bottom row to start a new record, no matter where you are. If you want to search for a specific value, for example Hodge, you can start typing in here, and it will get highlighted in your data as you type. Now, let's find all of our SalesReps who make $8 an hour. Scroll over to the right and find an $8 and highlight it.
When I go up here to Find, because I highlighted it, it will autofill. I have choices here for looking in the whole document, or just in this field. It can either be just a part of the field or the whole entire field. Let me move this out of the way. As I click Find Next, it will scroll through and find them all. It's finished searching the records, so I'll click OK. Now, let's say we want to give all of our $8 reps a raise. We can replace the 8 with 9.
I can either click on the Replace tab right here, or I could also use this Replace button here, if the window is not open. In the Replace With field, type in $9, and we can either replace them one at a time or go ahead and replace them all at once. Now notice that you cannot undo this Replace operation, and we do want to continue. Now, all of our $8 reps now make $9. Go ahead and cancel this window.
Now, let's work with our Customers table. Open up Customers. Let's say that one of our customers is no longer using our olive oils, and we want to delete them from our database. Go up to Find. It's autofilled this Customer ID, because that's what I was clicked on when we invoked the Find command. So let's change this to Uni and then change it to the current document and whole field, and click Find Next. Then cancel the window. It's highlighted Uni. Click on the gray box to the left of it, and up here, click Delete.
Now, because we have a relationship between our customers and the orders they place, Access notices that we're going to do a cascading delete. Not only are we going to delete this one record, but we're also going to delete all of Uni's related orders. Do we want to delete them? Yes we do. That change is permanent. Now, let's do some sorting. Click on the arrow field next to State. It gives us the options to sort it from Z to A, or from A to Z. Now, we can also filter.
If we just want to see certain values, click on that dropdown again. Turn off the Select All. Let's just look for customers from California, and scroll down, and Maryland. Click OK. Now, we just see those customers. Notice there is a filter here to remind us that a filter is applied. Drop it down again, and choose Select All, then click OK, and everybody comes back again. Click on Company, and now this time let's do the sort from up here.
Click on this Ascending button. Now, go over to the City field and double-click on Abilene. Click on this Selection button right here, and it gives us the options of finding everybody in Abilene, everybody except in Abilene, or as appropriate, containing or not containing Abilene. We'll go ahead and say Does Not Contain 'Abilene'. Now notice that this Toggle Filter button is now orange. Click on it repeatedly to toggle your last filter on and off again.
This big Filter button here has the same choices as clicking on this arrow right here. If you click on Text Filters, you'll also see Begins With and Ends With. Also notice this Clear filter from City, which completely removes all of your filters from memory, so that you see all of your records. Next, go up to Advanced, and choose Filter By Form. This blanks out your table, and now you can fill in the blanks to find what you're looking for.
This is perfect when you want to search for records that match more than one criteria. Go to State and choose Maryland. Then tab over to Web Page. We're going to find all the companies who do have Web pages. The Access programming used for blanks and not blanks is either Is Null, or Is not Null. Is not Null means that there will be data in the Web page. So, right now, we're looking for companies from Maryland who do have Web pages.
Go ahead and toggle your Filter on. We see two customers. So, now we can see if they advertise that they only use the finest ingredients, including TwoTreesOliveOil. Click the Advanced button again and choose Clear All Filters. The Advanced button also has an option for an Advanced Filter/Sort, which actually pulls up the Query Builder. We'll explore these in detail in a later chapter. Close this window. These filtering techniques also work when using Forms.
So, you can see that finding, sorting, and filtering records gives you a quick and powerful way to locate only the records you're looking for.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.