Office 2016 For Dummies (2016)
Part VI
Storing Stuff in Access
Visit www.dummies.com/extras/office2016 for great Dummies content online.
In this part …
Designing a database
Editing a database
Searching and filtering a database
Sorting data
Creating reports
Visit www.dummies.com/extras/office2016 for great Dummies content online.
Chapter 16
Using a Database
In This Chapter
Understanding how databases work
Designing a database
Editing a database
Typing information into a database
Closing a database
A database is a program that stores information such as names, addresses, and phone numbers, or inventory part numbers, shipping dates, customer codes, and any other type of information that you think is worth saving.
To help you store information in a database, Office 2016 comes with the Access database program. Access provides two huge advantages over storing information on paper. First, Access can store literally billions of chunks of information (try doing that with a filing cabinet). Second, Access makes it easy to search and sort through your information in the blink of an eye.
The three main advantages of a computer database over a paper database are
· Massive storage: The largest computer database can fit on a hard drive, but a paper database may take a roomful of file cabinets.
· Fast retrieval: Searching for a single name in a computer database is fast and easy. Doing the same thing in a paper database is difficult, error prone, and nearly impossible with a large database.
· Reporting: A report can help you make sense out of your data, such as showing a list of customers who earn a certain amount of money and live in a specific area. Trying to find this information in a paper database is time-consuming and error-prone.
Understanding the Basics of a Database
A database is nothing more than a file that contains useful information that you need to save and retrieve in the future. A database can consist of a single name and address, or several million names and addresses.
A typical Access database file consists of several parts:
· Fields: A field contains a single chunk of information, such as name, street address, or phone number.
· Records: A record consists of one or more fields. A business card is a paper version of a database record that stores fields (name, address, phone number, and so on) about a single person (record).
· Tables: A table displays records in rows and columns, much like a spreadsheet. Tables group related records, such as records of all your customers or records of all your invoices.
· Forms: A form displays all the fields of a single record onscreen, mimicking a paper form, so that you can add, edit, or view a single record at a time.
· Queries: A query lets you retrieve certain information based on your criteria, such as only retrieving names and addresses of people who earn more than $50,000 a year and have children.
· Reports: A report arranges your data in a certain way, such as showing all customers who placed more than 1,000 orders last year or all customers who live within a certain zip code.
Access is known as a relational database. Basically, this means that you can store data in separate tables and link or “relate” them together to avoid duplicating data in multiple tables. One table may contain customer names and addresses while a separate, related table may contain those same customers’ purchase orders.
Here are the two basic steps to using a database. First, you need to design your database, which means deciding what type of information your database will hold, such as names, addresses, e-mail addresses, telephone numbers, and so on.
After you design a database, the second step is filling it with actual data, such as typing the name Bob Jones in the Name field or the e-mail address BJones@somecompany.com in the e-mail field.
When you first create a database, you’ll probably start out with a single table that contains customer information. Inside the Customer Information table will be multiple records where each record represents a single customer. Each record will consist of multiple fields, such as Last Name, Company Name, Phone Number, and E-mail Address.
To help you edit and view your database table information, you may eventually want to create a form that displays your fields onscreen, mimicking a paper form that’s easy to read.
If you find yourself searching for the same type of information on a regular basis, such as looking for the names of your best customers (say, those who order more than $1,000 worth of products from you a week), you can store this search criteria as a query. Then you can just click the query name and make Access show you exactly what you want to find.
Finally, you may want to print your data in a way that makes sense to you, such as printing a quarterly sales report. By saving your printing criteria in a report, you can make Access print selected data on a page that’s easy for you to read and understand.
Features such as forms, queries, and reports are optional but handy. Features like tables, records, and fields are necessary to store your information in your database.
Designing a Database
To design a database, you need to first create a database table and then define the names of all the fields you want to store in that particular table. Database tables let you divide a file into separate parts. For example, one database table may hold the names and addresses of all your customers, a second database table may hold the names and addresses of all your employees, and a third database table may hold the names and addresses of your suppliers. Access stores all this related information in a single Access file that’s saved on your hard drive, as illustrated in Figure 16-1.
Figure 16-1: You can divide an Access file into separate tables that contain different fields.
To design your database, you can create a database from scratch or use an existing template, which you can modify. Designing a database means defining both the number of fields to use for storing information and the maximum amount of data each field can hold.
If you have a field that stores numbers, what are the maximum and minimum limits on the numbers you want to save in that field? If you’re storing someone’s age, you probably don’t want the field to contain negative numbers or numbers beyond 200. If your field needs to hold salaries, the field may need to hold large numbers but no negative numbers.
In general, store information in separate fields. So rather than create a single field to hold someone’s full name, create two separate fields: One field holds a first name, and the second field holds the last name. By storing last names in a separate field, you can easily yank last names out of your database to create form letters that state, “The Smith family has just won $200,000 dollars in the Publisher’s Umpteenth Sales Pitch Sweepstakes!”
Access can create a blank database or a special database by using one of many templates available from the Microsoft website. No matter how you create a database, you will likely need to modify it to customize it for the type of data you want to store.
When you start Access, it gives you a choice of opening an existing database or creating a new one.
To create a database with Access already running, follow these steps:
1. Click the File tab.
2. Choose New.
Access displays a variety of options, as shown in Figure 16-2.
3. Click an icon such as Blank desktop database or any database template.
If you click a template, a window appears; you see a preview of your template.
4. Click in either the File Name (for a blank database) or in the App Name (for a database template) text box and type a descriptive name for your database.
If you click the folder icon that appears to the right of the File Name text box, you can open a dialog box that will let you define a specific drive and folder in which to store your database file.
5. Click the Create button to create your database file.
Access displays a blank database as shown in Figure 16-3.
6. Click on the Click to Add heading.
Access displays a menu for defining what type of data to hold, such as text or numbers, as shown in Figure 16-4.
7. Choose Long Text.
Access displays a generic field name, such as Field1.
8. Type a name for your field (such as First Name or Salary).
9. Press Enter.
Access displays another Click to Add column; its menu lets you choose the type of data to store in the next field.
10. Choose Long Text.
Access displays another generic field, such as Field1.
11. Type a name for your field, such as Last Name.
12. Repeat Steps 6 through 9 for each additional field you want to create.
Figure 16-2: Creating a new database.
Figure 16-3: A new database lets you start defining field names.
Figure 16-4: The Click To Add column heading displays a menu of different data types.
Editing and Modifying a Database
After you create a database from scratch or from a template, you may need to modify it by giving each field a descriptive name, defining the size of each field, or adding and deleting a field.
Naming a field
If you create a database from scratch, Access displays generic field names such as Field1. If you didn’t modify these generic field names at the time you created your database, you can change names later. If you create a database from a template, you’ll see the descriptive field names, but you may still want to rename the fields to something else.
To rename a field, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click the table that contains the fields you want to rename.
Access displays the Datasheet view (which resembles an Excel spreadsheet) of your database.
2. Double-click the field (column head) that you want to rename.
Access highlights the column heading.
3. Type a new name for your field and then press Enter when you’re done.
Adding and deleting a field
Sometimes you may need to add a field to make room to store new information. At other times, you may want to delete a field that you don’t really want after all. To add a field to a database table, follow these steps:
1. Click the downward-pointing arrow that appears to the right of the Click to Add heading.
A menu appears (see Figure 16-4).
2. Choose the type of field you want to add, such as Currency or Short Text.
Access inserts your field and gives it a generic name like Field3.
To delete a field from a database table, follow these steps:
1. Right-click the field (column head) you want to delete.
Access highlights the entire column in your database table and displays a popup menu, as shown in Figure 16-5.
2. Click DeleteField.
If you have stored data in that field, a dialog box appears, asking whether you want to permanently delete all the data in the field.
If you delete a field, you also delete any data that may be stored in that field. Depending on how much data you have stored, you can wipe out a lot of information by deleting a single field, so be careful.
3. Click Yes (or No).
If you click Yes, Access deletes your chosen field.
Figure 16-5: Right-clicking a field lets you modify it.
Defining the type and size of a field
The data type of a field defines what kind of data the field can hold (numbers, text, dates, and so on), and the size of a field defines the amount of data the field can hold (no numbers larger than 250 digits, any text string with fewer than 120 characters, and so on).
The purpose of defining the type and size of a field is to make sure that you store only valid data in a particular field. If a field is meant to store names, you don’t want someone typing in a number. If a field is meant to store a person’s age, you don’t want the field to accept negative numbers.
To define the type and amount of data a field can store, follow these steps:
1. Click the Home tab.
2. In the All Access Objects pane on the left of the screen, double-click the table that contains the fields you want to define.
Access displays the Datasheet view of your table.
3. Click the column heading of the field you want to define.
Access highlights the entire column.
4. Click the Fields tab that appears under the Table Tools heading on the Ribbon interface.
The Fields tab displays options for modifying your field.
5. Click the arrow that appears to the right of the Data Type combo box.
A pull-down menu appears, listing all the different types of data you can define to store in a field, as shown in Figure 16-6.
6. Choose a data type, such as Number, Text, or Date/Time.
Depending on the data type you choose, you can choose additional options by clicking in the Format combo box.
Figure 16-6: You can choose a specific data type to make sure that a field stores the correct information.
If you change a field’s data type, you may lose any existing data stored in that field.
Typing Data into a Database
After you create a database table and define fields within that table to hold chunks of information, you’re ready to type in actual names, phone numbers, and e-mail addresses into each field. Access gives you two ways to enter data:
· Through Datasheet view
· Through Form view
Datasheet view displays information in rows and columns, where each row represents a single record and each column defines a specific field name. Datasheet view can be especially handy for examining multiple records at once.
Form view displays all the fields of a single record onscreen. Form view is most useful when you just need to view or edit a single record, as when you’re typing in the phone number of your cousin or boss.
Using Datasheet view
Datasheet view displays all your database information in rows and columns, much like a spreadsheet. To view and enter data in Datasheet view, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click a table name.
If Access displays the Datasheet view of your table right away, you can stop here, Otherwise, continue with the following steps.
2. Click the bottom half of the View icon (that displays a downward-pointing arrow), which appears in the Views group.
A pull-down menu appears.
3. Click Datasheet View.
Access displays the Datasheet view of your database table.
4. Click in a field defined by the column and row.
Each column defines a field, such as a name or address. Each row represents a single record.
If you click in a field that already contains data, you can edit or delete that data.
5. Press Tab to select the next field (or Shift+Tab to select the preceding field).
6. Type or edit the data in the field.
Using Form view
The biggest problem with Datasheet view is that it can be confusing to find a field for a specific record. Because most people are familiar with paper forms or index cards that arrange related data (fields) on a page, Access offers you Form view.
Form view simply displays the fields of a single record onscreen, arranged like the information typically stored on a business card. To use Form view, you must first create a form and arrange your fields on that form. After you create a form, you can add, edit, and view data through that form.
Creating a form
The simplest way to create a form is to let Access design one for you, which you can modify. To create a form quickly, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click a table.
Access displays the Datasheet view of your database.
2. Click the Create tab.
3. Click the Form icon in the Forms group.
Access creates a form, as shown in Figure 16-7. Notice that the form name automatically uses the name of the database table you chose in Step 1.
4. Click the Save icon on the Quick Access toolbar (or press Ctrl+S).
The Save As dialog box appears, asking you to type a name for your form.
5. Type a descriptive name for your form in the Form Name text box and then click OK.
Access displays your form’s name underneath the All Tables pane. The next time you open this database and want to view the form, you can double-click the form’s name in the left pane.
Figure 16-7: Form view displays multiple fields of a single record.
Viewing and editing data in a form
After you create a form, you can use it to edit and add data at any time. To view a form, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click the name of the form you want to use.
Access displays the Form view of your database.
2. Click one of the following icons (they appear near the bottom of the screen) to display a record:
· First Record: Displays the first record stored in your file
· Previous Record: Displays the preceding record in the file
· Next Record: Displays the next record in the file
· Last Record: Displays the last record that contains data
· New (Blank) Record: Displays a blank form so you can type in data that will create a new record in your file
3. Click in a field and type the information you want to store, such as a name or phone number.
You don’t need to use the Save command to save your changes because Access automatically saves any data you add or edit in your file as soon as you type or edit the data and move the cursor to a new field or record. The Save command is used to save the design of the form.
Editing a form
A form can be a convenient way to view all the fields of a single record. However, you can always rearrange the position of certain fields onscreen (to make them easier to find), or you can delete one or more fields altogether. This can be handy to create a form that shows only a filtered view of your data, such as a form that shows you only employee names, phone numbers, and e-mail addresses instead of also showing you their salaries and employee ID numbers at the same time.
Deleting a field
If you delete a field from a form, you simply prevent the form from displaying any data stored in that field. For example, if you don’t want to see each person’s hire date, you can delete the Hire Date field from your form.
Deleting a field on a form doesn’t erase any data; it just keeps you from seeing that data on a particular form.
To delete a field from a form, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click the name of the form you want to use.
Access displays the Form view of your database.
2. Click the Home tab.
3. Click the downward-pointing arrow underneath the View icon in the Views group.
A pull-down menu appears.
4. Choose Design View.
Access shows your chosen form in Design view, which displays a background grid to help you align fields on your form (as shown in Figure 16-8).
5. Hold down the Ctrl key and click the field label and field that you want to delete.
Access highlights your chosen field and label.
Each field consists of a field label and the actual field itself. The field label contains descriptive text that defines the data, such as Name, Age, or Sales. The field is the text box that holds the actual data, such as “Fred,” 47, or $34.08.
6. Click the Home tab, and then click the Delete icon in the Records group.
Access deletes your chosen field.
If you press Ctrl+Z right away, you can undelete any field that you just deleted.
7. Click the downward-pointing arrow underneath the View icon in the Views group.
A pull-down menu appears.
8. Choose Form View.
Access shows your form with the deleted field missing.
Figure 16-8: Design view displays a grid to help you align fields on a form.
Adding a field
Before you can add a field to a form, you must make sure that the field already exists in your database table. For example, if you want to add a field on a form that displays phone numbers, you must first create that field in your database table and then stuff it with actual data.
To add a field to a form, follow these steps:
1. In the All Access Objects pane on the left of the screen, double-click the name of the form you want to use.
Access displays the Form view of your database.
2. Click the Home tab.
3. Click the downward-pointing arrow underneath the View icon in the Views group.
A pull-down menu appears.
4. Choose Design View.
Access displays your form in Design view.
5. Click the Design tab.
6. Click the Add Existing Fields icon in the Tools group.
The Field List pane appears on the right side of the screen, as shown in Figure 16-9.
7. Double-click a field.
Access displays the field label and a field on your form.
8. Move the mouse pointer over the edge of the field and drag the mouse (hold down the left mouse button and move the mouse) to move the field and field label on your form.
9. Click the downward-pointing arrow underneath the View icon in the Views group and choose Form View.
Access displays the Form view. Notice that the form displays both your newly added field and any data stored in that field.
Figure 16-9: The Field List pane shows you all the fields stored in your database table that you can place on the form.
Closing and Saving a Database
When you’re done using a database file, you can either close it or exit Access altogether. Access gives you two options for closing a database:
· Close a database.
· Close and exit from Access.
Closing a database
Closing a single database table simply removes the data from view. After you close a database table, you can open another one.
You don’t have to close a database table to open another one, but if you know you won’t need to view a particular database table, you may as well close it to get it out of the way and free up memory in your computer.
To close a database table, follow these steps:
1. Click the File tab.
2. Choose Close.
If you haven’t saved any changes you made to your form or datasheet, Access displays a dialog box, asking whether you want to save your changes.
3. Click Yes or No.
Access closes your chosen database table.
When you save an Access database, you’re saving only the changes you made to the database table or form. Access automatically saves any data you type or edit in your database file. (When you type or edit data, Access saves it as soon as you move the cursor to a new field or record.)
Exiting Access
When you’re done using Access, you can close the program altogether and shut down any databases you may have open. To exit Access and close any open databases, follow these steps:
1. Choose one of the following.
· Press Alt+F4.
· Click the Close icon that appears in the upper-right corner of the Access window.
If you made any changes to your database structure such as moving the location of a field on a form or adding (or deleting) a field, a dialog box appears, asking whether you want to save your changes.
2. Click Yes or No.