Ex19 Ac Ch05 Grader Cap As Instructions

Ex19 Ac Ch05 Grader Cap As Instructions

Work as a database administrator at the Paterson Credit Union to modify a database with validation, lookup fields, and input masks. You will also modify queries to enhance their functions and features. Tasks include creating tables, setting required fields, adding validation rules and input masks, enabling lookup wizards, adding parameter criteria, creating calculated fields, grouping data, and classifying data based on specific thresholds. Save and close the database after completing all tasks, then submit the database as instructed.

Sample Paper For Above instruction

The task involves comprehensive modifications to an existing Access 2019 database for the Paterson Credit Union, aimed at improving data integrity, usability, and analytical capabilities. The process begins with creating a new table called AccountTypes, which catalogs the different account types—Platinum, Silver, and Gold—each identified by a primary key, AccountType. This helps standardize account type data and prevents errors associated with free-text entries. After creating and populating this lookup table, the focus shifts to enforcing data requirements: setting the PhoneNumber and AccountType fields in the Customers table as required fields, thus ensuring critical customer information is always provided during data entry.

Key to data validation is restricting loan interest rates to acceptable ranges. By editing the Loans table, a validation rule is established on the InterestRate field requiring values to be between 2.0% and 10.25%. An associated validation text provides clarity for users. To test this, the interest rate in the first record is temporarily changed to a value outside the range, prompting the validation message, and then restored. This step ensures the rule's functionality is intact. To further improve data entry accuracy, an input mask is added to the PhoneNumber field in Customers, applying a standardized format that facilitates consistent data entry and reduces errors.

Enhancing user experience, the AccountType field is transformed into a lookup field referencing the AccountTypes table, allowing users to select account types from a dropdown menu rather than typing. The default sorting and presentation options are accepted, simplifying the process for end-users. Testing involves changing the account type to Platinum for the first customer, verifying that the lookup functions correctly. Additionally, a parameterized query named Customer Loans Parameter is developed to enable dynamic filtering of loan records based on a minimum loan amount entered at runtime. The query prompts for this threshold, and upon execution with input 250000, it accurately filters records with loan amounts above this value. The query’s summary features, such as total sums and averages at the bottom, are activated to facilitate financial analysis.

The next step addresses data completeness by creating a query to identify customers missing address data. By adding a calculated column called AddressPresent that displays "Missing" when an address is null and nothing otherwise, the query can filter to show only customers with missing addresses. Results are verified, ensuring the filtering works correctly. Further, the Loans By Interest Rate query is modified to include a calculated RoundedRate field that rounds each loan’s interest rate to the nearest whole number, enabling straightforward analysis of interest rate distributions.

Another analytical enhancement involves summarizing loan payment data monthly. Using the DatePart function, the Payment By Month query groups payments by month extracted from PaymentDate, then calculates total and average payments for each month. The grouping and summary functions are tested to confirm that they produce accurate monthly summaries, such as a total of $5,246.51 and an average of $1,311.63 in payments for February, with totals and averages displayed at the bottom of the respective columns.

Finally, a classification system within the Refinance Candidates query is implemented to flag high-priority loans based on interest rates. By adding a Priority field that assigns "High" to loans with interest rates at or above 7.9%, and "Low" otherwise, the database visually prioritizes loans requiring attention. This classification assists credit union staff in managing loan portfolios efficiently. Once completed, the database is saved, closed, and prepared for submission as specified. These steps improve data accuracy, facilitate analysis, and support informed decision-making at the credit union.

References

• Allen, L. (2020). Mastering Microsoft Access 2019. Packt Publishing.
• Belliveau, J. (2021). The definitive Guide to Microsoft Access. Microsoft Press.
• Harrington, J. (2017). Relational Database Design and Implementation. Morgan Kaufmann.
• Simons, A. (2019). Database Systems: A Practical Approach to Design, Implementation, and Management. Pearson.
• Ryan, P., & McGee, P. (2018). Microsoft Access 2019 Programming by Example with VBA, XML, and ASP. Shelly Cashman Series.
• Rob, P., & Coronel, C. (2018). Database Systems: Design, Implementation, & Management. Cengage Learning.
• O’Neil, P., & O’Neil, E. (2017). Database: Principles, Programming, and Performance. Morgan Kaufmann.
• Schneider, G. P. (2022). An Introduction to Database Systems. Addison-Wesley.
• Vander Weyden, P. (2019). Practical Microsoft Access 2019. Apress.
• Paul, R., & Kathleen, M. (2020). Access 2019 Bible. Wiley Publishing.