Entering and Modifying Data in Datasheet View

Locked lesson.

Upgrade

Quick reference

Entering and Modifying Data in Datasheet View

Learn how to modify table data in datasheet view. Resize, hide and delete columns, edit data effectively and use the record selector to navigate. 

When to use

It is important for all Access users to know how to enter and modify data. These are basic skills that are used consistently when building a database. 

Instructions

Resizing Columns and Rows

  • From the Home tab, click the View button and select Datasheet View. 
  • Drag the column boundaries in or out to resize. 
  • OR, right-click on the column heading.
  • Choose Field Width.
  • Enter the Column Width, e.g. 20. 

Alternatively, we can resize multiple columns automatically to the width of the longest word in the cell. 

  • Select multiple columns. 
  • Right-click on any of the column headings. 
  • Choose Field Width. 
  • Click Best Fit. 

Follow the same process to adjust the height of the rows. 

Moving and Hiding Columns

Moving Columns

Sometimes we might need to work on two columns that are not positioned next to each other. To avoid unnecessary scrolling, we can move the columns without affecting the underlying data structure. 

  • Select a column.
  • Click on the column heading and drag and drop it to a new location in the table. 
Hiding Columns

If we are not working on a column or columns and would prefer to remove them from the view, we can hide columns. When we hide a column, it temporarily hides it from view, it doesn't delete the column or the data. 

  • Select one or multiple columns. 
  • Right-click and select Hide Fields.
Unhiding columns
  • Select one of multiple columns, 
  • Right-click and select Unhide Fields. 

The fields with checkmarks next to them are currently shown in the table. 

  • Check the box next to the hidden field to show it in the table. 
  • Click Close. 

Deleting Columns

There are a couple of ways to delete columns in Access. 

  • Select the column or columns. 
  • From the Table Fields tab, in the Add & Delete group, click Delete. 
  • OR, select the column or columns. 
  • Right-click on the column. 
  • Select Delete from the menu. 

Editing Field Information

  • Click in any field. 

The curser will become active and a pencil icon will appear in the record selector on the left-hand side. 

  • Modify the field. 
  • Press TAB to move to the next field. 

If a field is highlighted in black and we start typing, we will overwrite the current field value. 

Adding New Records

New Records can be added to the table by clicking in the row labeled 'New' at the bottom. New rows are denoted with an asterisk (*). 

Record Selector

We can navigate through our records using the Record Selector icons at the bottom of the screen. Click the right arrow to move down to the next record and the left arrow to move up to the previous record. New Records can also be added from here. 

Alternatively, we can use the arrow keys on our keyboard to cycle through records in a table.

Login to download
  • 00:04 In this lesson, we're going to take a look at how we can edit and modify records and
  • 00:08 columns in Datasheet View.
  • 00:10 Now if we take a look at our columns,
  • 00:13 we can very easily adjust the column width by dragging the column boundaries.
  • 00:18 If I hover my mouse between CustomerName and Priority I can simply drag that
  • 00:22 column out to make it bit wider or drag it back in to make it a little bit narrower.
  • 00:27 What I can also do is get very specific about how wide I want one or more columns.
  • 00:33 So, for example, if I select these three columns I can right click my mouse and
  • 00:39 go to Field Width.
  • 00:40 And here I can define how wide I want these columns to be.
  • 00:44 So I'm going to say let's go for 20, first of all, click on OK and
  • 00:48 it's going to widen those out.
  • 00:50 If I want all columns in my table to be the best fit, and what I mean by that
  • 00:55 is that the columns are exactly the correct width for their contents.
  • 01:01 What I can do is select all of my columns, right click,
  • 01:05 go to Field Width and choose the Best Fit option.
  • 01:09 And that's going to auto resize all of those columns is to fit exactly what's
  • 01:13 in them.
  • 01:14 It's also worth noting that you can do similar things with rows.
  • 01:18 So, I can grab any of these row boundaries drag it down and
  • 01:22 notice by dragging one it's going to resize all of them.
  • 01:26 Alternatively, I can select all of the rows, right click, and go to Row Heights.
  • 01:32 And this time we don't have a Best Fit option.
  • 01:34 But if I select Standard Height and click on OK,
  • 01:37 it's going to put those back to how they're supposed to be.
  • 01:40 Now something else you might want to do when you're looking at this Datasheet View
  • 01:44 is you might want to rearrange the order of the columns.
  • 01:47 For example, in our table, all of our columns fit very nicely on the screen.
  • 01:52 I don't have to use my mouse to see all of them.
  • 01:55 But as your dataset grows,
  • 01:57 it might be that your columns start going off the edge of the page.
  • 02:00 And you find yourself having to scroll back and
  • 02:02 forth to see the information that you want.
  • 02:05 And if maybe you're only interested in the CustomerName and, let's say,
  • 02:10 the description of the error you might want to move Description next to
  • 02:14 CustomerName.
  • 02:15 So it makes it a lot easier for you to read the two columns.
  • 02:19 So what you can do here is grab the Description column and
  • 02:22 drag it next to CustomerName and it will simply reorder them.
  • 02:26 And it's worth noting that if we go back into Design View,
  • 02:29 that doesn't actually change the order of the fields in your table.
  • 02:34 So the underlying data is preserved.
  • 02:36 It's just the way that you're viewing in Datasheet View where you're going to see
  • 02:40 that change.
  • 02:41 Now I'm going to drag this back to where it was.
  • 02:45 Another thing you might want to do here is hide columns.
  • 02:48 So instead of deleting them hiding is a good option if you maybe have columns in
  • 02:53 your table that are of no interest to you and
  • 02:56 you want to just exclude them from the view.
  • 02:58 So maybe I'm not interested in seeing the Technician that's assigned to these
  • 03:02 Tickets.
  • 03:03 I don't want to delete the column, what I could do is right click and
  • 03:08 hide the field.
  • 03:09 And that temporarily removes it from my view.
  • 03:12 If I want to bring that field back, if I right click on any column heading and
  • 03:17 choose Unhide Fields, I can then just select the Technician field and
  • 03:21 it's going to put that field back.
  • 03:23 So don't forget about that option as well.
  • 03:25 And, of course, if you do want to delete an entire column,
  • 03:28 you can simply select the column, press the delete button on your keyboard.
  • 03:32 Or you can choose the Delete command from the Records Group on the Table Fields tab.
  • 03:37 Now when it comes to editing records in Datasheet View,
  • 03:40 there's a couple of little tricky things here that you need to be aware of.
  • 03:44 If I want to edit, for example, the CustomerName in this first record,
  • 03:48 I can simply click and then I'm in cell and I can make my changes.
  • 03:53 Once I have finished editing, if I press the tab key on my keyboard
  • 03:57 that's going to tap me across to that next field.
  • 04:00 And notice that the value in that field is highlighted in black.
  • 04:04 So because it's highlighted, if I were to start typing
  • 04:07 now it's going to overwrite the value that's currently in the field.
  • 04:12 If you don't want to do that you need to make sure that you click your mouse and
  • 04:16 then you can append to that field.
  • 04:19 What you'll also notice is that when you are editing a field
  • 04:22 you'll know that you're in editing mode because you get this little pencil at
  • 04:26 the end here in what we call the record selector area.
  • 04:29 And if you want to come out of editing mode press the Escape key on your
  • 04:33 keyboard.
  • 04:34 The final thing to know is how to add new records in Datasheet View.
  • 04:38 I could simply go down to the bottom of the table and
  • 04:41 just start adding in a new record.
  • 04:43 Alternatively, right underneath we have what we call the record selector.
  • 04:48 And this little area allows you to do a few different things.
  • 04:51 I can step through each of the records in my table.
  • 04:55 So currently I'm clicked in record 1 of 16.
  • 04:59 But if I click the arrow, it's going to move to 2, 3, 4, so on and so forth.
  • 05:05 And I can go back the other way.
  • 05:06 If I want to jump to the first record, I can select this arrow to the last record,
  • 05:11 I can select that arrow.
  • 05:13 And then right at the end here we have a new blank record option,
  • 05:16 which will allow me to append essentially a new record to the bottom of the table.

Lesson notes are only available for subscribers.

Gift this course