Database Assignment #3

Step I

  1. Create a new database file; name it Assignment #3 and save to your Access folder.
  1. Create a structure for your database using design view. The fields are listed below. Save the datasheet; name it EMPLOYMENT XX. Where xx is type your first and last initials. (DO NOT SET A PRIMARY KEY).

Field Name / Data Type / Field Size
LAST / TEXT / 30
FIRST / TEXT / 30
ADDRESS / TEXT / 50
CITY / TEXT / 20
STATE / TEXT / 5
ZIP / NUMBER
DOB / DATE/TIME (SHORT)
SALARY / CURRENCY (0 DECIMALS)
  1. Enter the records below into the datasheet.

RECORD 1RECORD 4

Your NameAnna Graham

212 Lehigh Avenue398 East Avenue

Clare, MI 48617Clare, MI 48617

2/18/993/18/90

$42,500$52,000

RECORD 2RECORD 5

Brooke TroutBonny Ann Clyde

8941 E. Main St.5237 W. Main St.

Harrison, MI 48625Coleman, MI 48618

9/25/7210/26/88

$28,900$42,000

s

RECORD 3RECORD 6

Brock LeeBill Ding

89 Washington Avenue342 Vernon Avenue

Harrison, MI 48625Clare, MI 48617

7/24/818/15/55

$20,000$45,800

  1. Adjust column widths as necessary to accommodate the longest entry in each field.
  1. You have decided to remove Bill Ding from your database as he has moved from the area. Delete his record from the database. Save changes.
  1. A new field called EMPLOYER is to be added to the database. Insert the new field before the SALARY field. Enter the data listed below. Adjust the column width of this field to accommodate the longest entry.

RECORD 1 – EMPLOYERRECORD 2 – EMPLOYER
Evergreen Sporting GoodsCountry Day Hospital

RECORD 3 – EMPLOYERRECORD 4 - EMPLOYER

Harbor LanesCity of Clare

RECORD 5 – EMPLOYER

Rodgers, Inc.

  1. Add a new record to the database. Insert the following information: Ferris Wheeler, 202 Union Street, Clare, MI 48617, 12/30/88, Elmo Steel, $31,000.
  1. Sort the database records in ascending order by LAST and then FIRST. (LAST is a primary sort and FIRST is a secondary sort). Change the layout to landscape. Preview the file to make sure the table fits on one page. Print the sorted table.
  1. Sort the database records with the oldest person being listed first. Preview the file to make sure the table fits on one page. Print the sorted table.
  1. Sort the database records from the highest to the lowest salary. Preview the file to make sure the table fits on one page. Print the sorted table.

Step II

Perform the queries indicated below in directions 11-14. After running the query, list the EMPLOYEE NAMES of your computer results on the line provided below each query.

  1. Show all fields. Query the database to find those individuals who make less than $30,000 a year. Save as Query 11-2 XX.

Q Query Results – Employee Name(s) (First and Last):

______

  1. Display the following fields: FIRST, LAST, and SALARY. Query the database to find those individuals who make $50,000 or more. Save as Query 12-1 XX.

Query Results – Employee Name(s) (First and Last):

______

  1. Show all fields. Query the database to find those people born between 1950 and 1980. Save as Query 13-2 XX.

Query Results – Employee Name(s) (First and Last):

______

  1. Show all fields. Query the database to find those people with the first names beginning with the letter B. Save as Query 14-1-3 XX.

Query Results – Employee Name(s) (First and Last):

______

______

  1. Display the following fields: LAST, EMPLOYER, and SALARY. Query the database to find those individuals who work at Harbor Lanes and make less than $21,000. Save as Query15-1 XX.

Query Results – Employee Name(s) (First and Last):

______