Exp$19\_$Access$\_$Ch$05\_$CapAssessment $-$ Paterson Credit Union $1\mathrm{.}2$

Project Description:

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.

Steps to Perform:

Step Instructions Points Possible

$1$ Start Access. Open the file named Exp$19\_$Access$\_$Ch$05\_$CapAssessment$\_$Paterson$\_$Credit$\_$Union.accdb. Grader has automatically added your last name to the beginning of the filename. $0$

$2$ You want to make sure that the customer account types are documented and stored correctly. To do this you will create a table that will list each account type.

Use Design view to create a new table. Add AccountType 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. Save and close the table. $4$

$3$ Now, you wish to ensure that, when customers are added to your database, the phone number and account type must be entered. To do this you will set the PhoneNumber and AccountType fields as required fields.

Open the Customers table in Design view. Set the PhoneNumber and AccountType fields to Required. Save and close the table. $6$

$4$ Paterson Credit Union only offers loans with interest rates between $2\mathrm{.}0\%$ and $10\mathrm{.}25\%.$ To ensure that no loans are offered outside of those constraints you will add a validation rule that will not allow loans outside of that range to the InterestRate field in the Loans 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\mathrm{.}0$ but less than or equal to $10\mathrm{.}25.$ Create validation text for the InterestRate: Value must be between $2$ and $10\mathrm{.}25($no period$).$ Save the table and switch to Datasheet view. Change the InterestRate in the first record to $1\mathrm{.}9.$ The validation text appears. Press ESC to restore the original value. Close the Loans table. $8$

$5$ You$$ve made the PhoneNumber field required in the Customers table, but now you want to ensure that phone numbers are entered in a specific format. To do this you will add an input mask to the PhoneNumber field in the Customers table.

Open the Customers table in Design view. Add a phone number input mask for the PhoneNumber field, storing the symbols with the data. $16$

$6$ You would like to easily add the account type for each customer without typing anything on your keyboard. To do this you will turn the AccountType field into a Lookup Wizard using the AccountTypes table, that you recently created, as the source.

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 name AccountType. Save the table. Switch to Datasheet view. $8$

$7$ Change the account type to Platinum in the first record. Close the table. $2$

$8$ For ease of use, you would like for users to be able to indicate the minimum loan amount on which they would like to pull loan information. You will do this by adding a parameter criterion to the LoanAmount field in the Customer Loans Parameter query.

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 $($no period$).$ The query should display all records that have a loan Amount that is greater than or equal to the value entered as the parameter. Run the query. Enter $250000$ when prompted to Enter Minimum Loan Amount. You should have five results. Ensure that the query results display a total at the bottom of the Date column, and an average at the bottom of the Amount column. Save and close the query. $16$

$9$ You have noticed that a few of your customers are missing address information. You would like to address this by creating a query that returns only the customers that are missing addresses so that you can update that information. You will complete this by adding a field that indicates whether an address is missing then adding criteria to that field so that only customers with missing addresses are returned.

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 of Missing 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. $10$

$10$ For simplicity, you are now interested in rounding the interest rates for each loan to the nearest whole number.