Chapter 6
In This Chapter
Opening an existing table or database
Adding new records to your table
Changing an existing record
Renaming fields and tables
Deleting unwanted records
Turning back time — to before you made your mistake
From remembering to change your car’s oil every 3,000 miles to cleaning out your rain gutters in the autumn, everything in your surroundings needs a little maintenance now and then. Most of that maintenance involves tidying up, getting rid of old or unwanted things, or making improvements. Sometimes all these things are part of the maintenance process.
Well, it’s no different for your database — an Access database needs a tune-up now and then, just to keep things running right. This can be as simple as checking for blank fields where you need to plug in missing data, as common as purging old or inaccurate records, or a practical matter such as changing the names of tables and fields so your database makes more sense to the people who use it.
Unlike getting your car serviced or cleaning out your gutters, however, maintaining your database isn’t expensive or difficult. Of course, not keeping your database in good working order can get expensive — it can cost you in terms of your time, the potential impact of inaccurate records on your organization, more time (and paper) wasted printing reports that include obsolete data, and the confusion that plagues those who use a database that has incorrect (or vague) field and table names.
Don’t worry, though — for all those doomsday potentialities, the solution is as simple as a few clicks, a couple of double-clicks, and a little bit of typing, and it’s free!
When you open Access 2016 (as we discuss in Chapters 1 and 2), the workspace offers you a list of recently opened databases, along with templates you can use to start a new database, as shown in Figure 6-1.
To open a database from the Recent Databases list, just point to it and click once.
If you remember where your database is stored, but it isn’t in the Recent list, use either of the following options to use the Open view:
Click the File tab to the left of the Home tab, and choose Open Other Files from the list of commands. Alternatively, if you’ve just opened Access, click the Open Other Files link at the bottom of the left-hand panel (shown in Figure 6-1).
Either way, the Open view appears onscreen, as shown in Figure 6-2.
By default, the Open view displays your most recently used databases.
If the database isn’t displayed, click This PC in the series of Open commands.
This displays the view shown in Figure 6-3.
When you find the database, open it by clicking its name.
The database file opens immediately, as shown in Figure 6-4.
If you don’t see your database listed in on the right-hand side of the workspace (after clicking This PC), click the Browse button instead. This opens a dialog box through which you can navigate to the folder that contains your database.
An introductory screen known as a switchboard (or perhaps — utilizing a feature introduced in Access 2010 — a Navigation form) may appear instead of the tabbed dialog box. Access is telling you that your database either contains some custom programming or was created by the Database Wizard. You probably have some special forms that help you interact with the information in your database. If you want to find out how to create your own navigation form, check out Chapter 23.
Within the All Access Objects list on the left side of the workspace, look for the table you want to open.
Each table has its own listing, accompanied by a table icon.
Double-click the table you want to edit.
The table opens in Datasheet view, and you can begin your maintenance of the data. You can add or remove fields or change the names of your fields — topics discussed later in this information-packed chapter.
Ever gone on vacation and forgotten your toothpaste? Sure, you can probably just pick up a tube at a drugstore in the place you’re visiting, but it’s still a pain. The same “Darn!” feeling overcomes you when you realize you’ve left something out of your database suitcase. (Okay, you might use a different four-letter word in that case, but this is a family publication.)
Luckily, adding a forgotten record or field to your table is about as easy as making a quick trip to the drugstore for that forgotten toothpaste — so easy that you may forget to say “Darn!” (or any other word expressing regret) when you discover a missing field. Instead, you’ll calmly launch into the following steps — one set for inserting a missing record and one set for inserting a missing field.
To add a record, follow these steps:
In the Datasheet view of the table that’s missing a record, click inside the first empty cell at the bottom of the table — below the last displayed record in the table.
Your cursor blinks in the first field in that record, as shown in Figure 6-5.
Type your information for the first field.
If the first field is an AutoNumber type, then you’re automatically placed in the second field when you click the row. In the second field, you can begin typing the data for that field. As soon as you start typing, the AutoNumber
field generates a new number and displays it in the field.
Don’t panic if the AutoNumber
field seems to skip a number when it creates an entry for your new record. When an AutoNumber
field skips a number, it means you probably entered (or at least started to enter) a record at some point during this (or a previous) data-entry session and then deleted it.
When you finish entering data into the last field for the new record, you’re finished!
Because Access saves the new record automatically while you’re typing it, you have nothing more to do. Pretty neat, eh?
If you want to add another record, press Tab and type away, filling in yet another new record.
With the field-challenged table open, follow these steps to add the field you’re missing:
In Datasheet view, find the field heading aptly called Click to Add (see Figure 6-6).
The column is typically placed at the end of your existing fields — so be prepared to scroll all the way to the end to see it.
Click the instructional Click to Add heading you found in Step 1.
A pop-up menu appears, from which you can choose the type of field this new field will be, as shown in Figure 6-7.
Choose a field type from the list.
The new field appears, entitled Field1
, and the Click to Add
column moves over one column. Field1
is highlighted and awaiting your new name for it, as shown in Figure 6-8.
Type the name of your new field and press Enter.
Your new field is created.
To rearrange your fields so the new field is where you want it to be among the existing fields, click the heading of the field column you just created — and then click again.
On the first click, the entire column is highlighted, and the black down-pointing arrow changes to a left-pointing white arrow. On the second click, the arrow acquires a small box just below it, indicating that you’re ready to move the column.
Drag to the left or right depending on where you want to drop your new field.
A thick vertical line follows you, indicating where the field will appear as soon as you release the mouse button, as shown in Figure 6-9.
When you’re happy with the intended location of the field, release the mouse button.
Your field is relocated.
You can also tinker with settings that go with the data type you choose — for example, if you choose a Number format, you can use the buttons in the Formatting section to determine how many decimal places will appear onscreen.
Getting rid of a field is no big deal — in fact, it might be too easy. Access does, at least, give you a little nudge (in the form of a dialog box) to make sure you’re positive you want to get rid of the field in question.
To get rid of an existing field that you no longer need, follow these steps:
Although your stuff is safely tucked away inside a table, you can reach in and make changes easily. In fact, editing your data is so easy that it’s hard to tell whether this is a good feature or a bad one.
To change something inside a record, follow these steps:
Click the field (the individual cell in the table) that you want to change.
The blinking line cursor pops into the field.
If your mouse has a wheel button, use the wheel to take a quick spin through the table. (For such a small innovation, that wheel is a big timesaver!)
Change the field.
What you change and how you change it is up to you:
Remove or add characters: Press Backspace to remove characters to the left of the cursor; press Delete to remove characters to the right. Insert new characters by typing.
If you’re in a time/date field and want to insert the current date, press Ctrl+; (semicolon). To insert the current time, press Ctrl+Shift+; (semicolon).
If you change your mind and want to restore the original data, press Esc or Ctrl+Z to cancel your edits.
You’ve built your table, and maybe you’ve done a stellar job from the get-go — you didn’t forget any fields, you put the fields in the right order, and you set up the fields to house the right kind of data. Well done, you!
Okay, back to reality. (Of course nothing in life is that simple, is it?) It’s not uncommon to realize, after building your table (and accepting my previous “Well done, you!”) that you need to make a few changes. You may need to change the table’s name or you might need to change one or more of the field names in the table. Or both! If this happens, it doesn’t mean you messed up or anything, it just means you’re a human being who, with even the most scrupulous planning and preparation, can make a mistake or change your mind — or perhaps the terminology has changed since the table was first created, and now it needs updating. Luckily, regardless of the reason you need to do it, Access makes it easy to make either kind of change.
Uh-oh. The field name you used when you first built the table has been the source of some confusion. People don’t know what “Status” means — does it mean the members have paid their dues or does it refer to whether they’re active in the organization? It’s clear enough to you, but it’s important that other folks who use the data feel confident that they understand what’s in it.
This sort of field-naming dilemma is common when you’re setting up a table for the first time, and can even crop up later on, when you’re working with a table that’s been around and in use for a while. The need to edit field names can arise for any reason, at any time; it’s never too early or too late to edit them.
So, for whatever reason, you find you have to edit a field name. What to do?
What could be easier than that?
So when you’re ready, follow these steps:
Double-click the field name, as shown in Figure 6-12.
The current name is highlighted in place, atop the column.
To replace the current name entirely, type the new name while the current name is still highlighted.
While the field name is highlighted, the very next thing you type will replace the current name — so don’t start typing at that point unless you want to replace the entire field name.
When you like the name you see, click in any cell in the table.
The new name appears at the top of the column, and you’re ready to do whatever you need:
To rename a field in the Fields tab in Datasheet view, follow these steps:
Click the field name that you want to edit.
You can also click in any cell in that column — just give Access some way of knowing which field you want to rename.
In the Properties section, click Name & Caption.
A dialog box opens (as shown in Figure 6-13) in which you can rename the field.
Press Enter or click OK to change the name.
This tells Access to accept your change, and you return to the table, with your field’s new name in place.
Renaming an entire table is not as common as needing to rename a field, but it can happen. Maybe you misspelled the name. Maybe the name you gave it is too long, too short, or is misleading to people who have to guess which table to open when they’re looking for something in particular.
To edit the name of a table, follow these steps:
Open the database that contains the table you want to rename.
With the chosen database listed on the left side of your Access workspace, look for the table you wish to rename.
Don’t open the table itself. It can’t be open during the renaming process if you want those steps to work.
Choose Rename from the menu that appears, as shown in Figure 6-14.
The name is highlighted.
Change the table name.
You can make the change in one of two ways:
It happens to everyone. You add, move, or change the name of a field and regret it later — or you rename your table and wish you hadn’t. What can you do? Call upon Access’ techniques for avoiding the panic that ensues when a mistake is made and you think you’re stuck with the results.
Now, none of the following three methods for cleaning up after a regrettable change are magical or really high-tech, but they are helpful. Here goes: