[MUSIC PLAYING] RICHARD D. HANSON: I'd like to welcome you to another episode of our continuing effort to learn Microsoft Access. Today, I want to talk about tables. And the main way that you work with tables directly in Access is with a data sheet view. So we're going to dive into data sheets and what they look like and how to use them, so that you can more gracefully deal with the data there. 

Now, later on, we'll attach tables to forms and tables to queries and reports. And you won't, as a user, be working directly with the tables unless you're just using the database for analysis. And a lot of times, just doing the analysis, you don't wrap it elegantly in a form or anything. You just have the table and you select your data and you search for your data and you collate it and so forth. 

So what we want to do is, you will have a great opportunity to work with tables in a data sheet view. So what we want to do is be fairly thorough in the things that you can do and see when you're working with data sheets. So what I want to do is open up this table of contacts, is a significant enough table that it has plenty of objects that we can work with. 

Now, when you open a table and it's wider than the screen or it's longer than the screen, of course, you're going to have scroll bars like you're used to seeing in Word or Excel or PowerPoint. It'll have both a horizontal and a vertical here and a horizontal scroll bar, letting you go back and forth and up and down. And so what you'll see is the ability to move. 

And frankly, the vertical one actually has a couple of extra features. It has what they call a scroll tip there that you see the record 8 of 18. And it lets you see what record is going to be at the top of your screen. So if I scroll down here and it says I'm at record 32, this record right here should be 32. So you'll know about how far you are in the data table. 

Now, that's particularly important when you've got a data table that is as many as 3,000, 4,000 lines long, or 20,000 lines long. There really is not much of a limit, except for the size of the database itself, as to how big your table can be. I've pushed as many as 17 million records in a data table in Access. Won't ever do it again because Access gets a bit sluggish and you need a bigger tool for that. 

But to illustrate, it has the capacity to push pretty much as many records as you need to in each data table as you are working with it. Now, navigating around a data table is relatively simple. You can use the arrow key. I'm using the right and left arrow key to get around the fields this way. If I want to go up and down, the up and down arrow key works fine. 

One issue that I have with users a lot is that they will tend to want to use the Enter key. And sometimes, the Enter key works just perfectly fine to go from field to field, but you have to set your options such that the Enter key goes to the next field. 

Otherwise, you want to instruct people to use the Tab key. So here I'm using the Tab key. And it'll go all the way to the end and then go to the next record, and so on the next record now. And using the Enter key does the same. Both enter keys, by the way, the Enter key over by the letters and the Enter key in your number pad work the same way. 

Now, you have a few elements in your data sheet in the ribbon up here in your data sheet view that are peculiar to just the data sheet itself. So here, you have a filter where you can filter according to what you desire. It'll bring this thing up here. This filter here is the same as if you clicked on this down arrow right next to here. 

And it acts a lot like what you are used to doing in Excel. So you can get very comfortable, you can sort it, you can filter by these items, or you can filter by the list. In some query data sheets, now this is a table data sheet. A query data sheet looks identical and acts the same way. Some query data sheets won't be able to give you this list, or it'll run the query for quite a while before it actually comes to the screen. 

In either case, if it can't give you the list, it'll still let you go over here and filter by these different items, equals does not equal and such, over here. Others in the data sheet is you see that ascending and descending is there. So you can highlight this. And instead of doing here and here you can do up here as well. Your refresh item is here also. So you can refresh your data if you've just had a query working on it. 

Let's say you've had a query in a tab over here, for example. And after you did the query, you came over and just tabbed over to the table. The table won't have the modifications because the table remains static while it's on the screen. So because it's on the screen, what you do is you just come up here and click the Refresh button, and it refreshes all the data and makes sure that it looks like what the data that is actually in the table. 

So you have a few of those specialized items. You do have Find and Replace also. And you have your text formatting here. And I already highlighted these two items, but you'll see them and be able to use them in a data sheet view, much like you use them and do with them in a spreadsheet. Now, you do have the data sheet specific menus up here, where you've got what you can do with Fields here and what you can do with the table as a whole. 

You can be able to change the look and feel of the table as well as the look and feel of fields, change their data types, and so forth and so on. This allows you to have specific tools that you can use to modify the data and the data types in the table. 

Now, something that is a little bit unique. You have what they call record selectors over here on the left. Now, the record selectors here allow you to select a specific record. And if I was wanting to delete a record, hitting the Delete key on the keyboard will take that record away and then give me the dialog box so that I can reject the change if I accidentally brushed over the Delete key while I was trying to work with other data and you didn't want to do that. You can say no and it'll put that record back. 

But the idea here is when you select records here and hit the Delete key, it'll ask you if you really want to delete that number of records. And you've got one chance and one chance only to reject it. If you do want to delete it and say Yes, and then you change your mind, there actually is no undo key. So if you come up here, there's an undo key up here. 

But the undo key doesn't relate to deleting records here. So if you click Yes, you won't be able to undo this delete operation. Remember that fact because you have to be sure that you want to delete those records. 

So, now, if you want to edit a record. So let's say Matt Sakes' name is spelled wrong here. And it should be instead of Sakes it should be Saks. The minute I start editing a record here, you notice that this pencil comes up over here. And since that pencil is there, that means this record is in edit mode. In other words, this record has an edit to it. 

And generally at this point, it has not recorded the data in the actual database yet. Now, the only reason, the only way that it can put that data in the database is that the data is valid. So in that last name field, of course, Sakes and Saks would both be valid. They're both going to be short text. That's fine. 

But if you put invalid data in any one of these fields that doesn't belong, it will then reject that and not allow this pencil to go away. But it will not write the data to the data table. So it has to have valid data. And it has to have a primary key in order to write the data. 

Now, if I were to come down here and click another record, notice the pencil goes away immediately. And that data is now written into the database. Now, if I were to put in here the contact type, I'm going to say buyer both seller, I just don't want to deal with this guy anymore. So I'm going to put this in. 

Notice an AutoNumber field is here. So it's going to put in 66 already. And when I tab off of it, it's written record. And now this guy that's a jerk is in there. And I decided to delete the record because I really don't want to call anybody a jerk. So I say Yes to deleting that record. 

Now, we already talked about moving between records. And you can move between records by the up and down arrow. You can move between a batch of records by your page up and page down. Notice that it's going a whole page instead. So 39 is the bottom of this page. If I hit the page down, it'll go to 39 being at the top of the page as well. 

You can change records by positioning the cursor and saying, I want to be on this record. Or you can move to change records by the scroll bar over there. If I want to search here and use the filter, if I want to use wild cards, I can go up here to text filter and say equals. And I want everything that begins with, let's look for Christopher. And I want everything that begins with Chris. And star gives me the wildcard for anything after that. And I can click OK. And thus, in the equals box, I can use a wildcard. 

Now, there are several other wild cards also. And those other wild cards are question marks for single-character brackets to match a list of characters, or from and to, you can have an exclamation point with brackets to exclude a list of characters. So, for example, you can say 19, and then in brackets you say exclamation point 67 with a bracket afterwards, and 6 or 7, and then a 1. 

And, for example, if you wanted to find 1951 but not 1961, it would ignore everything that was 196 or 197 with a 1 after it. So it looks like-- this one is weird. And that's why-- so instead of Christopher here. If I was looking for a year, I can type 19 and then a bracket. And now I'm going to say not for the exclamation point. 

I don't want those two characters to be 6, 7. And then close the bracket, and 1. So I want to look and find 1961s or 71s-- I don't want to find 61s or 71s, but I can look for 1951, for example, it would find, or 01 or 111 or 21, it would find those. And of course, it didn't find anything in first name by that criteria. 

So now, up here though, so when you get to this point, in order to clear the filter, you can do it one of two ways. You can click the down arrow here and clear the filter here. It's very similar like you would in Excel. Or you can come up here and toggle the filter. And this turns off all of the filters, as though you would do in Excel when you turned it off the ribbon. 

Hopefully, this gives you a good idea of all the things you can do in a data sheet view. So I thank you for being with me today. Please hit the Subscribe button. You'll know when the next video comes out. And hopefully, we'll see you next time. Thanks.