Access 2019 Project Ch03 Assessment
access 2019 Projectexp19 Access Ch03 Capassessme
Grader Instructionsaccess 2019 Projectexp19 Access Ch03 Capassessme
Grader - Instructions Access 2019 Project Exp19_Access_Ch03_CapAssessment - Retirement Accounts 1.2 Project Description: One-Stop Finance is a company that works with Clients in all areas of finance. They assist with banking, financial planning, mortgages, stock, insurance, retirement counseling, and debt consolidation. Cala Hamieh, one of the senior planners for the company, is hoping to extract information from the database. The company has a large database with hundreds of thousands of accounts, but to allow you to rapidly test your queries, you have created a smaller version of the database with a small amount of clients. Once you are confident your queries work, you can import them into the main Access database.
Steps to Perform: Step Instructions Points Possible 1 Start Access. Open the downloaded Access file named Exp19_Access_Ch03_CapAssessment_Retirement_Accounts.accdb. Grader has automatically added your last name to the beginning of the filename. Create a query using Query Design. From the Clients table, display the client’s FirstName and LastName. From the Accounts table, select the Savings Balance and OpenDate. Sort the query by Savings Balance in descending order. Save the query as Account Longevity. Add a calculated field named AccountTime that calculates the number of days each client’s accounts have been open. Assume today’s date is 12/31/2019. Recall dates must be enclosed in # to denote to Access it is a date. Format the results in General Number format. Run the query. Close the query. Create a query using Query Design. From the Clients table, display the client's FirstName and LastName. From the Accounts table, select the Savings Balance. Add appropriate grouping so the client’s total retirement account savings balances are displayed. Add a sort so the highest total savings balances are displayed first. Switch to Datasheet view. Add a totals row displaying the count of the last name and the average of total savings balances. Save the query as Total Balances By Client, and close the query. Create a copy of the Total Balances By Client query. Name the query Total Balances By State. Open the query in Design view and remove the client name from the query. Add grouping by the client’s State. Sort by the client’s State in Ascending order and remove the sort on the Savings Balance. Add criteria so clients with retirement account savings balances of $12,000 or more are factored into the query. Switch to Datasheet view. Save and close the query. Create a new query using Query Design. From the Clients table, select the client FirstName, LastName, and State. From the Accounts table, select the Savings Balance. Add criteria so only customers with balances under $13,000 are displayed. Add a new field named LoanPayment using the Expression Builder. Insert the Pmt function to determine the monthly payment for a 2-year loan, paid monthly, with a 5% yearly interest rate. The present value is 25000 minus the Savings Balance. For example, if the purchase price were 25000, with 5000 in savings to put toward the purchase, your present value would be 20000. Ensure the number displays as a positive number. Change the format of the LoanPayment field to Currency. Change the caption to Loan Payment. Run the query. Save the query as Monthly Loan Payments and close the query. Close all database objects. Close the database and then exit Access. Submit the database as directed. 0 Total Points 100 Created On: 07/12/2019 1 Exp19_Access_Ch03_CapAssessment - Retirement Accounts 1.2 RegistrationData First Name & Middle Initial First Name Registration Date Cleansed Registration Date Phone Number Cleansed Phone Number Carter X Jane Fitzgerald K Arden W Zeph Lilah Bruno L Kiara Yardley Y Maxine Walter M Dexter Asher Ruby I Keaton T Kiona Kermit V Christen Coby Cullen F Desiree J Seth Alden Germaine Maxwell E CustNames Name Clean Proper Trim Find Last Name First Name & Middle Initial Full Name GAINES THOMAS M. Stone Jerome Cannon Simone Z Campbell Reece H. Chase Stafford Mcclain Jack JONES BOBBY J. Davis Tanner Murray Colt Q.
GIFFORD MARJORIE HANKINS ELIZA S. Branch Anthony Bray Brian JOYCE DAVID J. KRAUS CAROL T. LAMB SAMANTHA Kirk Jocelyn B. LEWIS PATRICK Gentry David PREWITT HANNAH E. RANGELL JOHN W. RUSSO ROBERT TATE ANDREA M. THOMPSON SHARON WOODFORD VERA T. E-mail Customer ID Full Address Find Colon Length Email Create an email list separated by semi-colons 1 Charles Barker mailto: < [email protected] > 2 Missy Malone mailto: < [email protected] > 3 Susan Winter mailto: < [email protected] > 4 Jose Rodriquez mailto: < [email protected] > 5 Nicole Rodriquez mailto: < [email protected] > 6 Brenden Erickson mailto: < [email protected] > 7 Kaseem Castillo mailto: < [email protected] > 8 Courtney Miller mailto: < [email protected] > 9 Fiona Britt mailto: < [email protected] > 10 Robert Allen mailto: < [email protected] > 11 John Trujillo mailto: < [email protected] > 12 Ian McShane mailto: < [email protected] > 13 Marge Fresquez mailto: < [email protected] > 14 Marcus Maestas mailto: < [email protected] > 15 Janice Marquardt mailto: < [email protected] > 16 Keith Marquardt mailto: < [email protected] > 17 Kate Neidhart mailto: < [email protected] > 18 Thomas Reed mailto: < [email protected] > 19 Cynthia Reid mailto: < [email protected] > 20 Joseph McMannon mailto: < [email protected] > Members Names First Name Last Name Thomas Gaines Jerome Stone Simone Cannon Reece Campbell Stafford Chase Jack Mcclain Bobby Jones Tanner Davis Colt Murray Marjorie Gifford Eliza Hankins Anthony Branch Brian Bray David Joyce Lessons LastName ScheduledDate Fee Winter 7/22/22 $175.00 Barker 6/8/22 $175.00 Miller 5/27/22 $145.00 Malone 6/8/22 $200.00 Barker 6/17/22 $325.00 Castillo 5/30/22 $75.00 Rodriquez 5/11/22 $200.00 Miller 5/1/22 $175.00 Miller 5/27/22 $145.00 Britt 5/17/22 $125.00 Rodriquez 5/12/22 $200.00 Barker 6/2/22 $100.00 Castillo 5/31/22 $300.00 Winter 7/23/22 $300.00 Barker 6/8/22 $175.00 Allen 6/10/22 $300.00 Rodriquez 5/11/22 $200.00 Invoices Invoice Numbers Grader - Instructions Excel 2019 Project YO19_Excel_CH09_Prepare_Data_Integration_PartB Project Description: The Red Bluff Golf Course & Pro Shop manager, Aleeta Herriott, has asked you to create a report that analyzes costs and revenues from tournaments hosted over the past year. In the past, her staff had to reenter data manually from different sources to create this report because no one at the resort knew how to import the data. As a result, they rarely completed the report. Aleeta worries about the accuracy of the reports that were compiled because of the manual data entry. However, she did keep all the original files. Recently, a new Golf database was created to track sales and allow for easy export to Excel for analysis. Aleeta wants you to design a spreadsheet that will help her automate the process of gathering and standardizing the data from the past for analysis. Steps to Perform: Step Instructions Points Possible 1 This exercise begins on page 519 of your text. Start Excel. Download and open the file named Excel_Ch09_Prepare_DataIntegration2of3.xlsx. Grader has automatically added your last name to the beginning of the filename. A list of customer information on a tournament golf club giveaway has been provided in the Excel_Ch09_Prepare_DataIntegration2of3.xlsx workbook. The data needs to be cleansed before it can be used for purposes such as analysis or merging to a Word document. On the RegistrationData worksheet in column B, use Flash Fill to separate the first name from the name in column A. 0. The dates in column C are not stored in date formats that Excel can use. In column D, use Flash Fill to convert the data in column C into a month/day/year date format e.g., 4/13/2022 , 3/18/2022 , etc. 0. The phone numbers provided in column E are not in a standard phone number format. In order to include these phone numbers in mail merges or other professional formats they need to be formatted using a hyphen. In column F, use Flash Fill to convert the string of numbers in column E to include a hyphen in between the first 3 digits and the last 4 digits e.g., , , etc. 0. The customer names on the CustNames worksheet were imported with non-printable characters, extra spaces, and in inconsistent formats. The data needs to be cleansed to appear as first name, middle initial (if present), and last name. On the CustNames worksheet, in column B, use the appropriate text function to clean all the nonprinting characters from the data in column A. Resize the column as needed to fit the contents. 0. The names should also be proper cased. In column C, use the appropriate text function to convert the data in column B into proper case. Resize the column as needed to fit the contents. 0. In cell D2, use the appropriate text function to remove any extra spaces before or after as well as between the names in column C. Resize the column as needed to fit the contents. 0. In column E, use the appropriate text function to return the position of the space character located in the data in column D. Resize the column as needed to fit the contents. 0. In column F, use the appropriate text function to extract only the last name from column D. Use the number in column E as the relative starting point for the number of characters needed to extract only the last name from the left side of the data in column D (no extra spaces). Resize the column as needed to fit the contents. 0. In column G, use the appropriate text functions to extract only the first name and middle initial from column D. Use the value in column E as part of the calculation to determine the number of characters needed to extract only the first name and middle initial from the right side of the data in column D. Resize the column as needed to fit the contents. 0. In column H, use the ampersand symbol ( & ) to concatenate the first name and middle initial in column G and the last name in column F with a space in between. Resize the column as needed to fit the contents. 0. The E-mail worksheet contains the names and email addresses of customers. In order to use the e-mail addresses to communicate with customers, they need to be separated from the rest of the associated text and then joined together so that they can be copied and pasted into an e-mail address field. In column C, use the appropriate text function to return the position of the colon character in column B. Resize the column as needed to fit the contents. 0. In column D, use the appropriate text function to calculate the total number of characters in each cell of column B. Resize the column as needed to fit the contents. 0. In column E, use the appropriate text function to extract the e-mail address from column B. Use the number in column C as the relative starting point for the number of characters needed to extract only the e-mail address from the left side of the address data in column B (no colon and no angle brackets). Resize the column as needed. 0. In cell G2, use the appropriate text function to create an e-mail list that combines the e-mail addresses in column E using a semi-colon and a space ("; ") for a delimiter. Use the appropriate argument to ignore any blank cells. 0. Additional customer names have been provided on the Members worksheet. They need to be separated into first and last names for communications with those customers. On the Members worksheet, use the Text to Columns Wizard to separate the names in column A so that the first names are in column B and the last names in column C. 0. A list of customers that received golf lessons is contained on the Lessons worksheet. There are duplicate records in the data that need to be removed. On the Lessons worksheet, use Remove Duplicates to remove any records with the same LastName, ScheduledDate, and Fee. 0. The Invoices worksheet contains a listing of invoice numbers. Any duplicate records need to be identified in the data. On the Invoices worksheet, use conditional formatting to highlight the data in column A so that duplicate values appear with a Light Red Fill with Dark Red Text. Turn on filtering for the data and sort by cell color. 0. Save and close Excel_Ch09_Prepare_DataIntegration2of3. Exit Excel. Submit your files as directed. Please note: This project continues in part C which is a separate Grader project. 0 Total Points 10 Created On: 12/18/2019 1 YO19_Excel_CH09_Prepare - Data Integration Part B 1.0
Sample Paper For Above instruction
access 2019 Projectexp19 Access Ch03 Capassessme
The project involves creating and analyzing queries within Microsoft Access to extract meaningful information about clients’ retirement accounts and account longevity. Additionally, the task includes importing and cleansing data in Excel for further analysis related to a golf course's tournament costs and revenues. This comprehensive project requires proficiency in Access query design, calculated fields, Grouping, Sorting, and criteria application, as well as advanced Excel data cleansing techniques such as using Flash Fill, Text functions, Remove Duplicates, and Conditional Formatting.
Part 1: Access Database Queries for Retirement Accounts
The initial steps involve opening the specified Access database file and creating multiple queries in Design View to analyze client data. The first query, titled "Account Longevity," lists clients’ first and last names along with their savings balance and account opening date. It calculates the number of days each account has been open by subtracting the open date from a fixed date of December 31, 2019, using date functions enclosed within # symbols to denote date literals.
This calculated field allows Cala Hamieh, the senior financial planner, to assess how long clients hold their savings accounts, which aids in retirement planning strategies. The query results are sorted by savings balance in descending order, highlighting clients with the highest balances.
The second query, "Total Balances By Client," groups client data to display total savings balances by each client, including a count of unique last names and the average of total balances. Sorting is applied to prioritize clients with the highest total balances, providing insights into the most significant accounts.
To analyze data by geographic location, a third query "Total Balances By State" is created by copying the previous query and removing client-specific identifiers. It groups data by state, filters clients with balances exceeding $12,000, and sorts alphabetically by state, facilitating regional analysis of retirement savings.
A fourth query targets customers with balances under $13,000, displaying their details and calculating monthly loan payments. The loan payment calculation employs the PMT function, considering a 2-year loan, with monthly installments, an annual interest rate of 5%, and the principal based on the purchase price minus savings. The results are formatted as currency for easy interpretation.
Finally, all database objects are closed, and the database is exited as part of the submission process.
Part 2: Excel Data Cleansing and Organization
The second component involves processing customer data in Excel. The provided workbook contains multiple sheets with raw data that require cleansing to prepare for analysis and communication. Techniques include using Flash Fill to extract first names, convert dates, and format phone numbers into standard formats with hyphens.
On the "CustNames" worksheet, text functions such as CLEAN, PROPER, TRIM, FIND, LEFT, RIGHT, and CONCATENATE are used to remove non-printable characters, capitalize names properly, remove unwanted spaces, locate name parts, and assemble full names with proper formatting.
The "E-mail" worksheet is processed to separate email addresses from concatenated text using FIND and MID functions, then creating an email list string using TEXTJOIN with semicolons, ignoring blanks. These steps ensure consistent and efficient communication with clients.
Additional customer names are organized via the Text to Columns wizard on the "Members" worksheet, splitting full names into first and last names for targeted outreach.
Duplicate records in the "Lessons" worksheet are identified and removed using the Remove Duplicates feature, based on combined criteria like last name, scheduled date, and fee. A similar process highlights duplicates in invoice data on the "Invoices" worksheet using Conditional Formatting, with sorting by cell color to prioritize review.
All configurations and cleansed datasets are saved, and the Excel file is closed for submission. These meticulous data processing steps enable accurate analysis, reporting, and communication strategies for the golf course and client database management.
Conclusion
This dual-faceted project demonstrates advanced proficiency with Access queries—covering calculation, grouping, sorting, and filtering—and robust data cleansing techniques in Excel. Mastery of these tools ensures precise data extraction, analysis, and communication, critical in both financial planning and operational analysis within organizations like One-Stop Finance and the Red Bluff Golf Course.