Microsoft Access homework

Confirming Pages

T6-2 * Plug-In T6 Basic Skills and Tools Using Access 2007

P L U G - I N

T6 Basic Skills and Tools Using Access 2007 1. Describe the primary functions using Microsoft Access. 2. Describe the steps for creating a new database file using Access. 3. Describe the steps for creating and modifying a table and fields using Access. 4. Describe the steps for creating relationships between tables using Access.

Introduction to Access Microsoft Access 2007 is a powerful database program that allows you to enter and organize large amounts of data. Because Access allows you to relate tables and databases to one another, it is often referred to as a relational database. Plug-In T5, “Designing Database Applications,” explains relational databases and their struc- tures in detail.

This plug-in introduces the basics of creating a database using Microsoft Access. It is designed to show you the essentials, along with a few added-value features, to get you off to a good start using the program. Additional material, animated tutori- als, and simulated practice files that go beyond what we cover in the text can be found at https://misource.simnetonline.com . Figure T6.1 displays many of the tasks and lessons that are provided from this Web site.

In brief, a relational database is a group of tables related to one another by com- mon fields. A table (or datasheet) looks similar to a spreadsheet. Each row in the table contains all the data for a single record. Each column in the table represents a specific data value called a field. All records in a relational database have the same fields. For example, a table called EMPLOYEE might include fields for Employee ID, Last Name, First Name, Address, City, State, and Zip. Another table called TIME SHEET might have fields for Time Sheet Number, Employee ID, Week, Hours Worked, and Rate of Pay. The two tables are related by the Employee ID field, so the database can generate reports combining information from the two tables. Figure T6.2 dis- plays this relationship.

Access comes with templates for common database categories, including project planning, employee time sheets, expense reporting, and inventory management.

LEARNING OUTCOMES

bal76744_plugint06_002-018.indd T6-2bal76744_plugint06_002-018.indd T6-2 2/22/08 11:27:03 AM2/22/08 11:27:03 AM

Confirming Pages

*

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-3

MISource CD Microsoft Access Lessons

Introduction to Access 2007 Creating and Modifying Queries

■ Introduction to Access 2007 ■ Using the Simple Query Wizard

■ Opening an Existing Database ■ Creating a Query in Design View

■ Creating a New Blank Database ■ Adding Criteria to a Query

■ Creating a New Database from a Template ■ Adding a Calculated Field to a Query

■ Creating Relationships ■ Using Action Queries

■ Modifying Relationships

■ Entering Data in a Table

■ Navigating Records in a Table

■ Entering Data in a Form

■ Navigating Records in a Form

■ Adding Attachments to Records

Creating and Modifying Tables Working with Reports

■ Designing a Table ■ Creating a Simple Report

■ Creating a New Table ■ Using the Report Wizard

■ Creating a Table from a Template ■ Adding Controls to a Report

■ Creating a Table in Design View ■ Grouping Records in a Report

■ Setting the Primary Key ■ Controlling the Page Setup of a Report

■ Defining Table Fields for Printing

■ Changing Data Types

■ Adding Fields to Tables

■ Adding a Total Row

■ Using Attachment Fields

■ Modifying Field Properties

■ Modifying Tables

■ Using the Table Analyzer

Creating and Modifying Forms Organizing Information

■ Creating a Form Based on a Table or Query ■ Viewing Dependencies

■ Creating a Split Form ■ Using Text Filters

■ Creating a Datasheet Form ■ Using Date and Numerical Filters

■ Changing the Look of a Form with AutoFormat ■ Sorting Records

■ Working in Form Design View ■ Searching for Information

■ Adding Controls to Forms

■ Arranging Controls

■ Defining the Tab Order of Controls

■ Creating Subforms

Importing and Exporting Data

■ Importing Data from Excel

■ Exporting Data

FIGURE T6.1

MISource Access Lessons

bal76744_plugint06_002-018.indd T6-3bal76744_plugint06_002-018.indd T6-3 2/22/08 11:27:04 AM2/22/08 11:27:04 AM

Confirming Pages

T6-4 * Plug-In T6 Basic Skills and Tools Using Access 2007

An Access database includes more than just data. In addition to tables, an Access database file contains several different types of database objects:

■ Saved queries for retrieving and organizing data.

■ Forms for entering and displaying data on screen.

■ Reports for printing table data or the results of queries.

This plug-in focuses on creating an Access data- base file, in addition to building tables, fields, attri- butes, and relationships. Plug-In T7, “Problem Solving Using Access 2007,” concentrates on building queries, and Plug-In T8, “Decision Making Using Access 2007,” spotlights forms and reports.

CREATING A NEW BLANK DATABASE

When creating a new blank database, the first thing you do is name your database. In Access, the database file cannot be moved to another disk or folder using the Save As command, so be sure to save the database to the preferred location when you start. (You can always move the database later using Windows Explorer.)

To create a blank database, follow these steps:

1. Open Access, click the Office button and then click New. 2. On the Getting Started with Microsoft Office Access page, under New Blank

Database, click Blank Database (illustrated in Figure T6.3 ).

FIGURE T6.2

Database Relationship Example

Click here to open a new blank database.

Enter a database name and click Create.

FIGURE T6.3

Create a New Blank Database

bal76744_plugint06_002-018.indd T6-4bal76744_plugint06_002-018.indd T6-4 2/22/08 11:27:05 AM2/22/08 11:27:05 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-5

3. In the Blank Database pane, type Slopeside Bikes for the database file name in the File Name: box.

4. Click Create. Access will save the new blank database in the specified database file (which will have the .accdb extension).

5. Access creates the database with an empty table named Table1, and then opens Table1 in Datasheet view. The cursor is placed in the first empty cell in the Add New Field column.

OPENING AN EXISTING DATABASE

When you start Access, the task pane to the right lists the most recently opened databases. To open a database that is not listed in the task pane, use the Open com- mand. To open a database (refer to Figure T6.4 ):

1. Click the Office button, and then click Open. 2. In the Open dialog box, browse to the database that you want to open. 3. Double-click the name of the database to open it, or click the name of the data-

base once to highlight it, then click the Open button.

USING THE DATABASE TEMPLATES

Access includes a variety of templates to help you get started with some of the most common types of databases.

Templates include databases to manage contacts, expenses, and inventory. Here is an overview on how to create a new database using a template (refer to Figure T6.5 ):

1. Click the Office button and then click New. 2. In the left pane, click Local Templates under Template Categories. 3. Several featured templates are displayed in the middle pane. Click the Contacts

template, accept the default file name Contacts1.accdb and click Create.

4. Access creates the database and then opens it. A form is displayed in which you can begin entering data.

Double-click file name to open . . .

. . . or select the file name and click the Open button.

FIGURE T6.4

Open an Existing Database File

bal76744_plugint06_002-018.indd T6-5bal76744_plugint06_002-018.indd T6-5 2/22/08 11:27:08 AM2/22/08 11:27:08 AM

Confirming Pages

T6-6 * Plug-In T6 Basic Skills and Tools Using Access 2007

USING THE DATABASE WINDOW AND OBJECT VIEWS

Whenever a database is open, Access displays the Database window (shown in Figure T6.6 ), which serves as the central location for working with the database objects (tables, queries, forms, reports, etc.) in the opened database.

The following are among the important ways to work with database objects using the Database window:

■ To work with a particular type of database object, click the Shutter Bar Open/ Close button (<<) and select the object you want to work with—Tables, Queries, Forms, Reports, Pages, and so on.

■ To view a database object, select the object name under the Navigation Pane and double-click the object to open it.

■ To change the design of a database object, select the object name under the Navigation Pane, right-click and choose the Design View button.

■ To create a new database object of the type currently displayed in the Database window, click the Create tab and then select the button type to create the new object (Table, Form, Report, Query Wizard, or Data Access Page).

■ To make a copy of a database object, right-click the object and choose Copy from the shortcut menu. Then right-click a blank spot in the Database window and choose Paste from the shortcut menu.

■ To rename a database object, right-click the object and choose Rename from the shortcut menu.

■ To delete a database object, right-click the object and choose Delete from the shortcut menu.

■ To close the current database, together with the Database window, click the Office button, then click the Close Database button.

Different templates located on your computer.

A brief description of the template selected.

FIGURE T6.5

Create a New Blank Database Using a Template

bal76744_plugint06_002-018.indd T6-6bal76744_plugint06_002-018.indd T6-6 2/22/08 11:27:09 AM2/22/08 11:27:09 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-7

USING TABLE VIEWS

You can open database objects in different views, depending on what you want to do. Datasheet view is the view to use when entering data (see Figure T6.7 ). Use

Quick Access Toolbar

Objects

Ribbon

Navigation Pane

Status Bar

Shutter Bar Open/Close

Button

FIGURE T6.6

Access Database Window

FIGURE T6.7

Datasheet View

View button

Datasheet view

bal76744_plugint06_002-018.indd T6-7bal76744_plugint06_002-018.indd T6-7 2/22/08 11:27:09 AM2/22/08 11:27:09 AM

Confirming Pages

T6-8 * Plug-In T6 Basic Skills and Tools Using Access 2007

Design view when you want to change the structure or properties of the table. To open a table in Datasheet view from the Database window:

1. Under the Navigation Pane, click the Shutter Bar Open/Close button and make sure that all of the All Access Objects are selected.

2. To automatically open a table in Datasheet View, double-click the name of the table under the Tables section of the Navigation Pane. This is the Datasheet view where you can enter data in the table.

3. To change to Design view, click the View button in the Views group on the tool- bar (refer to Figure T6.7 ). In Design view, you can add or remove fields or change field properties.

4. To switch back to Datasheet view, click the View button again.

Creating and Modifying Tables Access gives you two different ways to create tables:

■ Create a table using data entry.

■ Create a table in Design view.

CREATING A TABLE USING DATA ENTRY

Sometimes you need to create a very simple table. In this case, the data entry method might be easiest. A new table created with the data entry method appears as a plain datasheet. Fields are named Field1, Field2, Field3, etc., until you rename them. To create a table using the data entry method (refer to Figure T6.8 ):

1. Click the Create tab and then click the Table button. 2. The new table appears and is ready for data entry. 3. Rename the fields that you are going to use by double-clicking the field name

(Field1, Field2, etc.) and typing the new field name (First Name, Last Name, etc.).

Field names are labeled Field1, Field2, etc., until you rename them.

FIGURE T6.8

Create a Table by Entering Data

bal76744_plugint06_002-018.indd T6-8bal76744_plugint06_002-018.indd T6-8 2/22/08 11:27:10 AM2/22/08 11:27:10 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-9

CREATING A TABLE IN DESIGN VIEW

Most Access objects are displayed in Design view, which allows you to work with the underlying structure of your tables, queries, forms, and reports. In this next step, you will be using the Slopeside Bikes database that you created in the “Creating a New Blank Database” section to use for many of the remaining steps in this plug-in. To create a table using Design view, follow these steps:

1. Click the Create tab, and then click the Table Design button. 2. Click in the Field Name cell and type Bike ID. 3. Click in the Data Type cell, click the list arrow and select Number. To make the

Bike ID field a primary key, click on the Primary Key button in the Tools group on the toolbar. In the Field Properties pane (below the table), make sure the Required property is set to Yes and the Field Size is set to Long Integer.

4. Using the same steps above, create fields for the following (see Figure T6.9 ): a. Field Name = Description, Data Type = Text, Field Size = 25, Required = Yes

b. Field Name = Cost Per Hour, Data Type = Currency, Required = Yes

Note: Designate a primary key. The primary key consists of one or more fields that Access can use to uniquely identify the records contained within the table. A table must have a primary key if it is on the “one” side of a one-to-many relation- ship, as explained in Plug-In T5.

When a single field is designated as the primary key, the field’s Indexed property is automatically set to Yes (No Duplicates). This setting cannot be changed. When data in a record are entered or modified, Access will not allow a primary key field to be left blank.

To designate a field, or a group of fields, as the primary key, select the field or fields in the field list and click the Primary Key button. Access will mark the pri- mary key field(s) with a key icon, as shown in Figure T6.10 . To remove the primary key designation from a field, select it and click the Primary Key button again.

5. Close the table, and click Yes to the save changes prompt. Enter BIKE as the Table Name.

6. Create another new, blank table as before. Enter fields for each entry in Fig- ure T6.11 , using field sizes and descriptions as appropriate. Remember to set the primary key and ensure that the Required property is set to Yes as appropriate.

FIGURE T6.9

Bike Table

bal76744_plugint06_002-018.indd T6-9bal76744_plugint06_002-018.indd T6-9 2/22/08 11:27:12 AM2/22/08 11:27:12 AM

Confirming Pages

T6-10 * Plug-In T6 Basic Skills and Tools Using Access 2007

7. Close the table, and click Yes to the save changes prompt. Enter CUSTOMER as the Table Name.

8. Create another new, blank table as before. Enter fields for each entry in Fig- ure T6.12 , using field sizes and descriptions as appropriate. Since there are three primary keys in this scenario (each acts as a composite key comprised as one pri- mary key), make sure all three (Drivers License, Date, and Bike ID) are selected by holding down the CTRL key when clicking each field, then select the Primary Key button.

9. Close the table, and click Yes to the save changes prompt. Enter RENTAL for the Table Name.

FIGURE T6.10

Setting the Primary Key

Indicates that this is a primary key field.

Select the field that will be the primary key.Primary Key button

FIGURE T6.11

Customer Table

bal76744_plugint06_002-018.indd T6-10bal76744_plugint06_002-018.indd T6-10 2/22/08 11:27:13 AM2/22/08 11:27:13 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-11

CHANGING DATA TYPES

By default, the data type for a new field is Text. You can change the data type to Number, AutoNumber, Date/Time, Currency, or one of the other options avail- able. Specifying the appropriate data type for a field is crucial to designing a useful database. For example, you cannot run calculations on a field with the Text data type, and you cannot sort a date field efficiently unless you use the Date/Time data type.

Carefully consider the type of data you will include in each field before you decide on the data type. To change the data type for a field:

1. In the Navigation Pane, open the table you wish to modify in Design view.

2. Click the existing data type for the field that you want to change.

3. Click the drop-down arrow to see the list of other available data types.

4. Select the appropriate data type for your data (see Figure T6.13 ).

USING THE INPUT MASK WIZARD

To ensure that users enter data in a particular format, use the Input Mask prop- erty. Rather than typing the mask format yourself, Access has a wizard that offers samples of the most common data formats. To use the Input Mask Wizard (see Figure T6.14 ):

1. Open the CUSTOMER table in Design view that you created in a previous section.

2. Click the Telephone field.

3. Click the Input Mask box in the Field Properties pane.

4. Click the Build button (the . . . ellipsis button) to the right of the box.

5. If you need to save the table, Access will prompt you to do so now.

6. The Input Mask Wizard appears. Select Phone Number under the Input Mask: column.

7. Click the Next button to continue.

FIGURE T6.12

Rental Table

All three fields act as a primary key field.

bal76744_plugint06_002-018.indd T6-11bal76744_plugint06_002-018.indd T6-11 2/22/08 11:27:14 AM2/22/08 11:27:14 AM

Confirming Pages

T6-12 * Plug-In T6 Basic Skills and Tools Using Access 2007

8. In this step, you can modify the input mask or change the placeholder character. To change the Placeholder character: click the drop-down arrow and select the char- acter you want. You can test the new format by typing values in the Try It: box.

9. Finally, choose how you want to store the data. Click the With the symbols in the mask, like this: radio button.

FIGURE T6.14

Input Mask Wizard

Input mask property

Sample input masks

FIGURE T6.13

Access 2007 Data Types Access 2007 Data Types

Text Any field that does not fit criteria for one of the other data types. This includes not only text, but also formatted numbers like ZIP codes and phone numbers.

Memo Text descriptions longer than 255 characters.

Number Quantities; values that will have mathematical calculations performed on them.

Date/Time Dates and times. There are a variety of date and time formats to choose from. Although you can enter dates and times as Text data types, you must use the Date/Time data type if you want to sort the values.

Currency Money amounts. Choose from a variety of currency formats. You can perform mathematical calculations on currency values.

AutoNumber Primary keys or other ID fields.

Yes/No Fields to which there is only a yes or no response.

OLE Object Pictures or other graphics. Links to other files such as Word or Excel documents.

Hyperlink E-mail or Web site addresses.

Lookup Wizard... Limits the data in the field to values in a list (either from another table or from a list that you create).

bal76744_plugint06_002-018.indd T6-12bal76744_plugint06_002-018.indd T6-12 2/22/08 11:27:17 AM2/22/08 11:27:17 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-13

10. Click Next to go to the last step.

11. Click Finish to complete the input mask.

USING THE FORMAT PROPERTY

Use the format property to ensure that data are entered in a consistent format. You can choose from predefined formats or design your own. To select a predefined format or enter a custom text format (see Figure T6.15 ):

1. Open the BIKE table in Design view that you created in a previous section. 2. Click the Cost Per Hour field. 3. Click the drop-down arrow under Format in the Field Properties pane to dis-

play the list of predefined formats. Refer back to Figure T6.13 for a list of the data types.

4. Select the Currency format. 5. Close the BIKE table window and click Yes at the save changes prompt.

Note: You can include Autotext in your custom text formats. For example, if you are entering apartment numbers, you could use the format “Apt. ” @-@. Entering the value B1 will display Apt. B-1. Placing quotation marks around “Apt. ” tells Access that this is a text string that should be included automatically in each data entry. Notice the space inside the quotation marks to ensure that a space will dis- play between “Apt.” and the apartment number. Using this text format will save data entry time. Figure T6.16 displays the different data formats used in Access.

Defining Relationships Remember that Access is a relational database. Objects in your database are related to one another through relationships defined by common fields between tables. There are three types of relationships: one-to-many, one-to-one, and many-to-many.

Change field properties.

Select the field you want to modify.

FIGURE T6.15

Format Property

bal76744_plugint06_002-018.indd T6-13bal76744_plugint06_002-018.indd T6-13 2/22/08 11:27:17 AM2/22/08 11:27:17 AM

Confirming Pages

T6-14 * Plug-In T6 Basic Skills and Tools Using Access 2007

One-to-many relationships are the most common. In a one-to-many relation- ship, the primary table contains a primary key field that is included as a field (as a foreign key) in the secondary table. Thus, one record in the first table can relate to many records in the second table.

When these fields have the same names, Access automatically creates the one-to- many relationship for you. However, the fields may have different names. In those cases, you may want to manually create the relationship using the Relationships window.

USING THE RELATIONSHIPS WINDOW

To define relationships between tables follow these steps:

1. Open the Relationships window by clicking on the Database Tools tab, then click- ing the Relationships button in the Show/Hide group (refer to Figure T6.17 ).

2. The Show Table dialog box appears. Select each table listed (i.e., BIKE, CUSTO- MER, and RENTAL ) and click the Add button, then click the Close button.

FIGURE T6.16

Access 2007 Data Formats Text Formats

� Forces text to lowercase. Type: Leash, Extra Long Display: leash, extra long

� Forces text to uppercase. Type: ca Display: CA

@ Requires character entry or Example: Use for a text field that space. must always have the same

number of characters (like a Social Security Number: @@@-@@-@@@@).

@;none Controls what value is shown. Displays value entered; if no value entered, displays “none.”

Number/Currency Formats

General Number Displays the number as entered. Example: 24000

Currency Uses the currency format specified in your Windows Regional Settings, including the comma thousands separator and currency symbol. Example: $24,000.00

Euro Uses the standard currency format with the euro symbol. Example: €24,000.00

Fixed Uses the number format specified in your Windows Regional Settings. Displays at least one digit. Example: 24000.00

Standard Uses the number format specified in your Windows Regional Settings, including the comma thousands separator. Example: 24,000.00

Percent Multiplies the value by 100 and adds % symbol. Enter: .03 Displays: 3%

Scientific Converts the number to scientific notation. Example: 24E+3

bal76744_plugint06_002-018.indd T6-14bal76744_plugint06_002-018.indd T6-14 2/22/08 11:27:18 AM2/22/08 11:27:18 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-15

3. The Relationships diagram should appear. To define a new relationship, click and drag the Bike ID pri- mary key from the BIKE table and drop it on the Bike ID foreign key in the RENTAL table. Make sure the table names and field names being linked are the cor- rect ones. The Edit Relationships dialog box should appear (see Figure T6.18 ). Select Enforce Referential Integrity. Then click the Create button. Note that related fields do not need to have the same name, only the same data type (although it is best to give the same name to clarify the relationship).

a. To change the features of a relationship, double- click the relationship’s line in the Relationships window.

b. To delete a relationship, click the line to select it and then press the Delete key.

4. Complete the diagram with the relationships shown in Figure T6.19 .

5. Close the Relationships window, and save the layout.

Notice the symbols on the relationship lines. The “1” indicates the “one” table in the one-to-many relationship. The infinity symbol “�” indicates the “many” table. When these symbols appear, you know that the relationship has referential integ- rity enforced.

Relationships button

Database Tools tab

FIGURE T6.17

The Relationships Button

One-to-many relationship

Table that has the primary key

Table that has the foreign key

Enforce Referential Integrity

FIGURE T6.18

Edit Relationships Dialog Box

Primary key must be unique to each record.

One-to-many relationshipForeign key

FIGURE T6.19

Completed Relationships Window

bal76744_plugint06_002-018.indd T6-15bal76744_plugint06_002-018.indd T6-15 2/22/08 11:27:18 AM2/22/08 11:27:18 AM

Confirming Pages

T6-16 * Plug-In T6 Basic Skills and Tools Using Access 2007

P L U G - I N S U M M A R Y * Most organizations maintain and manage large amounts of information. One of the most efficient information management computer-based applications is Microsoft Access 2007. Access provides a powerful set of tools for creating and maintaining a relational database. A few of the basic modules that most users utilize when working with Access are building tables and relationships.

Customer Table

Customer Last First ZIP First ID Name Name Street City State Code Phone Pickup

1 Wagoner Sam 5480 Alpine Boulder CO 80308 (303) 161- 05/25/2004 Street 5545

2 Calahan Eliza 2140 Edgewood Boulder CO 80308 (303) 886- 05/25/2004 Avenue 6003

3 Lake James 701 Eastman Boulder CO 80308 (303) 562- 08/25/2005 Road 4499

4 Meadows Sara Pond Hill Drive Boulder CO 80308 (303) 792- 02/28/2004 3646

20 Smith Alto 114 Lexington Boulder CO 80308 (303) 838- 06/02/2004 Street 7111

64 Monarch Shiela 431 Phillips Lane Boulder CO 80308 (303) 352- 07/17/2005 4847

65 Guo Amy 1935 Snow Boulder CO 80308 (303) 555- 05/19/2005 Avenue 6731

80 Rivera Juan 482 Weston Boulder CO 80308 (303) 815- 12/28/2004 Avenue 2456

85 Williams Max 230 Southpark Boulder CO 80308 (303) 333- 07/19/2003 Circle 0000

M A K I N G B U S I N E S S D E C I S I O N S* 1. WasteNot Recycling

WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Neigh- borhoods subscribe to the service so that pickup is cost-effective. WasteNot provides spe- cial containers to subscribers for sorting recyclables: a blue container for paper products and a purple container for aluminum, plastic, and glass products.

Subscribers place their recycling containers on the curb for biweekly pickup. Each recycling container is weighed before being emptied. WasteNot drivers carry handheld recording devices used to track each pickup. Subscribers receive quarterly profit-sharing checks based on their contributions. If WasteNot does not make a profit, subscribers are not paid for their recyclables. If WasteNot makes a profit, subscribers share in that profit. WasteNot has asked you to help de- velop a relational database that will effectively track subscribers, using the data downloaded from the drivers’ devices. WasteNot has provided you with a snapshot of two tables you need to create. The Customer table shown here holds static customer information such as name, address, and phone. The Customer Record table holds data about each recyclable pickup.

Specifically, WasteNot needs you to:

bal76744_plugint06_002-018.indd T6-16bal76744_plugint06_002-018.indd T6-16 2/22/08 11:27:19 AM2/22/08 11:27:19 AM

Confirming Pages

Plug-In T6 Basic Skills and Tools Using Access 2007 * T6-17

1. Create a Microsoft Access database. 2. Create the tables, fi elds, data types, and primary key(s) for the database. 3. Create the relationship(s) needed between the tables. 4. Populate the database with the data provided above.

2. It’s A Grind Coffee Shop It’s A Grind Coffee Shop is an Oakland, California, neighborhood coffee shop. Besides serving gourmet coffee, It’s A Grind dishes up sandwiches and desserts. Local bands, Internet con- nections, and floor-to-ceiling books on every wall provide entertainment. Kate Fitzgerald, the proprietor, has decided that a database would be helpful in the acquisition of new books. Although customers rarely buy books, they do disappear or fall apart from use. Kate needs a way to keep track of which books she has so that she does not pick up duplicates. She has hired you to help design a database to keep track of the books. To assist with the de- sign, Kate has provided you with some data in an Excel spreadsheet, T6_ItsAGrindCoffee_ Data.xls. The spreadsheet is not normalized; Kate asks you to assist with that before you start to create the database.

Specifically, Kate wants you to:

1. Create a Microsoft Access database. 2. Create the tables, fi elds, data types, and primary key(s) for the database using the

structure provided in the T6_ItsAGrindCoffee_Data.xls fi le. 3. Create the relationship(s) needed between the tables. 4. Populate the database with the data provided in the T6_ItsAGrindCoffee_Data.xls fi le.

3. Academic Software Launched in 2005 in Boston, Massachusetts, Academic Software has consistently been the fastest-growing education-focused software retailer in North America. It is committed exclu- sively to academic customers, offering thousands of full-version software titles at great dis- counts. Academic Software has partnered with the top technology manufacturers, including

Customer ID Srvc Date Weight Paper Weight Other

1 11/22/2007 8 15

1 10/15/2007 32 85

1 11/7/2007 12 43

2 11/7/2007 19 0

2 11/22/2007 28 174

3 10/15/2007 5 8

3 11/22/2007 16 32

3 12/4/2007 7 12

20 10/15/2007 18 40

20 11/22/2007 35 60

80 10/15/2007 10 10

80 11/7/2007 9 13

80 11/22/2007 16 18

80 12/4/2007 18 21

Customer Record Table

bal76744_plugint06_002-018.indd T6-17bal76744_plugint06_002-018.indd T6-17 2/22/08 11:27:20 AM2/22/08 11:27:20 AM

Confirming Pages

T6-18 * Plug-In T6 Basic Skills and Tools Using Access 2007

Adobe, Microsoft, Sibelius, Sony Media Software, and Wacom, to offer excellent service and prices, which are available only to students, schools, and teachers.

From the very beginning, Academic Software has relied heavily on technology to ensure a positive shopping experience for its customers. The company’s philosophy is simple: Hire amazing people, give them the best tools, and help them deliver an unbeatable customer experience.

One facet of Academic Software’s business that needs assistance is its database or- ganization. You have been asked to assist Academic Software with creating a relational database structure for organizing software, vendors, and academic categories. Currently this information is stored in an Excel spreadsheet, T6_AcademicSoftware_Data.xls, which Academic Software has provided to you.

Specifically, you are asked to:

1. Create a Microsoft Access database. 2. Create the tables, fi elds, data types, and primary key(s) for the database using the

structure provided in the T6_AcademicSoftware_Data.xls fi le. 3. Create the relationship(s) needed between the tables. 4. Populate the database with the data provided in the T6_AcademicSoftware_Data.xls

fi le.

4. On-Campus Health On-Campus Health is the infirmary located on the campus of the University of Denver. Re- cordkeeping at the infirmary’s pharmacy, although meticulous and professional, is inefficient. Maintaining the recordkeeping using mostly manual systems is becoming more costly as ad- ditional people are hired to meet stricter industry regulations regarding the Health Insurance Portability and Accountability Act (HIPAA) and because of state regulations that affect the sale, storage, and dispensing of prescription drugs. Although On-Campus Health succeeded in automating some of the data management for the pharmacy in an Excel spreadsheet, a more substantial change is needed to properly maintain and store data.

Students who use the infirmary can request prescriptions, either by presenting a written order from a doctor or asking for a refill of an existing prescription. The pharmacist adds this request to the system by getting the required information to fill it, including information about the drug, the student’s name, the student’s health plan, and the prescribing doctor. Use the data that On-Campus Health has provided you in the file T6_CampusHealth_Data. xls to complete the following:

1. Create a Microsoft Access database. 2. Create the tables, fi elds, data types, primary key(s), and the like for the database. 3. Create the relationship(s) needed between the tables. 4. Populate the database with the data provided in the Excel fi le.

bal76744_plugint06_002-018.indd T6-18bal76744_plugint06_002-018.indd T6-18 2/22/08 11:27:21 AM2/22/08 11:27:21 AM