Learn how to insert a new record in a table. Author Jen McBee demonstrates how to quickly find an existing record using the Search tool, and how to edit and delete records, all skills you may be asked to demonstrate on the Access 2010 MOS exam.
- [Instructor] Managing records in your database is crucial to making sure you data is always accurate. In this video, we're going to explore how to add a new record to a table, how to update a record, and how to delete a record. I also want to review the process for performing a Find and Replace, just so you know how you can easily update the information in your table. On the Access 2010 MOS exam you may be asked to add a new record or to edit a record. And to quickly find that record, I want to show you a nice quick way to find a specific record so you can go in and edit it.
You may also be asked to delete a record and that's another way that your Find or Search can really help you locate that record that you need to delete. I have the 03_06 Exercise file open so let's get going. Let's open up our Employees table. We see that we have 10 or 11 records in here. If we wanted to insert a new employee, what we could do is just go to the next available field, or row, in our table.
Now, if this table had thousands of records we'd have to scroll all the way down to find that very bottom row. If that's the case, you can always come down and click on the arrow that has a sunburst next to it and that will insert a brand new record for you. Let's put a new employee in. The ID number is 1011. The ID number is an auto-number that Access put in for us. This person's going to be working in the sales department. I'll go ahead and put myself in as an employee, account manager, I'll be in building two, and my salary will be $50,000.
Great. I know that's very basic to know how to insert a record. The whole idea of preparing for the MOS exam is you need to be able to do things really quickly and so that's why I showed you the shortcut to just insert a new blank record and start typing. So there's our new record. Go ahead and tab down to get to the next blank record row. And what if I wanted to find a specific person in the table? I can come down to my Search field. Let's look for John Adams.
I'll type in Adams. See how it takes me right there. Now I can just go ahead and update that record. And let's also now talk about Find and Replace. In our location field, we have the prefix of BLDG and then the building number that follows and that's a little redundant. What I'd like to do is use Find and Replace to take the prefix BLDG out of each of the entries. Now, before you do any major editing such as we're about to do, it's a great idea to go ahead and make a copy of the table or the object that you're working in.
So I'll come over to the navigation pane and right click on Employees, choose Copy, right click in the same place and choose Paste. Now it's asking me if I want to keep just the structure, so in other words, just the fields and their properties, or do I want the structure and the data. I really want to hang on to all the records in my table so I'll leave that second radio button selected. I'm going to leave the default name Copy of Employees in there. And remember, I'm just doing this just in case I change my mind. There is no undo when you're using Find and Replace.
Or when you update your records, everything is kind of final. So this is just a backup copy for me. I'll go ahead and click OK. Super, now let's open our Find and Replace window by using Ctrl + F for Find on our keyboard. And let's have it find BLDG. I'll click the Replace tab and let's have it replace it with nothing. Remember, all we want to do is remove that prefix. Now, we can have it look in the entire document, in the entire table, or we can just have it look in the current field.
I'll go ahead and select Current Field and then make a point to come over and click in that field to make sure that Access knows that is our active, or our current, field. Next we can say match Any Part of the Field or match the Whole Field. Most of the time you will use Any Part of Field and that's what we're going to use in this case. So let's test it, let's make sure it's working correctly. Find the first one, replace. Oh, that looks good. I'll go ahead and click Replace All and everything has now been removed as far as that prefix.
Now it's just warning me that I won't be able to undo this replace option, am I sure that I want to continue? I'm going to say Yes because I created a backup copy of the Employees table so I'm going to be all set. Yes I am and I'll close this Find and Replace window. Perfect. That was an easy task. It's great that we had time to do a little refresher on inserting a new record, finding a record within a table, and also how to use Find and Replace.
On the MOS exam you may be asked to show your skill in completing any three of those tasks so be sure and practice. You'll have another chance to practice at the end of this chapter during the Chapter Challenge.
- Creating a new Access database
- Applying application parts
- Backing up a database
- Creating new tables and fields
- Sorting and filtering records
- Setting primary keys
- Importing data
- Creating basic forms
- Creating Access queries, including crosstab and multitable queries
- Creating Access reports