Assignment : Introduction to Databases, Relationships, and Queries

Purpose and Objective

The purpose of this assignment is to introduce you to building relationships and queries in Access databases.

Q1 Employee Salary Analysis

Exercise 1

Northwind purchases food items from suppliers around the world and sells them to restaurants and specialty food shops. Northwind depends on the data stored in its Access database to process orders and make daily decisions. You will open the Northwind database, examine the Access interface, review the existing objects in the database, and explore Access views. You will add, edit, and delete records using both tables and forms. Finally, you will back up the database.

1. Open, Save, and Enable Content in a Database
This database will help you learn the fundamentals of working with database file.

a. Start your computer. Click Start, and click Access from the list of applications. Click Open Other Files and click Browse. Navigate to the location of your student files. Double-click a01h1Traders. Click the File tab, click Save As, and then click Access Database. Click Save As and save the file as a01h1Traders_LastFirst.
When you save files, use your last and first names. For example, as the Access author, I would save my database as "a01h1Traders_RutledgeAmy".
The Security Warning message bar displays below the ribbon, indicating that some database content is disabled.

b. Click Enable Content on the Security Warning message bar. When you open an Access file, you should enable the content.
2. Now that you have opened the Northwind database, you should examine the Navigation Pane, objects, and views to become familiar with these fundamental Access features.
a. Scroll through the Navigation Pane and notice the Access objects listed under each expanded group.
The Tables group and the Forms group are expanded, displaying all the table and form objects.
b. Double-click the Customers table in the Navigation Pane.
The Customers table opens in Datasheet view, showing the data contained in the table. The Customers tab displays below the ribbon indicating the table object is open. Each customer's record displays on a table row. The columns of the table display the fields that comprise the records.
c. Click the View arrow and then select Design view, in the Views group on the Home tab.
The view of the Customers table switches to Design view. The top portion of Design view displays and the field names match the field headings previously seen in Datasheet view. Additionally, the field's data type, and an optional description of what the field should contain can be seen. The bottom portion of Design view displays the field properties (details) for the selected field.
d. Click the View arrow and then select Datasheet view in the Views group on the Home tab again.
Your view returns to Datasheet view, which shows the data stored in the table.
e. Double-click Employees in the Tables group of the Navigation Pane. Double-click Products from the same location.
The Employees and Products tables open. The tabs for three table objects display below the ribbon: Customers, Employees, and Products.
f. Click Shutter Bar Open/Close on the title bar of the Navigation Pane to hide the Navigation Pane. Click again to show the Navigation Pane.

The Navigation Pane collapses and expands to enable you to view more in the open object window, or to view your database objects.
g. Scroll down in the Navigation Pane and locate Reports.
The Reports group is expanded, and all report objects are displayed.
h. Scroll up until you see Forms. Click Forms in the Navigation Pane.
The Forms group collapses and individual form objects no longer display.
i. Click the Database Tools tab and click Relationships in the Relationships group.
j. Examine the join lines showing the relationships that connect the various tables. For example, the Orders table is connected to the Order Details table using the OrderID field as the common field.
k. Click Relationship Report in the Tools group. The report opens in a new tab. Right- click on the tab and select Save and click OK.
The report is now saved in the Reports section on the Navigation Pane.
l. Click Close x the at the top right of the report to close the Relationships Report.
m. Click Close x the at the top right of the tab to close the Relationships window.
n. Click Close x the at the top right of the table to close the Products table.

3. Modify Records in a Table
You want to learn to edit the data in the Northwind database, because data can change. For example, employees will change their address when they move, and customers will change their order data from time to time.

a. Click the Employees tab to view the Employees table.
b. Double-click Peacock (the value of the Last Name field in the fourth row); the entire name highlights. Type your last name to replace Peacock.
The pencil symbol in the record selector box indicates that the record is being edited but has not yet been saved.
c. Press Tab to move to the next field in the fourth row. Replace Margaret with your first name and press Tab.
You have made changes to two fields in the same record.
d. Click Undo on the Quick Access Toolbar.
Your first and last names revert to Margaret Peacock because you have not yet left the record.
e. Type your first and last names again to replace Margaret Peacock. Press Tab. You should now be in the title field, and the title, Sales Representative, is selected. The record has not been saved, as indicated by the pencil symbol in the record selector box.
f. Click anywhere in the third row where Janet Leverling's data are stored.
The pencil symbol disappears, indicating that your changes have been saved.
g. Click the Address field in the first row, Nancy Davolio's record. Select the entire address and type 4004 East Morningside Dr. Click anywhere on the second record, Andrew Fuller's record.
h. Click Undo.
Nancy Davolio's address reverts to 507 - 20th Ave. E. However, the Undo command is now faded. You can no longer undo the change that you made replacing Margaret Peacock's name with your own.
i. Click Close x at the top right of the table to close the Employees table.
The Employees table closes. You are not prompted to save your changes; they have already been saved for you because Access works in storage, not memory. If you reopen the Employees table, you will see your name in place of Margaret Peacock's name.

4. Add Records to a Table
You have been asked to add information about a new line of products to the Northwind database. You add records to the Products table through the Products Form. The two objects are directly connected.

a. Right-click the Customers tab and click Close All.
b. Click the Queries group in the Navigation Pane to collapse it. Click the Reports group in the Navigation Pane to collapse it as well.
c. Click the Forms group in the Navigation Pane to expand the list of available forms.
d. Double-click the Products form to open it.
e. Click the Next record arrow in the Navigation bar at the bottom of the form window. Click Last record, click Previous record, and then click First record.
f. Click Find in the Find group on the Home tab, type Grandma in the Find What box, click the Match arrow, and then select Any Part of Field. Click Find Next.
You should see the data for Grandma's Boysenberry Spread. Selecting the Any Part of Field option will return a match even if it is contained as part of a word.
g. Click Cancel to close the Find dialog box.
h. Click New in the Records group on the Home tab.
i. Type the following information for a new product. Click, or press Tab, to move into the next cell. Notice as soon as you begin typing, Access will assign a ProductID to this product.

5. Delete Records from a Table
To help you understand how Access stores data, you verify that the new product is in the Products table. You also attempt to delete a record.

a. Click the Forms group in the Navigation Pane to collapse it. Expand the Tables group.
b. Double-click the Products table to open it.
c. Click Last record in the Navigation bar.
The Pecan Pie record you entered in the Products form is listed as the last record in the Products table. The Products form was created from the Products table.
Your newly created record, Pecan Pie, is stored in the Products table even though you added it using the form.
d. Navigate to the fifth record in the table and place the pointer in the record, Chef Anton's Gumbo Mix.
e. Scroll right using the horizontal scroll bar until you see the Discontinued field.
The check mark in the Discontinued check box tells you that this product has been discontinued.
f. Click the record selector to the left of the fifth record.
A border surrounds the record and the record is shaded, indicating it is selected.
g. Click Delete in the Records group and read the error message.
The error message tells you that you cannot delete this record because the table "Order Details" has related records. (Customers ordered this product in the past.) Even though the product is now discontinued and no stock remains, it cannot be deleted from the Products table because related records exist in the Order Details table.
h. Click OK.
i. Navigate to the last record and click the record selector. The Pecan Pie record you added earlier is displayed.
j. Click Delete in the Records group. Read the warning.
The warning box tells you that this action cannot be undone. Although this product can be deleted because it was just entered and no orders were created for it, you do not want to delete the record.
k. Click No. You do not want to delete this record. Close the Products table.

6. Use Database Utilities
You will protect the Northwind database by using the Back Up Database utility.
a. Click the File tab and click Save As.
b. Double-click Back Up Database under the Advanced section to open the Save As dialog box.
c. The backup utility assigns the default name by adding a date to your file name.
d. Verify that the Save in folder displays the location where you want your file saved and click Save.
e. The backup file should be submitted as well.

Exercise 2

The sales manager at Northwind Traders wants quick answers to her questions about customer orders. You use the Access database to filter tables to answer these questions and sort the records based on the manager's requirements.

1. Use a Selection Filter to Find Exact Matches
The sales manager asks for a list of customers who live in London. You use a Selection filter with an equal condition to locate these customers.

a. Open the a01h1Traders_LastFirst database if you closed it at the end of Hands-On Exercise 1, and save it as a01h2Traders_LastFirst, changing h1 to h2. Click Enable Content.
b. Double-click the Customers table in the Navigation Pane, navigate to record 4, and then replace Thomas Hardy with your name in the Contact Name field.
c. Scroll right until the City field is visible. The fourth record has a value of London in the City field. Click the London field to select it.
d. Click Selection in the Sort & Filter group on the Home tab.
e. Select Equals "London" from the menu. Six records are displayed.
The Navigation bar display shows that six records that meet the London criterion are available. The other records in the Customers table are hidden. The Filtered icon also displays on the Navigation bar and column heading, indicating that the Customers table has been filtered.
f. Click Toggle Filter in the Sort & Filter group to remove the filter.
g. Click Toggle Filter again to reset the filter.

2. Use a Selection Filter to Find Records Containing a Value
The sales manager asks you to narrow the list of London customers so that it displays only records that contain the title Sales Representatives. To accomplish this task, you add a second layer of filtering using a Selection filter.

Click in any field value in the Contact Title field that contains the value Sales Representative.

Click Selection in the Sort & Filter group, select Contains "Sales Representative," and then compare your results to those shown in Figure 1.36.

Three records match the criteria you set. You have applied a second layer of filtering to the customers in London. The second layer further restricts the display to only those customers who have the words Sales Representative contained in their titles. Because you chose Contains as your filter, any representatives with the phrase Sales Representative appear. This includes Victoria Ashworth, who is a Sales Representative Trainee.

Close the Customers table. Click Yes when prompted to save the design changes to the Customers table.

3. Use Filter By Form
You are asked to provide a list of records that do not match just one set of criteria. You will provide a list of all extended prices less than $50 for a specific sales representative. Use Filter By Form to provide the information when two or more criteria are necessary. You also preview the results in Print Preview to see how the list would print.

a. Click the Tables group in the Navigation Pane to collapse the listed tables.
b. Click the Queries group in the Navigation Pane to expand the list of available queries.
c. Locate and double-click Order Details Extended to open it.
This query contains information about orders. It has fields containing information about the sales person, the Order ID, the product name, the unit price, quantity ordered, the discount given, and an extended price. The extended price is a field used to total order information.
d. Click Advanced in the Sort & Filter group and select Filter By Form from the list. The first field, First Name, is active by default.
All of the records are now hidden, and you see only field names and an arrow in the first field. Although you are applying Filter By Form to a query, you can use the same process as applying Filter By Form to a table. You are able to input more than one criterion using Filter By Form.
e. Click the First Name arrow.
A list of all available first names displays. Your name should be on the list.
f. Select your first name from the list.
g. Click in the first row under the Last Name field to reveal the arrow. Locate and select your last name by clicking it.
h. Scroll right until you see the Extended Price field. Click in the first row under the Extended Price field and type <50.
This will select all of the items ordered where the total price was less than 50.
i. Click Toggle Filter in the Sort & Filter group.
You have specified which records to include and have executed the filtering by clicking Toggle Filter.
j. Click the File tab, click Print, and then click Print Preview.
You instructed Access to preview the filtered query results. The preview displays the query title as a heading. The current filter is applied, as well as page numbers.
k. Click Close Print Preview in the Close Preview group.
l. Close the Order Details Extended query. Click Yes when prompted to save your changes.

4. Perform Sorts
The Sales Manager is pleased with your work; however, she would like some of the information displayed in a different order. You will now sort the records in the Customers table using the manager's new criteria.

a. Click the Queries group in the Navigation Pane to collapse the listed queries.
b. Click the Tables group in the Navigation Pane to expand the list of available tables and double-click the Customers table to open it.
This table contains information about customers. The table is sorted in alphabetical order by CustomerID.
c. Click Shutter Bar Open/Close in the Navigation Pane to hide the Navigation Pane.
It will be easier to locate fields in the Customer table if the Navigation Pane is hidden.
d. Click any entry in the Customer ID field. Click Descending in the Sort & Filter group on the Home tab.
Sorting in descending order on a text field produces a reverse alphabetical order.
e. Scroll right until you can see both the Country and City fields.
f. Click the Country column heading. The entire field is selected.
g. Click the Country column heading again and hold down the left mouse button.
A thick line displays on the left edge of the Country field.
h. Ensure that you see the thick line on the edge of the Country field. Drag the Country field to the left until the thick line moves between the City and Region fields. Release the mouse button and the Country field position moves to the right of the City field.
You moved the Country field next to the City field so that you can easily sort the table based on both fields.
i. Click any city name in the City field and click Ascending in the Sort & Filter group.
The City field displays the cities in alphabetical order.
j. Click any country name in the Country field and click Ascending in the Sort & Filter group.
The countries are sorted in alphabetical order. The cities within each country also are sorted alphabetically. For example, the customer in Graz, Austria, is listed before the customer in Salzburg, Austria.
k. Close the Customers table. Click Yes to save the changes to the design of the table.
l. Click Shutter Bar Open/Close in the Navigation Pane to show the Navigation Pane.
m. Close the database. Submit the file.

Exercise 3

You have decided to use Access to create a personal contact database. Rather than start from a blank table, you use an Access Contacts template to make your database creation simpler. You explore the template objects and customize the database to suit your needs.

1. Create a Database Using a Template
You locate an Access template that you can use to create your personal contact database. This template not only enables you to store names, addresses, telephone numbers, and other information, but also helps you categorize your contacts, send email messages, and create maps of addresses. You download and save the template.

a. Open Access. Click the Contacts template.
b. Click Browse to navigate to the folder where you are saving your files, type a01h3Contacts_LastFirst as the file name, and then click OK.
c. Click Create to download the template.
d. Click Enable Content on the Security Warning message bar.
e. Click the Show Welcome when this database is opened check box to deselect it. Close the Welcome to the Contacts Database page.
The database displays the Contact List form.

2. Explore and Customize a Database Template
Because the database opens in the Contact List form, you decide to begin by entering a contact in the form. You then explore the objects created by the template so that you understand the organization of the database.

a. Click in the First Name field of the first record. Type the following information, pressing Tab between each entry. Do not press Tab after entering the ZIP/Postal Code.

b. Scroll back to the left if necessary to see the first field. Click Open in the first field of Dr. Machuca's record. Open is a hyperlink to a different form in the database. The Contact Details form opens, displaying Dr. Machuca's
information. More fields are available for you to use to store information. (Note that this form could also be opened from the Navigation Pane.)
c. Type the following additional information to the record:

Field Value
Street 56 West 200 North
City Mapleton
State/Province UT
Country/Region USA
Notes Available Tuesday - Friday 7 a.m. to 4 p.m.
d. Click the Click to Map hyperlink to view a map to Dr. Machuca's office.
Bing displays a map to the address in the record. You can get directions, locate nearby businesses, and use many other options.

e. Close the map. Click Close in the top right of the form to save and close the Contact Details form.
The record is saved.
f. Click New Contact beneath the Contact List title bar. The Contact Details form opens to a blank record.
g. Type the following information for a new record, pressing Tab to move between fields. Some fields will be blank.

Field Value

First Name Rowan
Last Name Westmoreland
Company Phoenix Aesthetics
Job Title Aesthetician
Email Rowan55W5@email.com
Category Personal
Street 425 North Main Street
City Springville
State/Province UT
Zip/Postal Code 84663
Mobile Phone 801-555-2221
Notes Recommended by Michelle

h. Click Close.
i. Double-click the Contacts table in the Navigation Pane.
The information you entered using the Contact List form and the Contact Details form displays in the Contacts table.

j. Double-click the Phone Book report in the Navigation Pane.
The Phone Book report opens displaying the contact name and phone information organized by category.
k. Double-click the Directory report in the Navigation Pane.
The Directory report opens, displaying a full alphabetical contact list. The Directory report was designed to display more fields than the Phone Book, but it is not organized by category.
l. Click All Access Objects on the Navigation Pane and select Tables and Related Views.
You can now see the objects that are based on the Contacts table.
m. Right-click the Directory report tab and select Close All.
n. Close the database and exit Access.
o. Submit the file.

Q2 Employee Salary Analysis

Creating a sample database as an intern for Commonwealth Federal Bank will be a great opportunity for you to showcase your database design and Access skills.

1. Create a Table in Datasheet View
You create a new database and a table to store information about the bank's branches. You enter the data for the first record (BranchID, Manager, and Location). You examine the design of the table and realize that the BranchID field is a better unique identifier, making the ID field redundant.

a. Start Access and click Blank database.
b. Type a02h1Bank_LastFirst in the File Name box.
c. Click Browse to find the folder location where you will store the database and click OK. Click Create to create the new database.
Access will create the new database named a02h1Bank_LastFirst and a new table, Table1, will automatically open in Datasheet view. There is already an ID field in the table by default that uses the AutoNumber data type.
d. Click Click to Add and select Short Text as the Data type. Click to Add changes to Field1. Field1 is selected to make it easier to change the field name.
e. Type BranchID and press Tab.
A list of data types for the third column opens so that you can select the data type for the third column.
f. Select Short Text in the Click to Add list, type Manager, and then press Tab.
g. Select Short Text in the Click to Add list and type Location.
h. Click in the first column (the ID field) next to the New Record asterisk, press Tab, and then type the data for the new table as shown in Figure 2.8, letting Access assign the ID field for each new record (using the AutoNumber data type). Replace YourLastName with your own last name.
i. Click Save on the Quick Access Toolbar. Type Branch in the Save As dialog box and click OK.
Entering field names, data types, and data directly in Datasheet view provides a simplified way to create the table initially.
j. Click View in the Views group on the Home tab to switch to Design view of the Branch table.
The field name for each of the four fields displays along with the data type.
k. Ensure that the ID field is selected, click Delete Rows in the Tools group on the Design tab. Click Yes to both warning messages.
Access responds with a warning that you are about to permanently delete a field and a second warning that the field is the primary key. You delete the field because you will set the BranchID field as the primary key.

2. Set a Table'S Primary Key
You determine that BranchID is a better choice as the table's primary key field. Rather than the sequential auto-numbering that Access uses by default for the key field, it is better to use the actual branch numbers. In this step, you will make the BranchID field the primary key field.

a. Ensure that the BranchID field is selected, as shown in Figure 2.9.
b. Click Primary Key in the Tools group on the Design tab.
You set BranchID as the primary key. The Indexed property in the Field Properties section at the bottom of the design window displays Yes (No Duplicates). When a primary key is set on a field, an index is automatically created by Access for that field to speed up searching through it.
c. Click Save on the Quick Access Toolbar to save the table.

3. Work with Field Properties
You will modify the table design further to comply with the bank's specifications. Refer to Figure 2.10

a. Click in the BranchID field name; modify the BranchID field properties by completing the following steps:
a. Click in the Field Size box in the Field Properties pane and change 255 to 5.
b. Click in the Caption box and type Branch ID. Make sure Branch and ID have a space between them.
A caption provides a more descriptive field name. It will display as the column heading in Datasheet view.
c. Check the Indexed property; confirm it is Yes (No Duplicates).
d. Check the Required property; confirm it is Yes. A primary key field always requires a value to be entered.
b. Click the Manager field name; modify the Manager field properties by completing the following steps:
a. Click in the Field Size box in the Field Properties pane and change 255 to 30.
b. Click in the Caption box and type Manager's Name.
c. Click the Location field name and modify the following Location field properties by completing the following steps:
a. Click in the Field Size box in the Field Properties pane and change 255 to 30.
b. Click in the Caption box and type Branch Location.

4. Create a New Field in Design View
You notice that a date field is missing from your new table. Modify the table to add the new field.
a. Click in the first blank field row below the Location field name and type StartDate. You added a new field to the table.
b. Press Tab to move to the Data Type column. Click the Data Type arrow and select Date/Time.
c. Press Tab to move to the Description column and type This is the date the manager started working at this location.
d. Click in the Format box in the Field Properties pane, click the arrow, and then select Short Date from the list of date formats.
e. Click in the Caption box and type Manager's Start Date.
f. Click Save on the Quick Access Toolbar.
A warning dialog box opens to indicate that "Some data may be lost" because the size

of the BranchID, Manager, and Location field properties were shortened (in the previous step). It asks if you want to continue anyway. Always read the Access warning! In this case, you can click Yes to continue because you know that the existing and anticipated data values are no longer than the new field sizes.

g. Click Yes in the warning box.

5. Modify the Table in Datasheet View
As you work with the new sample, you will modify tables in the bank database by adding and modifying records. Refer to Figure 2.12 as you complete Step 5.

a. Right-click the Branch tab and select Datasheet View from the shortcut menu. The table displays in Datasheet view. The field captions display at the top of the columns, but they are cut off.
b. Position the pointer over the border between Branch ID and Manager's Name so that it becomes a double-headed arrow and double-click the border. Repeat the process for the border between Manager's Name and Branch Location, the border between Branch Location and Manager's Start Date, and the border after Manager's Start Date. The columns contract or expand to display the best fit for each field name.
c. Click inside the Manager's Start Date in the first record and click the Date Picker next to the date field. Use the navigation arrows to find and select December 5, 2016 from the calendar.
You can also enter the dates by typing them directly into the StartDate field.
d. Type the start date directly in each field for the rest of the managers, as shown in Figure 2.12.
e. Click the Close at the top-right corner of the Branch datasheet, below the ribbon. Click Yes to save the changes.
f. Double-click the Branch table in the Navigation Pane to open the table.
g. Click the File tab, click Print, and then click Print Preview.
h. Occasionally, users will print an Access table. However, database developers usually create reports to print table data.
i. Click Close Print Preview in the Close Preview group and close the Branch table.

Exercise 2 Multiple-Table Databases

You created a new bank database and a new Branch table. Now you are ready to import additional tables-one from an Excel spreadsheet and one from an Access database. The data are formatted correctly and structured properly so that you can begin the import process.

1. Import Excel Data
You have discovered that one of Commonwealth's existing files contains reusable customer data. The data are related to the Branch table you created earlier, so you decide to import the data into your database. In this step, you import an Excel spreadsheet into the bank database. Refer to Figure 2.23 as you complete Step 1.

a. Open a02h1Bank_LastFirst if you closed it at the end of Hands-On Exercise 1, and save it as a02h2Bank_LastFirst, changing h1 to h2.
b.
c. Click Enable Content below the ribbon to indicate that you trust the contents of the database.
d.
e. Click the External Data tab, click New Data Source, point to From File in the Import & Link group, and then select Excel to launch the Get External Data - Excel Spreadsheet dialog box. Ensure that the Import the source data into a new table in the current database option is selected.

f. Click Browse and navigate to your student data files. Select the a02h2Customers.xlsx workbook. Click Open and click OK to open the Import Spreadsheet Wizard.
g.
h. Ensure that the First Row Contains Column Headings check box is selected to indicate to Access that column headings exist in the Excel file.
i.
j. The field names CID, FirstName, LastName, Street, City, State, Zip, and Phone will import from Excel along with the data stored in the rows in the worksheet. You will modify the field names later in Access.
k.
l. Click Next. m.
n. Ensure that CID is displayed in the Field Name box in Field Options. Click the Indexed arrow and select Yes (No Duplicates). Click Next.
o.
p. The CID (CustomerID) will become the primary key in this table. It needs to be a unique identifier, so you change the property to No Duplicates.
q.
r. Click the Choose my own primary key option. Make sure that the CID field is selected. Click Next.
s.
t. The final screen of the Import Spreadsheet Wizard prompts you to name your table. The name of the Excel worksheet is Customers, and Access defaults to the worksheet name.
u.
v. Click Finish to accept Customers as the table name. w.
x. A dialog box opens prompting you to save the steps of this import to use again. If this is data that are to be collected in Excel and updated to the database on a regular basis, saving the import steps would save time. You do not need to save the import steps in this example.
y.
z. Click Close. aa.
bb. The new table displays in the Navigation Pane of the bank database. cc.
dd. Open the imported Customers table in Datasheet view and double-click the border between each of the field names to adjust the columns to Best Fit. Compare your table to Figure 2.23.
ee.
ff. Save and close the table.

2. Import Access Data
You import an Access database table that contains account information related to the Branch and Customers tables that you created and will help you to build out a sample database for the bank that is a realistic example. You use the Import Wizard to import the database table. Refer to Figure 2.24 as you complete Step 2.

a. Click the External Data tab, click New Data Source, point to From Database in the Import & Link group, and then click Access to launch the Get External Data - Access Database dialog box. Ensure that the Import tables, queries, forms, reports, macros, and modules into the current database option is selected.
b. Click Browse and navigate to your student data files. Select the a02h2Accounts database. Click Open and click OK to open the Import Objects dialog box.
c. Click the Accounts table and click OK.
d. Click Close in the Save Import Steps dialog box.
The Navigation Pane now contains three tables: Accounts, Branch, and Customers.
e. Open the imported Accounts table in Datasheet view and compare it to Figure 2.24. Notice that the imported table contains AID, CID, and BID fields. The fields need to be modified to be more readable, and to ensure that they work with the other tables in your database.
f. Close the table.

3. Modify an Imported Table's Design
When importing tables from either Excel or Access, the fields may have different data types and property settings than those required to create table relationships. You will modify the tables so that each field has the correct data type and field size. Refer to Figure
2.25 as you complete Step 3.

a. Right-click the Accounts table in the Navigation Pane.
b. Select Design View from the shortcut menu to open the table in Design view. The Accounts table displays with the primary key AID selected.
c. Change the AID field name to AccountID.
d. Change the Field Size property to Long Integer.
Long Integer ensures that there will be enough numbers as the number of customers grows over time and may exceed 32,768 (the upper limit for Integer values).
e. Type Account ID in the Caption box for the AccountID field. The caption contains a space between Account and ID.
f. Click the CID field. Change the CID field name to CustomerID.
g. Change the Field Size property to Long Integer.
You can select the Field Size option using the arrow, or you can type the first letter of the option you want. For example, type l for Long Integer or s for Single. Make sure the current option is completely selected before you type the letter.
h. Type Customer ID in the Caption box for the CustomerID field. The caption contains a space between Customer and ID.
i. Click the BID field. Change the BID field name to BranchID.
j. Type 5 in the Field Size property box in the Field Properties.
k. Type Branch ID in the Caption property box for the Branch ID field.
l. Change the Data Type of the Balance field to Currency.
The Currency data type is used for fields that contain monetary values. In this case, changing the data type is not consequential; formatting the imported Balance field as Currency will not change the original data values.
m. Change the Data Type of the OpenDate field to Date/Time and set Short Date in the

Format field property. Type Open Date in the Caption property box. The OpenDate field stores the date that each account was opened.
n. Click View in the Views group to switch to Datasheet view. Read the messages and click Yes to each one.
In this case, it is acceptable to click Yes because the shortened fields will not cut off any data. Leave the table open.
o. Right-click the Customers table in the Navigation Pane and from the shortcut menu, select Design View.
p. Change the CID field name to CustomerID. Change the Field Size property of the CustomerID field to Long Integer and add a caption, Customer ID.
You have entered an intentional space between Customer and ID for readability in the datasheet.
q. Change the Field Size property to 20 for the FirstName, LastName, Street, and City fields. Change the Field Size for State to 2.
r. Change the data type for Zip and Phone to Short Text. Change the Field Size property to 15 for both fields. Remove the @ symbol from the Format property where it exists for all fields in the Customers table.
s. Click the Phone field name and click Input Mask in Field Properties. Click the ellipsis on the right side to launch the Input Mask Wizard. Click Yes to save the table and click Yes to the Some data may be lost warning. Click Finish to apply the default phone number input mask.
The phone number input mask enables users to enter 6105551212 in the datasheet, and Access will display it as (610) 555-1212.
t. Click Save to save the design changes to the Customers table.

4. Add Data to an Imported Table
Now that you have created the database tables, you discover that you need to add another customer and his account records. Refer to Figure 2.26 as you complete Step 4.

a. Click View in the Views group to display the Customers table in Datasheet view. The asterisk at the bottom of the table data in the row selector area is the indicator of a place to enter a new record.
b. Click next to the * in the Customer ID field in the new record row below 30010. Type 30011. Fill in the rest of the data using your personal information as the customer. You may use a fictitious address and phone number.
Note the phone number format. The input mask you set formats the phone number as you type.
c. Close the Customers table. The Accounts table tab is open.
d. Click next to the * in the Account ID field in the new record row. Type 1024. Type 30011 as the Customer ID and B50 as the Branch ID. Type 14005 for the Balance field value. Type 8/7/2018 for the Open Date.
e. Add the following records to the Accounts table:

f. Close the Accounts table but keep the database open.

5. Establish Table Relationships
The tables for the sample bank database have been designed and populated. Now you will establish connections between the tables. Look at the primary and foreign keys as a guide. Refer to Figure 2.27 as you complete Step 5.

a. Click the Database Tools tab and click Relationships in the Relationships group. The Relationships window opens, and the Show Table dialog box displays.
b. Double-click each of the three tables displayed in the Show Table dialog box to add them to the Relationships window. Click Close in the Show Table dialog box.
c. Click and drag the border of the Customers table field list to resize it so that all the fields are visible. Arrange the tables as shown in Figure 2.27.
d. Drag the BranchID field (the primary key) in the Branch table onto the BranchID field (the foreign key) in the Accounts table. The Edit Relationships dialog box opens. Click Create.
Access interprets a one-to-many relationship between the Branch and Accounts tables. Each single branch relates to many accounts. At this point, the tables are related by the BranchID field, but referential integrity is not yet enforced between them.
e. Drag the CustomerID field (the primary key) in the Customers table onto the CustomerID field (the foreign key) in the Accounts table. The Edit Relationships dialog box opens. Click Create.
Access interprets a one-to-many relationship between the Customers and Accounts tables. A customer will have only a single CustomerID number. The same customer may have many different accounts: Savings, Checking, Credit Card, and so forth. The tables are related by the CustomerID field, but referential integrity is not enforced between them.
f. Click Save on the Quick Access Toolbar to save the changes to the relationships.

6. Enforce and Test Referential Integrity
The design of the bank database must be 100% correct; otherwise, data entry may be compromised. Even though the table relationships are set, you need to enforce referential integrity. Next, you will test them by entering invalid data. If referential integrity is enforced, the invalid data will be rejected by Access.

a. Double-click the join line between the Branch and Accounts tables. The Edit Relationships dialog box opens. Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes to select them. Click OK.
A black line displays, joining the two tables. It has a 1 at the end near the Branch table and an infinity symbol on the end next to the Accounts table. Referential integrity has been successfully enforced between the two tables; every BranchID in the Accounts table exists in the Branch table.

b. Double-click the join line between the Customers and Accounts tables. The Edit Relationships dialog box opens. Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes to select them. Click OK. Close the Relationships window.
c. Double-click the Accounts table to open it in Datasheet view. Add a new record, pressing Tab after each field: Account ID: 1027, Customer ID: 30003, Branch: B60, Balance: 4000, Open Date: 4/13/2018. Press Enter.
You attempted to enter a nonexistent BranchID (B60) and were not allowed to make that error. A warning message is informing you that a related record in the Branch table is required because the Accounts table and the Branch table are

connected by a relationship with Enforce Referential Integrity checked.
d. Click OK. Double-click the Branch table in the Navigation Pane and examine the data in the BranchID field. Notice the Branch table has no B60 record. Close the Branch table.
e. Replace B60 with B50 in the new Accounts record and press Tab three times. As soon as the focus moves to the next record, the pencil symbol disappears, and your data are saved.
You successfully identified a BranchID that your Accounts table recognizes as a valid value. Because referential integrity between the Accounts and Branch tables has been enforced, Access looks at each data entry item in a foreign key and matches it to a corresponding value in the table where it is the primary key. In Step c, you attempted to enter a nonexistent BranchID and were not allowed to make that error. In Step e, you entered a valid BranchID. Access examined the BranchID field in the Branch table and found a corresponding value for B50.
f. Close the Accounts table. Close any open tables.
g. Keep the database open if you plan to continue with the next Hands-On Exercise. If not, close the database and exit Access.

Exercise 3 Single-Table Queries

The tables and table relationships have been created, and some data have been entered in the bank database. Now, you begin the process of analyzing the bank data using queries. You decide to begin with the Accounts table.

1. Use the Query Wizard
You decide to start with the Query Wizard, knowing you can always alter the design of the query later in Design view. You will display the results in Datasheet view. Refer to Figure
2.40 as you complete Step 1.

a. Click the Create tab and click Query Wizard in the Queries group.
The New Query dialog box opens. Simple Query Wizard is selected by default.
b. Click OK.
c. Verify that Table: Accounts is selected in the Tables/Queries box.
d. Click AccountID in the Available Fields list and click Add One Field > to move it to the Selected Fields list. Repeat the process with CustomerID, BranchID, and Balance. The four fields should now display in the Selected Fields list box.
e. Click Next.
f. Confirm that Detail (shows every field of every record) is selected and click Next.
g. Name the query Accounts from Campus Branch. Click Finish.
This query name describes the data in the query results. Your query should have four fields: AccountID, CustomerID, BranchID, and Balance. The Navigation bar indicates that 27 records are present in the query results.

2. Specify Query Criteria, Specify Sort Order, and Run The Query
You decide to modify your query to analyze accounts specifically for the Campus branch. Refer to Figure 2.41 as you complete Step 2.

a. Click the Home tab and click View in the Views group.
The Accounts from Campus Branch query opens in Design view. You have created and named this query to view only those accounts at the Campus branch.
However, other branches' accounts also display. You need to limit the query

results to only the records for the branch of interest.
b. Click in the Criteria row (fifth row) in the BranchID column of the query design grid, type B50, and then press Enter.
c. B50 is the BranchID for the Campus branch. Access criteria are not case sensitive; therefore, b50 and B50 will produce the same results. Access adds quotation marks around text criteria after you press Enter, or you can type them yourself.
d. Click in the Sort row (third row) in the AccountID column and select Ascending.
e. Click Run in the Results group.

3. Copy and Modify a Query
You decide to modify a value directly in the query datasheet and add a new record using the Accounts table. To create a second query that sorts the records in a different way, you copy and modify the existing query.

a. Click in the Balance field in the record for account 1020. Change $1,200 to
$12,000. Press Enter. Save and close the query. You modified the record directly in the query results.
b. Double-click the Accounts table in the Navigation Pane. Add a new record to the Accounts table with the following data: 1028 (Account ID), 30005 (Customer ID), B50 (Branch ID), 8000 (Balance), and 8/4/2019 (Open Date). Press Tab.
The new record is added to the Accounts table.
c. Double-click the Accounts from Campus Branch query in the Navigation Pane.
d. Customer 30005 now shows two accounts: one with a balance of $1,500 and one with a balance of $8,000. Close the query. Right-click the Accounts from Campus Branch query in the Navigation Pane, and from the shortcut menu, select Copy. Right-click in the empty space in the Navigation Pane, and from the shortcut menu, select Paste. Type Accounts from Campus Branch Sorted as the query name. Click OK.
e. Double-click the Accounts from Campus Branch Sorted query in the Navigation Pane. Click View in the Views group to return to Design view of the duplicate query.
f. Click in the Sort row of the AccountID field, click the arrow, and then select (not sorted). Click in the Sort row of the CustomerID field and select Ascending. Click in the Sort row of the Balance field and select Ascending.
g. Click Run in the Results group.
Customer 30005 now shows two accounts with the two balances sorted in ascending order. The record for Account ID 1028 was added in the underlying Accounts table, and the query updates automatically to display it with the others. All other customers with more than one Campus branch account are listed in ascending order by balance.
h. Save the query. Close the Accounts from Campus Branch Sorted query and close the Accounts table.

Exercise 4 Multiple Queries

In order to evaluate the sample set of data further, you will create queries that are based on multiple tables rather than on a single table. To create a multitable query, you decide to open an existing query, add additional tables and fields to it, and then save the query.

1. Add Additional Tables and Fields to a Query
The previous query was based on the Accounts table, but now you want to add information to the query from the Branch and Customers tables. You will add the Branch and Customers tables along with some additional fields from these tables to the query.

a. Open a02h3Bank_LastFirst if you closed it at the end of Hands-On Exercise 3, and save it as a02h4Bank_LastFirst, changing h3 to h4.
b. Right-click the Accounts from Campus Branch query in the Navigation Pane and select Design View from the shortcut menu.
c. Drag the Branch table from the Navigation Pane to the top pane of the query design window to the right of the Accounts table.
A join line connects the Branch table to the Accounts table. The tables in the query inherit the relationship created earlier in the Relationships window.
d. Drag the Location field from the Branch table to the first empty column in the design grid. The Location field should be positioned to the right of the Balance field.
e. Click the Show check box below the BranchID field to deselect it and hide this field from the results.
The BranchID field is no longer needed in the results because the Location field provides the branch name instead. Because you deselected the BranchID Show check box, the BranchID field will not display the next time the query is run.
f. Delete the B50 criterion in the BranchID column.
g. Type Campus as a criterion in the Location field and press Enter.
Access adds quotation marks around Campus for you because Campus is a text criterion. You are substituting the Location criterion (Campus) in place of the BranchID criterion (B50).
h. Click in the AccountID field Sort row, click the arrow, and then select not sorted. Click in the Sort row of the Balance field. Click the arrow and select Descending.
i. Click Run in the Results group.
The BranchID field does not display in Datasheet view because you hid the field in Step e. Only Campus accounts display in the datasheet (10 records). Next, you will add the Customers LastName field to, and delete the CustomerID field from, the query.
j. Save the changes to the query design.
k. Click View in the Views group to return to Design view. Point to the column selector at the top of the BranchID field, and when an arrow displays, click to select it. Press Delete.
The BranchID field has been removed from the query design grid.
l. Drag the Customers table from the Navigation Pane to the top pane of the query design window and reposition the tables so that the join lines are not blocked (see Figure 2.49). The join lines automatically connect the Customers table to the Accounts table (similar to Step c above).
m. Drag the LastName field in the Customers table to the second column in the design grid. The LastName field should be positioned to the right of the AccountID field.
n. Click the column selector in the CustomerID field to select it. Press Delete.
The CustomerID field is no longer needed in the results because you added the LastName field instead.

o. Click Run in the Results group.
The last names of the customers now display in the results.
p. Save and close the query.

2. Create a Multitable Query from Scratch
You realize that another query is needed to show those customers with account balances of $1,000 or less. You create the query and view the results in Datasheet view. Refer to Figure 2.50 as you complete Step 2.

a. Click the Create tab and click Query Design in the Queries group.
b. Double-click the Branch table name in the Show Table dialog box. Double-click Accounts and Customers so that all three are added to Design view. Click Close in the Show Table dialog box.
Three tables are added to the query. The join lines were set earlier in the Relationships window.
c. Double-click the following fields to add them to the query design grid: LastName, FirstName, Balance, and Location.
d. Type <=1000 in the Criteria row of the Balance column.
e. Click Run in the Results group to see the query results. Six records that have a balance of $1,000 or less display.
f. Click Save on the Quick Access Toolbar and type Balance 1000 or Less as the Query Name in the Save As dialog box. Click OK.
g. Close the query.

3. Modify a Multitable Query
You decide to make additional changes to the Balance 1000 or Less query you just created. You will create a copy of the existing query and modify the criteria to display the accounts that were opened on or after January 1, 2011, with balances of $2,000 or less. Refer to Figure 2.51 as you complete Step 3.

a. Right-click the Balance 1000 or Less query in the Navigation Pane, and from the shortcut menu, select Copy.
b. Right-click in the empty space of the Navigation Pane and select Paste.
c. Type the name Open Date 2011 or Later for the new query in the Paste As dialog box and click OK.
d. Double-click the Open Date 2011 or Later query in the Navigation Pane. Click View in the Views group to switch the query to Design view.
e. Type <=2000 in place of <=1000 in the Criteria row of the Balance field and press Enter.
f. Double-click the Open Date field in the Accounts table in the top pane of the query design window to add it to the first blank column in the design grid.
g. Type >=1/1/2011 in the Criteria row of the Open Date field and press Enter to extract only accounts that have been opened since January 1, 2011.
After you type the expression and move to a different column, Access will add the # symbols around the date automatically.
h. Click Run in the Results group to display the results of the query. Five records display in the query results.
i. Click View in the Views group to return to Design view of the duplicate query.
j. Click in the Sort row of the Open Date field and select Ascending.
k. Click Run in the Results group.
The records are sorted from the earliest open date on or after January 1, 2011, to the most recent open date.
l. Save and close the query.

4. Use a Total Row to Summarize Data in a Query
You will to create a query that displays the number of accounts each customer has opened. You create a query using a Total row to summarize the number of accounts per customer. Refer to Figure 2.52 as you complete Step 4.

a. Click the Create tab and click Query Design in the Queries group.
b. Add the Accounts table and the Customers table to the top pane of the query design window. Click Close in the Show Table dialog box.
c. Double-click the CustomerID in the Customers table in the top pane of the query design window to add it to the first blank column in the design grid and double-click the AccountID in the Accounts table to add it to the second column.
d. Click Run in the Results group.
The results show there are 28 records. Every account for every customer is displayed in its own record. You want only the total number of accounts a customer has, so you modify the query to group the records by the CustomerID and summarize the AccountIDs using a function.
e. Click View in the Views group to return to Design view of the query.
f. Click Totals in the Show/Hide group.
Both columns show the Group By option in the Total row.
g. Click Group By in the Total row of the AccountID field and select Count.
The Count function will count the number of Account IDs per customer. You will give the AccountID field a more identifiable description to display in the results.
h. Modify the AccountID field to read Number of Accounts: AccountID.
You typed a descriptive name followed by a colon that will display Number of Accounts in the datasheet when you run the query.
i. Click Run in the Results group. Resize the columns of the datasheet to fully display the results.
The results show one row for each customer and the number of accounts each customer has opened since the database was created.
j. Click Save on the Quick Access Toolbar and type Number of Customer Accounts as the query name. Click OK. Close the query.
k. Close the database and exit Access. Based on your instructor's directions, submit a02h4Bank_LastFirst.

Q3 Employee Salary Analysis

The Morgan Insurance Company offers a full range of insurance services. They store all of the firm's employee data in an Access database. This file contains each employee's name and address, job performance, salary, and title, but needs to be imported into a different existing database. A database file containing two of the tables (Location and Titles) already exists; your job is to import the employee data from Access to create the third table. Once imported, you will modify field properties and set new relationships. The owner of the company, Victor Reed, is concerned that some of the Atlanta and Boston salaries may be below the guidelines published by the national office. He asks that you investigate the salaries of the two offices and create a separate query for each city.

a. Open a02p2Insurance and save it as a02p2Insurance_LastFirst. Double-click the Location table and review the data to become familiar with the field names and the type of information stored in the table. Review the Titles table. Close both tables.
- Click the External Data tab, click Access in the Import & Link group, and then complete the following steps:
- Click Browse and navigate to the a02p2Employees database in the location of your student data files. Select the file, click Open.
• Click OK in the Get External Data - Access Database dialog box.
• Select the Employees table, and then click OK.
• Click Close without saving the import steps.
• Double-click the Employees table in the Navigation Pane, then click View in the Views group on the Home tab to switch to Design view of the Employees table. Make the following changes:
• Ensure that the EmployeeID field is selected, and then click Primary Key in the Tools group.
• Click the LastName field and change the Field Size property to 20.
• Change the Caption property to Last Name.
• Click the FirstName field and change the Field Size property to 20.
• Change the Caption property to First Name.
• Click the LocationID field and change the Field Size property to 3.
• Change the Caption property to Location ID.
• Click the TitleID field and change the Field Size property to 3.
• Change the Caption property to Title ID.
• Change the Salary field data type to Currency and change General Number in the Format property in field properties to Currency.
• Save the design changes. Click Yes to the Some data may be lost warning.

b. Click View in the Views group to view the Employees table in Datasheet view and examine the data. Click any record in the Title ID and then click Ascending in the Sort & Filter group on the Home tab. Multiple employees are associated with the T01, T02, T03, and T04 titles.

c. Double-click the Titles table in the Navigation Pane to open it in Datasheet view. Notice that the T04 title is not in the list.

d. Add a new record in the first blank record at the bottom of the Titles table. Use the following data:
• Type T04 in the TitleID field.
• Type Senior Account Rep in the Title field.
• Type A marketing position requiring a technical background and at least three years of experience in the Description field.
• Type Four year degree in the Education Requirements field.
• Type 45000 in the Minimum Salary field.
• Type 75000 in the Maximum Salary field.

e. Close all tables. Click Yes if you are prompted to save changes to the Employees table.

f. Click the Database Tools tab and click Relationships in the Relationships group, and then Click Show Table. Follow the steps below:
• Double-click each of the three table names in the Show Table dialog box to add it to the Relationships window and close the Show Table dialog box.
• Click and drag to adjust the height of the Employees table so that all fields display in each one.
• Drag the LocationID field in the Location table onto the LocationID field in the Employees table.
• Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes in the Edit Relationships dialog box to select them.
Click Create to create a one-to-many relationship between the Location and Employees tables.
• Drag the TitleID field in the Titles table onto the TitleID field in the Employees table (move the field lists by clicking and dragging their title bars as needed so that they do not overlap).
• Click the Enforce Referential Integrity and Cascade Update Related Fields check boxes in the Edit Relationships dialog box to select it. Click Create to create a one-to-many relationship between the Titles and Employees tables.
• Click Save on the Quick Access Toolbar to save the changes to the Relationships window and close the Relationships window.

g. Click the Create tab and click the Query Wizard in the Queries group. Follow the steps below:
• Select Simple Query Wizard and click OK.
• Select Table: Employees in the Tables/Queries box.
• Double-click LastName in the Available Fields list to move it to the Selected Fields list.
• Double-click FirstName in the Available Fields list to move it to the Selected Fields list.
• Double-click LocationID in the Available Fields list to move it to the Selected Fields list.
• Click Next.
• Type Employees Location as the query title and click Finish.
• Click View in the Views group on the Home tab to switch to Design view of the query. Click and drag the Titles table from the Navigation Pane into the top pane of the query design window.
• Double-click Title in the Titles table to add the field to the query design grid.
• Click the Sort cell of the LocationID field, click the arrow, and then click Ascending.
• Click Run in the Results group (311 records display in the Datasheet sorted by LocationID in ascending order). Double-click the border to the right of each column to set the column widths to Best Fit.
• Save and close the query.
h.
i. Click the Create tab and click the Query Wizard in the Queries group. Follow the steps below:
• Select Simple Query Wizard and click OK.
• Select Table: Location in the Tables/Queries box.

• Double-click Location in the Available Fields list to move it to the Selected Fields list.
• Select Table: Employees in the Tables/Queries box.
• Double-click LastName, FirstName, and Salary.
• Select Table: Titles in the Tables/Queries box.
• Double-click MinimumSalary and MaximumSalary. Click Next.
• Ensure that the Detail (shows every field of every record) option is selected, and click Next.
• Type Atlanta Salaries as the query title and click Finish.

j. Click View in the Views group on the Home tab to switch to Design view of the Atlanta Salaries query.
• Click in the Criteria row of the Location field, and type Atlanta. Click the Sort cell of the Salary field, click the arrow, and then click Ascending.
• Click Run in the Results group. Review the data to determine if any of the Atlanta employees have a salary less than the minimum or greater than the maximum when compared to the published salary range. These salaries will be updated later.
• Save and close the query.

k. Right-click the Atlanta Salaries query in the Navigation Pane and from the shortcut menu, select Copy. Right-click a blank area in the Navigation Pane and select Paste. In the Paste As dialog box, type Boston Salaries for the query name. Click OK.

l. Right-click the Boston Salaries query in the Navigation Pane and select Design View. In the Criteria row of the Location field, replace Atlanta with Boston.
• Click Run in the Results group. Review the data to determine if any of the Boston employees have a salary less than the minimum or greater than the time
• Modify some data that have been incorrectly entered. In the query results, for the first employee, Frank Cusack, change the salary to $48,700.00; for Brian Beamer, $45,900.00; for Lorna Weber, $45,700.00; for Penny
Pfleger, $45,800.00.
• Save and close the query.

m. Close the database and submit a02p2Insurance_LastFirst.

Attachment:- Computer Applications.rar