Credit Weight: 25% of your final grade

You must download data files to complete each textbook exercise. Refer to the Assignment 3 Dropbox page to locate these files.

Instructions: General

Each exercise will require you to create or modify one or more files. The assignment instructions state exactly how the file names for the results should be constructed. For example, for Assignment 3, the instructions require the input file Acc1Loans.accdb.

Save this file as Acc1Loans_LastFirst.accdb

Replace LastFirst with your name, e.g., Acc1Loans_ SmithJane.accdb

Each exercise will specify which created or modified files should be submitted for marking.

Access Exercise 1: Small Business Loans

You are employed as a technical supervisor at a lending firm for small business loans. You will learn how to populate an Access table, work with a form that can be used to add, delete, and modify member loan records, apply a filter to a table, and locate loan officer information through a query.

Modify Data in a Table

You will open an original database file and save the database with a new name. You will then modify, add, and delete information using tables and forms.

1. Open the Acc1Loans.accdb file and save the database as Acc1Loans_LastFirst.accdb.
2. Open the Loan Officers table in Datasheet view. Update the database with the information below.

First Name	Last Name	Email Address	Phone Ext	Title
John	Badman	john_badman@loanofficer.com	x1757	Loan Officer
Stan	Dupp	stan_dupp@loanofficer.com	x6720	Senior Loan Officer
Herb	Avore	herb_avore@loanofficer.com	x2487	Loan Officer
Polly	Esther	polly_esther@loanofficer.com	x8116	Senior Loan Officer
Strawberry	Fields	strawberry_fields@loanofficer.com	x3219	Loan Officer
Ann	Serdifone	ann_serdifone@loanofficer.com	x5962	Managing Loan Officer

• Close the Loan Officers table.
• Open the Loans table in Datasheet view. Add a new record with the following information:

OfficerID:	5
MemberID:	15
LoanAmount:	7,000
Term:	36 months
InterestRate:	15.41%
Payment:	$244.07
Grade:	D
IssueDate:	12/15/21
LoanStatus:	Late (31–120 days)

• Open the Maintain Members form. In record 3 (for Brynn Anderson, MemberID 13), add a new loan to the subform:

OfficerID:	5
LoanAmount:	17,000
Term:	36 months
InterestRate:	4.35%
Payment:	$300.45
Grade:	B
IssueDate:	9/1/21
LoanStatus:	Fully Paid

• Use the Navigation bar to search for MemberID 16 and edit the subform so that the InterestRate is 0.1254 instead of 0.1899 for the LoanID 47.
• Close the Maintain Members form.

Sort a Table and Use Filter By Form

You will sort the Loan table and apply a filter to display only loans with an interest rate less than 11% and a term of 36 months.

• Sort the records in the Loans table by the IssueDate field in descending order (newest to oldest).
• Use the Filter By Form feature to apply filters to display only loans that have a rate of less than 11% (<0.11) and a term of 36 months.
• Close the table and save the changes.

Apply a Selection Filter and Sort a Query

You are interested in quickly filtering the data in the Loans, Officers, and Members query based on a specific loan officer. You will then sort the filtered results to view the loans by loan status.

1. Open the Loans, Officers, and Members Query in Datasheet view.
2. Use a Selection filter to show only the loans managed by the loan officer whose name is John Badman.
3. Sort the query by LoanStatus in alphabetical order. Save and close the query.

Back Up a Database and Add an Application Part

You will first back up the database to reinforce the importance of backing up stored data. You will also add an application part to the database.

1. Create a backup copy of your database, accepting the default file name.
2. Add a Comments application part, selecting the option One ‘Loans’ to many ‘Comments’.  Select the LoanStatus field as the field from the Loans table and sort this field ascending. Name the lookup column Status and then click on the Create command button.
3. Open the Comments table. In row 1, enter Current in the Status column, select 12/23/2021 for the Comment Date, and enter the comment Loan 47 rate changed from 18.99% to 12.54%. 
4. Click on the Click to Add column heading and select the Short Text option. Change the Field1 column heading to Explanation.Enter the text Entry error in the Explanation column in row 1.
5. Close the Comments table. Right-click on the Comments table in the navigational pane and rename it Interest Rate Changes.
6. Close the database and exit Access.
7. Submit the Acc1Loans_LastFirst.accdb file and its backup file for marking.

Access Exercise 2: Fantastic Foods

Fantastic Foods is an importer of exotic foods from all over the world. You landed a summer internship with the company and discovered that their product lists and the suppliers they buy from are stored in Excel workbooks. You offer to help by using your newly gained knowledge of Access to create a relational database for them.

You will begin by importing the workbooks from Excel into a new Access database. Your manager mentions that she also wants a table that specifies food categories so that it is possible to relate products sold to specific categories in the database. You will create a table from scratch to track categories, create relationships between the tables, and create some baseline queries.

Create a New Database

You will examine the data in the Excel worksheets to determine which fields will become the primary keys in each table and which fields will become the foreign keys so that you can join them in the Relationships window.

1. Open the Acc2Suppliers.xlsx Excel workbook, examine the data, and then close the workbook.
2. Open the Acc2Products.xlsx Excel workbook, examine the data, and then close the workbook.
3. Create a new, blank database named Acc2Foods_LastFirst.accdb. Close the new blank table created automatically by Access without saving it.

Import Excel Data to the Suppliers Table

You will import the Excel workbook that contains supplier information into a Suppliers table.

• Click the External Data tab. Click New Data Source, point to From File in the Import & Link group and then select Excel.
• Navigate to and select the Acc2Suppliers.xlsx workbook to be imported as a new table in the current database.
• Place a check mark in the First Row Contains Column Headings option.
• Set the SupplierID field Indexed option to Yes (No Duplicates).
• Select SupplierID as the primary key when prompted and accept the table name Suppliers. Do not save the import steps.
• Open the Suppliers table in Datasheet view to examine the data. Close the table.

Import Excel Data to the Products Table

You will import the Excel workbook that contains the product information into the Products table.

1. Import the Acc2Products.xlsx workbook, set the ProductID Indexed option to Yes (No Duplicates), and then select ProductID as the primary key.
2. Accept the table name Products.
3. in Design view, change the Field Size of the QuantityPerUnit field to 25. Set the Field Size of the ProductID and CategoryID fields to Long Integer.
4. Save the changes. Open the table in Datasheet view to examine its contents. Close the table.

Create a Categories Table

You will create a new table that will enable Fantastic Foods to associate each product with a food category in the database.

1. Create a new table in Design view and save the table as Categories.
2. Add the following fields in Design view and set the properties as specified:
3. Add the primary key field as CategoryID with the Number data type and Number assigned to a new category. (type the period) as the Description. Set the Caption property to Category ID.
4. Add CategoryName with a Short Text data type and Name of food category. (include the period)as the Description. Change its field size to 15. Set the Caption property to Category Name and the Required property to Yes.
5. Add CategoryDescription with the Long Text data type. Set the Caption property to Category Description.
6. Switch to Datasheet view and save the table when prompted. Close the table.

Add Data to the Categories Table

You will add eight records to the Categories table to give you sample data to test in the database.

• Add the following records to the Categories table, and then save and close the table.

Category ID	Category Name	Category Description
1	Beverages	Soft drinks, coffees, teas
2	Condiments	Sauces, relishes, seasonings
3	Confections	Desserts, candies, sweet breads
4	Dairy Products	Cheeses
5	Grains/Cereals	Breads, pasta, cereal
6	Meat/Poultry	Prepared meats
7	Produce	Dried fruit, bean curd
8	Seafood	Seaweed and fish

Create Relationships

You will create the relationships between the Suppliers, Products, and Categories tables using the Relationships window.

1. Add all three tables to the Relationships window.
2. Join the primary key field in the Categories table with its foreign key counterpart in the Products table. Select the Enforce Referential Integrity and Cascade Update Related Fields check boxes.
3. Join the primary key field in the Suppliers table with its foreign key counterpart in the Products table. Select the Enforce Referential Integrity and Cascade Update Related Fields check boxes.
4. Save and close the Relationships window.

Use the Query Wizard

You will use the Simple Query Wizard to create a query of all products that you import in the seafood category.

• Add the ProductName, SupplierID, and CategoryID fields from the Products table in that order.
• Save the query as Seafood Products.
• In Design view, add a criterion to include only products with 8 as the CategoryID. Sort the query results in ascending order by ProductName.
• Run, save, and close the query.

Copy and Modify a Query in Design View

You want to create a query that displays category names rather than their CategoryIDs. This will show which seafoods, meat, and poultry products are currently imported. You will copy the Seafood Products query and modify it by deleting a field, then adding an additional table and field.

• Copy the Seafood Products query and paste it using Seafood Or Meat/Poultry as the query name.
• Open the Seafood Or Meat/Poultry query in Design view and delete the CategoryID column.
• Add the Categories table to the top pane of the query design window. Add the CategoryName field to the last column of the design grid and set its criterion to “Seafood” or “Meat/Poultry”.
• Run, save, and close the query.

Create a Multitable Query

You will create a query that identifies suppliers and their associated products. Because there is a relationship between the two tables, you can now pull data from each of them together as usable information.

• Create a query in Design view that includes the Suppliers and Products tables. The query should list the company name, contact name, phone number (in that order), then the product name and the product cost (in that order).
• Sort the query by company name in ascending order, then by product cost in descending order.
• Run, close, and save the query as Company by Product List.

Use a Total Row to Summarize Data in a Query

You determine that the data in the Company by Product List query could be summarized with a Total row. You will group the records by company name and count the number of products you buy from each of them.

• Copy the Company by Product List query and paste it using Summary of Company by Product as the query name.
• Open the Summary of Company by Product query in Design view and delete the ContactName, Phone, and ProductCost columns.
• Click Totals in the Show/Hide group on the Query Tools Design tab. Click in the Total row of the ProductName field, click the arrow, and then select Count. The records will be grouped by the company’s name, and the products for each company will be summarized.
• Modify the field name of the second column to Product Count: ProductName to make the field name more identifiable.
• Click Run in the Results group. Twenty records should display in the Datasheet results. These display the product count for each company that supplies your organization.
• Save and close the query.

Finalize the Database

• Adjust any column widths in the created queries so that all data is displayed in the datasheet results.
• Close the database and exit Access.
• Submit Acc2Foods_LastFirst.accdb for marking.

Access Exercise 3: Superior Games

Superior Games, a boardgame manufacturer, has hired a new CEO. She has asked for your assistance in providing summaries of data that took place before she started with the company. To help her with her strategic planning, you will create queries to perform data analysis on the sales data.

Based on your meeting, you plan on creating four queries. One query will find orders with minor delays. Another query will summarize company revenue and cost by country. A third query will be used to help evaluate payments made by customers on their orders. The final query will calculate the total sales by sales representative title.

Database File Setup

You will open the Superior Games database and use Save As to make a copy of the database. You will add yourself as a Sales Representative to the employee table.

1. Open Acc3Games.accdb and save the database as Acc3Games_LastFirst.accdb.
2. Open the Sales Reps table. Add yourself as a sales rep. Fill in all information, including the RepID field. Set your Title to Managing Partner, salary to $105,144, and the country field to Canada.
3. Close the Sales Reps table.

Shipping Efficiency Query

You will create a query to calculate the number of days between the date an order was placed and the date the order was shipped for each order. This will allow you to determine which orders took more than 30 days to ship. The salespeople will then be required to review the records and report the source of each order’s delay. The CEO thinks there may be issues with one of the products and would like data to back that up.

• Create a query using Query Design. Add the Customers, Orders, and Product tables. From the Customers table, include the fields FirstName, LastName, and Phone (in that order). From the Orders table, include the fields OrderID, OrderDate, and ShippedDate (in that order).
• Run the query and examine its records. Save the query as Shipping Efficiency.
• Add a calculated field named DaysToShip in column 7 to calculate the number of days taken to fill each order. This expression will include the OrderDate and ShippedDate fields and should result in a positive number.
• Run the query and examine its results. Save the query.
• Add criteria to limit the query results so that only orders that took more than 30 days to ship are included.
• Add the Quantity field from the Orders table and the ProductName field from the Products table to the query (in that order). Sort the query by ascending LastName.
• Add the caption Days to Ship to the DaysToShip field, and then switch to Datasheet view to review the results. Adjust the column widths to display all data.
• Save and close the query.

Revenue and Cost by Country Query

The CEO is considering the financial impact of shipping to other countries. She asked for a query showing the total revenue and total cost for each country to which orders are being shipped. She hopes to understand the distribution of revenue across the world more clearly.

1. Create a query using Query Design by adding the Customers, Orders, and Products tables. Add the Country field from the Customers table. Set the field’s Total row to Group By. Save the query as Revenue and Cost by Country.
2. Add a calculated field named TotalRevenue in the second column. This field should multiply the quantity ordered (from the Orders table) by the unit price for that item (from the Products table). Format the calculated field as Currency and change the caption to Total Revenue. Change the Total row for the TotalRevenue field to Sum.
3. Add a calculated field in the third column named TotalCost. This field should multiply the quantity ordered (from the Orders table) by the unit cost for that item (from the Products table). Format the calculated field as Currency and add a caption of Total Cost. Change the Total row to Sum.
4. Run the query. Examine the results. Save the query and return to Design view.
5. Add the OrderDate field and create criteria for that field so that only orders made between 7/1/2021 and 12/31/2021 are displayed. This expression will display only orders that were completed during the second half of 2021. Change the Total row to Where.
6. Run the query and view the results. Adjust column widths to display all data. Save and close the query.

Customer Payments Query

The CEO wants the salespeople to discuss financing with customers. To assist with this, she asked you to create a query showing the impact on price for prior orders. This way, the reps can give customers a comparison with an order they have already placed. For the moment, she is considering a 5% interest rate, paid over 12 months. She wants you to leave the results as negative numbers.

1. Create a Customer Payments query by adding the Customers, Orders, and Products tables while in Design view.
2. Add the FirstName and LastName from the Customers table.
3. In the third column, create a calculated field called OrderTotal that multiplies the Quantity from the Orders table by the Price Per Unit from the Products table. Change its caption to Order Total.
4. Use the Expression Builder and the following PMT function parameters to add a new field named SamplePayment in the fourth column.
5. Use 0.05/12 for the rate argument (5% interest, paid monthly).
6. Use the number 12 for the num_periods argument (12 months).
7. Use the calculated field [OrderTotal] for the present_value.
8. Use the value 0 for both future_value and type.
9. Change the Total row to Expression for the SamplePayment field. Note: If your version of the Access application yields an aggregate function error, change its Total row to Sum.
10. Change the SamplePayment field’s format to Currency and its Caption to Sample Payment.
11. Run the query and examine the results. Adjust column widths to display all data. Save and close the query.

Revenue by Sales Rep Query

The company is planning to evaluate the success of each tier of sales representative. You will provide a list of total revenue earned by each sales rep tier for years 2020 and 2021. You will add a Total row which will calculate the total revenue generated by all sales representatives.

• Create the Revenue by Sales Rep query by adding the Sales Reps, Orders, and Products tables while in Design view.
• Add the Title field from the Sales Rep table.
• Add the calculated TotalRevenue field to the second column by multiplying the Quantity from the Orders table and the PricePerUnit from the Products table. Change its Caption to Total Revenue, its formatting to Currency and its Total row to Expression. Note: If your version of the Access application yields an aggregate function error, change its Total row to Sum.
• Change the sort order so that the title tier with the highest TotalRevenue is first and the lowest TotalRevenue is last.
• Run the query and examine the summary records of the four sales rep tiers.
• Use the Totals option in the Home tab’s Records Group to add a total row that calculates total revenue for all four tiers. Adjust column widths to display all data.
• Save and close the query. Close the database and exit Access.
• Submit the Acc3Games_LastFirst.accdb file for marking.

Get expert help for Assignment 3: Access and many more. 24X7 help, plag-free solution. Order online now!