haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 2 CONFIRMING PAGES P L U G - I N T6 Basic Skills and Tools Using Access LEARNING OUTCOMES 1. Describe the primary functions using Microsoft Access. 2. Describe the steps for creating a new database file using Microsoft Access. 3. Describe the steps for creating and modifying a table and fields using Microsoft Access. 4. Describe the steps for creating relationships between tables using Microsoft Access. Introduction to Access Microsoft Access 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 structures 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. However, you should review the CD, MISource, which accompanies this text for additional material, animated tutorials, and simulated practice files that go beyond what we cover in the text. Figure T6.1 displays all the tasks and lessons that are provided on the MISource CD. In brief, a relational database is a group of tables related to one another by common 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 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 Code. 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 displays this relationship. Access comes with templates for common database categories, including project planning, employee time sheets, expense reporting, and inventory management. T6-2 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 3 CONFIRMING PAGES FIGURE T6.1 MISource CD Microsoft Access Lessons Introduction to Access I Introduction to Access 2003 Defining Relationships I Using the relationships window I Opening an existing database I I Creating a new blank database I Using the database wizard I MISource Access Lessons Using table views using form views Enforcing referential integrity using report views I Using navigation controls in tables I Using navigation controls in forms I Changing datasheet column widths I Changing the look of a datasheet I Hiding and unhiding columns in a datasheet Creating and Modifying Tables Creating a table with the table design I Producing Reports I Using the report wizard wizard I Using autoreports I Creating a table using data entry I Modifying the report design I Creating a table in design view I I Setting the primary key I Changing data types I Adding controls to the report I Saving the table I Adding a calculated control to a report I Using the input mask wizard I Adding a lookup field to a table Changing margins and page orientation for reports section I Previewing and printing a report using the lookup wizard I Modifying field properties in design view I Using the format property Creating and Modifying Queries I Using the simple query wizard I Integrating with Other Applications I Importing objects from another Reordering columns in the select query * database datasheet I Importing data from Excel I Creating a select query in design view I Importing data from Outlook I Adding selection criteria to a select query I Exporting data from a table or query I Sorting data in a select query I Using the page wizard to create a data I Adding a calculated field to a query I Using the expression builder I Using aggregate functions to calculate Access page totals in queries I Using aggregate values in calculated fields I Formatting results displayed in a calculated field I Using the crosstab query wizard I Using the find duplicates query wizard I Using the find unmatched query wizard (Continued) Plug-In T6 Basic Skills and Tools Using Access * T6-3 haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 4 FIGURE T6.1 CONFIRMING PAGES MISource CD Microsoft Access Lessons (Continued) Creating and Modifying Forms I Creating a form using the form wizard Managing Databases I Identifying object dependencies I Creating and saving auto forms I Printing database objects and data I Modifying the properties of a form I Backing up a database I Modifying specific controls on a form I Using compact and repair I Aligning controls in a form I Adding form headers and footers I Adding page headers and footers to a form Viewing and Organizing Information I Entering records into a table I Entering records using a form I Editing records in a table I Deleting records in a table I Using find and replace I Using undo I Sorting records in a datasheet I Filtering by selection I Filtering by form An Access database includes more than just data. In addition to tables, an Access database file contains several different types of database objects: I I I 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 database file, in addition to building tables, fields, attributes and relationships. Plug-In T7, \"Problem Solving Using Access,\" concentrates on building queries, and Plug-In T8, \"Decision Making Using Access.\" spotlights forms and reports. FIGURE T6.2 Database Relationship Example CREATING A NEW BLANK DATABASE To start a new database using Microsoft Access, click the Blank Database option in the New File task pane. 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. If the New File task pane (illustrated in Figure T6.3) is not visible, show it by clicking the New toolbar button, selecting File, New, or pressing Ctrl + N. 2. Click the Blank Database command in the New area of the New File task pane. 3. Select a location to save the file and enter Slopeside Bikes for the database file name in the File New Database dialog box. T6-4 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 5 New button CONFIRMING PAGES FIGURE T6.3 New File task pane Create a New Blank Database Click here to open a new blank database. 4. Access will save the new, blank database in the specified database file (which will have the .mdb extension), and it will open the Database window within the main Access window. OPENING AN EXISTING DATABASE When you start Access, the task pane lists the most recently opened databases. To open a database that is not listed in the task pane, use the Open command. To open a database (refer to Figure T6.4): 1. Click the Open toolbar button. 2. If necessary, from the Look-in: drop-down list, select the drive or location where the database is stored. 3. If necessary, open the appropriate folder. 4. Double-click the name of the database to open it, or click the name of the database once to highlight it, then click the Open button. USING THE DATABASE WIZARD 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. Once you select a template, the Wizard will ask you a series of questions about your data to tailor the template to your specific needs. When the Wizard is finished, you will have an empty database structure into which you can enter data. Here is an overview on how to create a new database using the Database Wizard (refer to Figure T6.5): FIGURE T6.4 Open Existing Database File Double-click file name to open. 1. Click the On my computer... link in the Templates section of the New File task pane. 2. If necessary, click the Databases tab. 3. Click an icon to select a template, then click OK. 4. Type a file name for your database. 5. Read each screen carefully. Click Next to go to the next step. 6. The first step will present you with a list of tables that will be in your database. Some tables have optional ...or select the file name and click the Open button. Plug-In T6 Basic Skills and Tools Using Access * T6-5 haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 6 CONFIRMING PAGES fields. To add a field, click the check box in front of the field name. If you change your mind and want to remove a field from your database, click the check box to remove the checkmark. 7. Next, you will select a graphic style for screen display and another style for printed reports. 8. Finally, you will give the database a title and build the database. USING THE DATABASE WINDOW AND OBJECT VIEWS FIGURE T6.5 Create a New Blank Database Using a Template 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: I I I I To work with a particular type of database object, click the corresponding button in the left column of the Database windowTables, Queries, Forms, Reports, Pages, and so on. To view a database object, select it and then click the Open button in the Database window, or just double-click the object. To change the design of a database object, select it and click the Design View button. To create a new database object of the type currently displayed in the Database window, click the New button to open the New Object dialog box (New Table, New Query, New Form, New Report, or New Data Access page). The New Object dialog box will display a list of all the ways to create a new database object (the list varies according to the type of object that is being created). FIGURE T6.6 Access Database Window Delete Design object Objects Object list T6-6 * Plug-In T6 Close database Basic Skills and Tools Using Access Database window haa23684_PlugInT6CD.qxd I I I I 9/6/06 5:38 PM Page 7 CONFIRMING PAGES To make a copy of a database object, right-click it 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, select it and press F2. To delete a database object, select it and press the Delete key or click the Delete button in the Database window. To close the current database, together with the Database window, click the Close button in the upper-right corner of the window. 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 Design view when you want to change the structure or properties of the table. To open a table from the Database window: 1. In the Database window, click Tables on the Objects bar to display your list of tables. 2. To automatically open a table in Datasheet View, double-click the name of the table. This is the Datasheet view where you can enter data in the table. 3. To change to Design view, click the View button on the toolbar (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 several different ways to create tables: I I I Create a table with the Table Design Wizard. Create a table using data entry. Create a table in Design view. CREATING A TABLE WITH THE TABLE DESIGN WIZARD Beginning a new database can be daunting. To make it easier, Access offers a wide variety of sample tables. Rather than creating your first table from scratch, try using the Table Design Wizard. View button FIGURE T6.7 Datasheet View Plug-In T6 Basic Skills and Tools Using Access * T6-7 haa23684_PlugInT6CD.qxd Select the type of table. 9/6/06 5:38 PM Page 8 CONFIRMING PAGES Select the fields to be included in the table. Select a sample table from the list. Fields in new table. To create a table using the Table Design Wizard (see Figure T6.8): 1. Double-click Create table by using wizard in the Tables window. 2. Select the type of table you need: business or personal. 3. Select the Sample Table that best fits your needs. 4. Select the fields from the sample that you want to include in your table. 5. Give your Table a meaningful name, such as CUSTOMER. 6. Allow Access to set the Primary Key. 7. Select what you want to do next: modify the table design, enter data in Datasheet view, or create a data entry form based on the table you just created. 8. Click the Finish button to end the wizard and finalize your table. FIGURE T6.8 CREATING A TABLE USING DATA ENTRY Create a Table By Using Wizard 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.9): 1. 2. 3. 4. If necessary, click Tables in the Objects bar. Double-click Create table by entering data. The new table appears and is ready for data entry. Rename the fields that you are going to use by double-clicking the column name (Field1, Field2, etc.) and typing the new name (First Name, Last Name, etc.). CREATING A TABLE IN DESIGN VIEW FIGURE T6.9 Create a Table By Entering Data Sometimes the Table Design Wizard does not offer a sample that fits your needs. In this case, you might want to design your table using Design view. 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. In the left column of the Database window, make sure that the Tables object is selected. 2. Click Design on the Database dialog box. 3. Create fields for the following (see Figure T6.10): a. Field Name = BikeID, Data Type = Number, Field Size = Long Integer, and make the BikeID the Primary Key b. Field Name = Description, Data Type = Text, Field Size = 25, Required = Yes c. Field Name = CostPerHour, 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 T6-8 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 9 CONFIRMING PAGES identify the records contained within the table. A table must have a primary key if it is on the \"one\" side of a oneto-many relationship, 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 choose Edit, Primary Key or click the Primary Key button on the toolbar. Access will mark the primary key field(s) with a key icon, as shown in Figure T6.11. To remove the primary key designation from a field, select it and choose the Primary Key command or click the Primary Key toolbar button again. FIGURE T6.10 Bike Table 4. Close the Table dialog box, and click Yes to Save the changes. Enter BIKE as the Table name. 5. Create fields for each entry in Figure T6.12, using field sizes and descriptions as appropriate. Remember to set the primary key and that the Required property is set to Yes as appropriate. 6. Close the Table dialog box, and click Yes to Save the changes. Enter CUSTOMER as the Table name. 7. Create fields for each entry in Figure T6.13, 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 primary 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 on the toolbar. 8. Close the Tables dialog box, and click Yes to Save the changes. Enter RENTAL for the Table name. Select the field that will be the primary. FIGURE T6.11 Setting the Primary Key Primary key button Indicates that this is a primary key field. Plug-In T6 Basic Skills and Tools Using Access * T6-9 haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 10 CONFIRMING PAGES FIGURE T6.12 Customer Table FIGURE T6.13 Rental Table All three fields act as a primary key field. 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 available. 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 field type, and you cannot sort a date field efficiently unless you use the date/time field 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. 2. 3. 4. Open the table in Design view. Click the data type for the field that you want to change. Click the drop-down arrow to see the list of available data types. Select the appropriate data type for your data (see Figure T6.14). USING THE INPUT MASK WIZARD To ensure that users enter data in a particular format, use the Input Mask property. 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.15): 1. Open the CUSTOMER table in Design view. 2. Click the Telephone field. 3. Click the Input Mask box in the Field Properties pane. T6-10 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 11 CONFIRMING PAGES FIGURE T6.14 Data Types 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). 4. 5. 6. 7. 8. Click the Input Mask Wizard build button (the ... ellipse button). If you need to save the table, Access will prompt you to do so now. Select the Phone Number under the Input Mask column. Click the Next button to continue. 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 character you want. You can test the new format by typing 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.15 Input Mask Wizard Input mask property Sample input masks Plug-In T6 Basic Skills and Tools Using Access * T6-11 haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 12 CONFIRMING PAGES 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.16): 1. Open the BIKE table in Design view. 2. Click the Cost Per Hour field. 3. Click the Format drop-down arrow to display the list of predefined formats. Refer back to Figure T6.14 for a list of the data formats. 4. Select the Currency format. 5. Close the BIKE table window. 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 display between \"Apt.\" and the apartment number. Using this text format will save data entry time. Figure T6.17 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. One-to-many relationships are the most common. In a one-to-many relationship, 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 oneto-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. FIGURE T6.16 Format Property Select the field you want to modify. Change field properties. T6-12 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 13 CONFIRMING PAGES FIGURE T6.17 Text Formats Data 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 space. Example: Use for a text field that 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 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 USING THE RELATIONSHIPS WINDOW To define relationships between tables follow these steps: 1. Open the Relationships window by choosing Tools, Relationships or, if the Database window is active, by clicking the Relationships button on the toolbar (refer to Figure T6.18). 2. The Show Table dialog box appears. Select each table listed (i.e., BIKE, CUSTOMER, and RENTAL) and click the Add button, then click the Close button. 3. To define a new relationship, click and drag the Bike ID from the BIKE table and drop it on the Bike ID in the RENTAL table. Click the Create button in the Edit Relationships dialog box that Access shows (see Figure T6.19). Make sure the FIGURE T6.18 Data Formats Plug-In T6 Basic Skills and Tools Using Access * T6-13 haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 14 CONFIRMING PAGES FIGURE T6.19 Edit Relationships Dialog Box Table names and field names are the correct ones being linked. Select the Enforce Referential Integrity box (refer to Figure T6.19). 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.20. 5. Close the Relationships window, and Save the layout. Notice the change to the relationship line. 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 integrity enforced. FIGURE T6.20 Completed Relationships Window T6-14 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd * 9/6/06 5:38 PM Page 15 CONFIRMING PAGES PLUG-IN SUMMARY M ost organizations maintain and manage large amounts of information. One of the most efficient information management computer-based applications is Microsoft Access. 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. * MAKING BUSINESS DECISIONS 1. WasteNot Recycling WasteNot Recycling picks up recyclables from homeowners in Boulder, Colorado. Neighborhoods subscribe to the service so that pickup is cost-effective. WasteNot provides special 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 develop 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 listed below Customer ID Last Name First Name 1 Wagoner Sam 5480 Alpine Street 2 Calahan Eliza 3 Lake 4 Customer Table State ZIP Code Boulder CO 80308 (303) 1615545 05/25/2004 2140 Edgewood Avenue Boulder CO 80308 (303) 8866003 05/25/2004 James 701 Eastman Road Boulder CO 80308 (303) 5624499 08/25/2005 Meadows Sara Pond Hill Drive Boulder CO 80308 (303) 79236460 02/28/2004 20 Smith Alto 114 Lexington Street Boulder CO 80308 (303) 8387111 06/02/2004 64 Monarch Shiela 431 Phillips Lane Boulder CO 80308 (303) 3524847 07/17/2005 65 Guo Amy 1935 Snow Avenue Boulder CO 80308 (303) 5556731 05/19/2005 80 Rivera Juan 482 Weston Avenue Boulder CO 80308 (303) 8152456 12/28/2004 85 Williams Max 230 Southpark Circle Boulder CO 80308 (303) 3330000 07/19/2003 Street City Plug-In T6 Phone First Pickup Basic Skills and Tools Using Access * T6-15 haa23684_PlugInT6CD.qxd 9/6/06 Customer Record Table 5:38 PM Page 16 CONFIRMING PAGES Customer ID Srvc Date Weight Paper Weight Other 1 11/22/2006 8 15 1 10/15/2006 32 85 1 11/7/2006 12 43 2 11/7/2006 19 0 2 11/22/2006 28 174 3 10/15/2006 5 8 3 11/22/2006 16 32 3 12/4/2006 7 12 20 10/15/2006 18 40 20 11/22/2006 35 60 80 10/15/2006 10 10 80 11/7/2006 9 13 80 11/22/2006 16 18 80 12/4/2006 18 21 will hold static customer information such as name, address, and phone. The CUSTOMER RECORD table holds data about each recyclable pickup. Specifically, WasteNot needs you to: 1. 2. 3. 4. Create a Microsoft Access database. Create the tables, fields, data types, and primary key(s) for the database. Populate the database with the data provided above. Create the relationship(s) needed between the tables. 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 connections, 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 what 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 design, 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, fields, data types, and primary key(s) for the database using the structure provided in the T6_ItsAGrindCoffee_Data.xls file. 3. Populate the database with the data provided in the T6_ItsAGrindCoffee_Data.xls file. 4. Create the relationship(s) needed between the tables. T6-16 * Plug-In T6 Basic Skills and Tools Using Access haa23684_PlugInT6CD.qxd 9/6/06 5:38 PM Page 17 CONFIRMING PAGES 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 exclusively to academic customers, offering thousands of full-version software titles at great discounts. Academic Software has partnered with the top technology manufacturers, including Adobe, Microsoft, Sibelius, Sony Media Software, and Wacom, to bring 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 organization. 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, fields, data types, and primary key(s) for the database using the structure provided in the T6_AcademicSoftware_Data.xls file. 3. Populate the database with the data provided in the T6_AcademicSoftware_Data.xls file. 4. Create the relationship(s) needed between the tables. 4. On-Campus Health On-Campus Health is the infirmary located on the campus of the University of Denver. Recordkeeping at the infirmary's pharmacy, although meticulous and professional, is inefficient. Maintaining the recordkeeping using mostly manual systems is becoming more costly as additional 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 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 Campus Health has provided you in the file T6_CampusHeath_Data.xls to complete the following: 1. 2. 3. 4. Create a Microsoft Access database. Create the tables, fields, data types, primary key(s), and the like for the database. Populate the database with the data. Create the relationship(s) needed between the tables. Plug-In T6 Basic Skills and Tools Using Access * T6-17 Elementary Set-up of a Multi-table Database You will essentially complete Problem #1 on pages T15-16 of Plug-in T-6, with some specified additions/modifications. You will also attempt some operations on the database you will create in this assignment, make some observations, & then report these observations in an accompanying document file submission. You will then upload both your database file and your observations to the appropriate dropbox. As soon as you have opened a new database file in Access 2003 or 2007 in order to create this assignment, you must save and name this file: "SU_Lastname_Firstname" where Lastname is your last name and Firstname is your first name. After completing parts 1 through 3, of this assignment, you should also: (a) Add two additional customer records, one with your name and one with a friend's name. (b) Add an additional 14 records to the Customer Service record table. These records should indicate service dates in January and February of 2007. They should also be related to existing customers in the Customer table (including the customer record for you and your friend). When you create the relationship between the 2 tables (i.e. part 4 of this problem), please be sure to check the checkbox for \"Enforce Referential Integrity\" as well as the checkboxes for \"Cascade Update Related Fields\" and \"Cascade Delete Related records\". You will now make some observations based on the following questions: What happens when you try to enter a record into the Customer Service records table for which there is no corresponding record in the Customer table? What happens when you try to delete a record from the Customer table for which there is at least one corresponding record in the Customer table? What happens when you try to change the value of \"Customer ID\" in the Customer table for which there is a corresponding record in the Customer Service Records table? Upon completion, upload your database file and your observations document to the appropriate dropbox for this assignment. Your Observations document file should also be named "SU_Lastname_Firstname" where Lastname is your last name and Firstname is your first name