Access Final Practice

PART A – Update the table called Client using the structure below.

1. Ensure Client Number field appears in uppercase.

2. Make Client Name a required field.

3. Specify the legal values (validation rule) NON, RET, and SER for Client Type. Include appropriate validation text.

4. Make sure the Client Type field is a Lookup field by checking in Data View.

5. Change Data Type for Field Discount to Yes/No.

6. Import the data from the Excel file Business Analyst info to the Business Analyst table.

PART B – Create Query

All queries are based on the Client table. Only fields that show in bold should appear in the query results. Save each query as Query 1, Query 2, etc.

1. Display Client Number, Client Name, Amount Paid and Current Due for all Client Name that begin with “Gr”.

2. Display Client Number, Client Name, Amount Paid, Current Due and Business Analyst Number for all BA # 11 that have Amount Paid over $3,000.00.

3. Display Client Number, Client Name, Amount Paid, Current Due and Business Analyst Number for all BA # 11 OR all other BA # that have Amount Paid over $3,000.00.

4. Display the average Amount Paid for Business Analyst Number BA # 11.

5. Display Client Number, Client Name and Business Analyst Number for all Client that DO NOT have a Discount.

6. Display the average Amount Paid for all clients grouped by Client Type.

7. Display Client Number, Client Name, Amount Paid, Total Amount and a calculated field to show the difference between Total Amount and Amount Paid. Call this field “Amount Own”.

PART C – Using update Query

1. Insert a new field named “Bonus” in the Business Analyst Table to follow Incentive YTD. Data Type is Text, field size is 3

2. Use an update query to populate the field with “Yes”.

3. Make “Yes” the default value for this field.

4. Save update query as “Update Query”

PART D – Create a new Access Database and Tables using the Blank desktop database option

Review Chapter 1 from page AC1-5 to AC1-16