Paterson Credit Union
You work as a database administrator at the Paterson Credit Union. You are modifying a database to add validation, lookup fields, and an input mask. You will also modify queries to take advantage of advanced functions and features.
For the purpose of grading the project you are required to perform the following tasks:
Start Access. Open the file namedexploring_a05_grader_a1_Bank.accdb.
Use Design view to create a new table. AddAccountType as the first field name, with data type Short Text and field size 10. Ensure AccountType is set as the primary key. Save the table and name it AccountTypes. Add three records: Platinum, Silver, and Gold. Close the table.
Open the Customers table in Design view. Set the PhoneNumber and AccountType fields to Required. Save the table. Close the table.
Open the Loans table in Design view. Establish a validation rule for the InterestRate field that requires the value to be greater than or equal to 2.5 but less than or equal to 10. Create validation text for the InterestRate:Value must be between 2.5 and 10. Save the table. Change the InterestRate in the first record to 2. The validation text appears. Press Esc to restore the original value. Close the Loans table.
Open the Customers table in Design view. Add a phone number input mask for the PhoneNumber field, storing the symbols with the data.
Change the Data Type of the AccountType field to Lookup Wizard. Use the AccountTypes table for the values in the lookup field, select the AccountType field from the table, accept the default sort, accept default column widths, and then accept the default nameAccountType. Save the table. Switch to Datasheet view.
Change the account type to Platinum in the first record and click the second record. Close the table.
Open the Customer Loans Parameter query in Design view. Add criteria for the Amount field. The user should be prompted to Enter Minimum Loan Amount. The query should display all results that greater than or equal to the threshold. Run the query. Enter 400000when prompted to Enter Minimum Loan Amount. You should have two results. Save and close the query.
Open the Missing Addresses query in Design view. Add a new column to determine if a customer does not have an address on file. If the customer’s Address is null, it should display Missing. If not, it should display nothing. Name the column AddressPresent. Add criteria ofMissing to the column you just created, so only the customers missing an address display. Move the AddressPresent field so it appears between PhoneNumber and Address. Run the query. Ensure only customers with null Address fields display. Save and close the query.