Microsoft Access HW

Page 1 of 4

Microsoft Access

1. Launch Access and create a database from scratch, save it with your name as a title.

2. Create a table called tblEmployees with the fields depicted in the figure below.

3. The LastName and FirstName fields must be limited to 25 characters in size

4. The Phone field must be limited to 10 characters in size and have an input mask suitable for a telephone number

5. The State field is a lookup with the following properties:

a. Field limited to 2 characters

b. Limit entries to the list

c. Entries are “forced” to upper case.

d. The values in the lookup field are: CA and AZ

6. The HourlyPay field has a format of currency and two decimal places, be sure that the cents are displayed.

7. All other fields have default values

8. Populate the table with the following records:

9. Save your work

Page 2 of 4

1. Create a table called tblStates with the fields depicted in the figure below.

2. Limit the Abbv field size to two, force entries to uppercase

3. Limit the StateName field size to twenty-five, force entries to uppercase

4. Populate the table with the following records:

5. Save your work

1. Create the following relationship

2. Enforce referential integrity

3. Cascade update related fields

4. Cascade delete related fields

5. Save your work

Page 3 of 4

1. Create a new form, call it frmEmployees.

2. The form should have a text box to enter a single digit.

3. When the user clicks the “Run Query” button, a query (which you will create next) will be invoked.

4. Save your work

Create a query, call it qryEmployees.

1. The query must display the first name, last name, phone number, state and Hourly Salary of employees which meet

the criteria explained below:

a. Display only those employees whose telephone area code begins with the digit entered by the user

2. Save your work

Page 4 of 4

Example 1:

Suppose I enter 1 as my digit and I click the “Run Query” button,

Access displays the one record below which is the only one that meets the criteria

Example 2:

Suppose I enter 2 as my digit, and I click the “Run Query” button,

Access displays the records below which are the only ones that meet the criteria