Create an Employee Table by completing the following steps:
(Note - you may use my sample setup to get started if you like: My Example)

1. Open Access
2. Click the Blank Database hyperlink located in the New section of the New File Task Pane.
3. At the File New Database dialog box, change to the drive where your file will be saved, if necessary. Type Payroll in the File name text box, press Enter or click the Create button. You must "save" before you can create a database, unlike spreadsheets or documents.
4. At the Payroll : Database window, double-click Create table in Design view in the list box.
5. At the Table1 : Table window, key the fields shown in the table below by completing the following steps:
   1. Key Emp # in the Field Name text box and then press Tab.
   2. The word Text is automatically inserted in the Data Type text box. Change it to Auto Number.
   3. Position the I-beam pointer in the Description text box (for the Emp # field) and click the left mouse button. (You can also press F6 to switch to the top of the window and then press Tab to move the insertion point to the Description text box.) Key Employee number in the Description text box and then press Tab.
   4. Key SSN in the Field Name text box and press Tab. Add your description. Go down to the Field Properties and click in the Input Mask line. Click on the button with the three dots at the right and select Social Security, click on Try It: to see what it will look like. Click Next twice, select to show the hypens.
   5. Key Last Name in the Field Name text box and press Tab.
   6. Change the field size to 30 and click in the Description text box for the Last Name field (or press f6 and then press Tab). Key Employee last name and then press Tab.
   7. Key First Name in the Field Name text box and press Tab.
   8. Change the field size to 30. Click in the Description text box for the First Name field (or press F6 and then press Tab). Key Employee first name.
      Read 9-12 below before continuing to enter data.
   9. Continue keying the field names, data types, and descriptions based on the table below. To use different data types, click the down-pointing triangle after Text and click the appropropriate type from the drop-down list. Note field size default changes for Emp #, Last Name, First Name, Middle Initial, Street Address, City, State, Dept Code, & Gender.
   10. The Input mask (SSN, Zip Code & Hire Date) Note1: you don't need this for stret address because it contains both #'s & text; Note2: Hire Date is NOT text, it's a Date/Time in the pull down menu) is designed to allow numbers to be enterd as text instead of as numbers for calculation AND set a pattern for how data is entered in a field. It ensures that data in records conforms to a standard format. It also saves key strokes because any desired hyphens will be automatically inserted. To use the Input mask, click on the row in the Field Properties section, click on the ... button and select the appropriate option. To see what it will look like, click on the Try It: box, follow the directions.
   11. Make EACH field required by selecting [required - yes] in the field properties.
   12. The Validation ( Dept Code & Gender) serves as a control that only certain data can be entered into the cell. This is availabe in the field properties. For example, when in the Gender field, click on Validation, click on the [...] box, type M in the box, click the [Or] button, and type F. This eliminates anything other than M or F to be entered in the field.
6. When all fields are entered, save the table by completing the following steps:
   1. Click the Save button on the Table Design toolbar.
   2. At the Save As dialog box, key Employees in the text box and press Enter or click OK.
   3. At the message telling you that no primary key is defined and asking if you want to create one, click No. (You will learn more about primary keys later)
7. Close the Employees table by clicking File and then Close or clicking the Close button located in the upper right corner of the window.
8. Close the Payroll database file by clicking File and then Close or clicking the Close button located in the upper right corner of the window at the right side of the Title bar.

• Open Access and the Payroll database.
• Select Tables as the Object, if not already selected. Open the Employees table by double-clicking on Employees.
• The Department Codes represent the following Departments; add a validation to this field that will allow ONLY one of these 5 entries:
  1. TI = Information Technology
  2. HR = Human Resources
  3. AC = Accounting Services
  4. SA = Sales Services
  5. AD = Administration
• Create at least 15 records. Make up the information based on the following criteria:
  1. Have at least one employee in each of the 5 departments.
  2. Have at least five of the employees hired before 01/01/2000, and at least five hired after this date.
  3. At least five states represented.
  4. A variety of zip codes.
  5. At least four employees who live in Santa Cruz and three who live in Watsonville.Emp
• Notice you can move to the next field by pressing Tab, Shift + Tab will take you to the previous field (backward). Tab at the end of the record will take you to the first field of a new record.
• Also notice there is a * where a new record can be added.
• Save and Close the table.
• You can print the table using the default settings by opening the table (if closed) and click on the Print button on the Table Datasheet toolbar. Or, click on Print Preview and see the document on the screen. Notice the default settings - Headers/Footers, page orientation, margins, etc. To change these settings, simply click on the Page Setup dialog box, change the orientation, margins, etc. If you do not want the default Headers/Footers, remove the check mark from the Print Headings option in the Margins tab.
• You can change the column widths of your table by positioning the arrow pointer between the column headings and drag to the width you want or double-click to have the width automatically change to accommodate for the longest record in the column (sometimes the heading has the longest content).

• Add a record by clicking on the New Record button on the Table Datasheet toolbar or go to the last record and press the Tab key.
• Delete a record by clicking anywhere in the record to be deleted. Click the Delete Record butoon and click Yes if you are sure you want to delete the record.
• Notice the button below File on the menu bar. Click on the down arrow to see the options. Click on the button and notice the table toggles between Design View and Datasheet View. To add a field, have the table in Design View mode. Position the insertion point of your mouse on the row below where you want the new field. Use the menu bar to select Insert, then Rows or right click the mouse button and select Insert Rows. The row will be added above the selected row. Click the Undo button if you change your mind.
• To delete a field, select Design View, if necessary. Select the row to be deleted, click Edit, then Delete Rows, or right click on your mouse and select Delete Rows. Be sure you want to delete the field because the deleted field and all the data in the field cannot be undone with the Undo button once the table has been saved.
• Practice by adding a new field named Telephone Number after the Zip Code field. Use the Input Mask with hyphens, make it required. Add another field named Employee Category, make it a field size of 10 and required. Use the AutoCorrect option under the Tools menu by adding h for Hourly, t for Temporary, s for Salaried to the dictionary. This is the same option you used in Word; when you enter h, the program corrects it to Hourly. Add a validation to this field that allows only these three entries. Add a third field named Pay Rate. Make this a required number that has properties defined as Currency, 2 Decimals. Add a validation that requires a minimum wage of $8.00. Note: (>7.99 or >8.00?)
• Add five new records, delete one record, insert information in each of the new fields for each employee, having at least one employee in each of the Employee Categories. Very the pay rates, use your imagination and fantasy.
• While in design view, you can move a field by clicking on the field to be moved and hold down the left mouse button until it turns into a gray attached square; drag to the selected place.
• To sort records, switch to Datasheet view, if necessary. Select the field to be sorted, click on the A-Z or Z-A button on the menu bar.

• Queries are used to extract (pull out) specific data from a table. The word query means to ask a question.
• Open your Payroll database, if not open.
• Extract records by performing the following:
  1. Click the Queries button on the Objects bar.
  2. Double-click Create query in Design view in the list box.
  3. At the Show Table dialog box with the Tables tab selected, click Employees in the list box, click the Add button and then click the Close button.
  4. Drag fields from the table box to the Field text boxes by positioning the arrow pointer on the Table name and double-click. This highlights all the fields. Position the mouse over any of the highlighted area and drag-and-drop at the first field cell. All the fields should fill in.
  5. Use the Criteria: row to extract those records that meet the criteria. You may use the or row as well depending on your query.
  6. Query all employees who live in CA by entering CA in the criteria cell under the State field. Click on the ! button on the menu bar. You should see all the employees who live in CA. Click on Save or Save As and save the query as CA residents.
  7. Extract and save the following queries:
     1. All hourly employees.
     2. All hourly OR Temporary.
     3. All salaried employees who live outside of CA.
     4. Employees who are temporary and earn more than [your choice].
     5. Employees who were hired after 01/01/2000 AND are CA residents AND are temporary.
     6. Employees who were hired before 01/01/2000 AND are not CA residents AND are working in the TI OR AD departments.
     7. Employees who live in Santa Cruz OR Watsonville who are salaried AND not working in SA.
     8. Make up three more queries of your choice giving you 10 all together.

• Select the report button on the Objects menu.
• Select Create report by using wizard.
• At the Tables/Queries box, select Table: Employees.
• In the Available Field box, the first field is highlighted. Click on the right arrow; this moves the Selected Fields box.
• To change the order, highlight the Dept Code and click the arrow. This formats the report with the Emp # first, Dept second.
• Put the report in the order of your choice.
• If you want the report in the same order, you can click on the double arrow which moves all the fields over in the created order.
• Click Next and Finish to accept all the defaults.
• The report is produced on screen.
• Click the Design View button to make desired changes in the field size, alignment, etc.
• Click the View button to see the report.
• Widen the title box by dragging the right side of the box all the way to the right margin. The title box will now "cover" the entire table. Enter a second line by using [Ctrl Enter]. The three line title will be centered across all the columns.
• Center some of the column headers as appropriate.
• Experiment with how you want the report to look by going back and forth between Design View and View.
• With your beautifully designed report in Design View, click on the Properties button on the menu bar. This looks like a hand in front of a piece of paper.
• Click on the down arrow on the Record Source line, select one of your queries. Click on View on the menu bar. You should be able to see the same report format with only the extracted records.
• Make each report different by using color, font styles, etc.
• Save each report with an appropriate name. You should have a report for EACH query and one for the table.
• Ask questions on the discussion board.
• Turn it in via BlackBoard under ther Assignments link as the Access Problem 1.

Sample Exercise