• Article

Exercise 1: Modifying the table structure of a Web database

Access 2010 templates are accessible from Access Backstage and are ready-to-use and free database solutions complete with tables, queries, forms, and reports built for common scenarios. In addition to the templates shipped with Access 2010, there are numerous third party templates available at office.com.

Many times, you may find that it is much easier to customize a template to a customer’s unique needs rather than start from scratch. In this exercise, you will learn how to perform tasks related to template customization and save development time.

Consider the needs of a human resources manager who needs to track job applicants. A good place to start in a situation like this is to see if there are any templates available that you could use as a starting point. In Access 2010, the Contacts Web Database template serves as an excellent starting point for a job applicant tracking system. Since the purpose of this exercise is to get experience modifying web database objects, we will use the Contacts Web Database template as a starting point for this exercise.

Task 1 – Create an Access Table

One common customization task when starting with a template is to modify the structure of existing tables in the template to suit your unique needs. Besides generic contact information, a job applicant tracking system needs to keep track of the details of the jobs that are available. In the first task, you will create a new database based on the Contacts Web Database template and then create a new table called Jobs for tracking available job details.

  1. Open Microsoft Access 2010
  2. Click on Sample templates
  3. Select the Contacts Web Database
  4. Change the File Name on the right side to Job Applicants

  5. Click Create

    Figure 1

    Available Templates

  6. Check out the various items in the database. In particular, check out the four tabs:
    1. Address Book – shows list of contacts and details of selected contact
    2. Datasheet – shows a list of contacts in data sheet view
    3. Report Center – shows a list of available reports and currently selected report
    4. Getting Started – provides links to helpful information
  7. Close the Main tab by right-clicking on the tab and choosing Close
  8. Expand the Navigation Pane. The navigation pane is the pane on the left-hand side of the application window that shows objects in the database. You can hide/show this pane by clicking on the “>>” button in the top-right corner of this pane.
  9. Take a minute and review the Tables, Queries, Forms, and Reports in the database
  10. Create the Jobs table. The Jobs table is a table that contains fields for storing the details related to the available positions. For example, it will store the position number, position title, status, and creation date, a description of the position, and the department of the position. As you perform these steps, keep in mind that you are working with a web database. Using web databases imposes some limitations on the types of functionality you can use in a database so that the database can run on SharePoint. The benefit of starting with a web database is that you can publish the database to the web with one click. You will notice that the design experience for creating and modifying web database objects is a little different from the design experience for modifying client only objects. To design a web table, you perform the modification in datasheet view using the functionality exposed in the Table Tools ribbon tabs.
    1. On the Create tab, click Table
    2. Select the ID column, choose Name & Caption from the ribbon

    3. Set the Name and Caption to Position Number and click OK

      Figure 2

      Field Properties

    4. Click Click to Add in the next column and select Text. This process sets the data type of the field and highlights the column heading so that you can enter the name of the field.
    5. Name the field Position Title
    6. In a similar manner as steps d and e, create the following fields. The list below shows the name of the field followed by the data type in parenthesis:
      1. Date Created (Date & Time)
      2. Description (Memo)
      3. Department (Text)
    7. Create the next field using the Lookup & Relationship data type. A lookup field allows you to control the possible values for a field based on a list of possible values. Use a relationship field for establishing relationships between tables in a database.

    8. Select I will type in the values that I want and click Next

      Figure 3

      Lookup Field Wizard

    9. Underneath the Col1 column heading, enter the following values: Open, Closed – filled, Closed – cancelled, Hold.

      Figure 4

      Lookup Field Values

    10. Click Next

    11. Set the label for the field to Status and check Limit To List

      Figure 5

      Lookup Field Label, Limit and Multiple Values Designation

    12. Click Finish. The following screen shot shows an example of the completed jobs table after entering a few records. Notice that the Status field contains the list of values you entered in step i previously. Figure 1

      Figure 6

      Lookup Field in Jobs Table

    13. Save the table as Jobs
    14. Right-click on the Jobs tab and select Close to close the table

Task 2 – Modify the Contacts table

In addition to adding tables to a template, you often need to modify existing tables. In this task, you will modify the contacts table to store additional information related to tracking a job applicant.

  1. Under Tables, double-click Contacts to open the Contacts table
  2. Click on the Company column header
  3. On the Fields tab, select Name & Caption

  4. Change the Caption from Company to Employer. In practice, you could also change the Name of the field. Changing the name however, could break any references to the field in queries, forms, or reports. In the interest of time for this exercise, leave the name the same so that you don’t need to worry about breaking any references.

    Figure 7

    Field Properties

  5. Click OK
  6. Scroll to the last field in the table.
  7. Add a new Date & Time field named Initial Interview. (See steps 10 d-f (or 4-6 if viewing lab on the web) in Task 1 for a refresher if you need.)
  8. Create a field for application status using the Lookup & Relationship data type
  9. Create a value list lookup by selecting I will type in the values that I want and click Next
  10. Enter the following values under Col1
    1. Open
    2. Closed – Not Qualified
    3. Closed – Applicant Withdrew
    4. Closed – Hired
    5. Closed – No decision
    6. Interviewing
    7. Offer outstanding
  11. Click Next
  12. Set the label to Status and check Limit To List
  13. Click Finish
  14. Create a field to store the referral source using the Lookup & Relationship data type. This field will have a drop-down list that allows you to select how the applicant heard about the position.
  15. Select I will type in the values that I want and click Next
  16. Enter the following values under Col1
    1. Agency
    2. Internal Referral
    3. Company Website
    4. Newspaper
    5. Other
    6. 3rd Party Website
  17. Click Next
  18. Set the label to Referral Source and check Limit To List
  19. Click Finish
  20. Create the next field using the Lookup & Relationship data type. In the next series of steps, you will create a Position column in the Contacts table that looks up position titles from the Jobs table. You will learn to establish a relationship between the Contacts table and the Jobs table. This process is different from the process you use for client-only databases.

  21. Select I want the lookup field to get the values from another table

    Figure 8

    Lookup Wizard

  22. Click Next

  23. Select Table: Jobs

    Figure 9

    Table Selection

  24. Click Next

  25. Double-click on Position Title and Position Number to transfer them to the Selected Fields list box

    Figure 10

    Field Selection

  26. Click Next

  27. Select Position Title in the first drop-down list to sort the values that appear

    Figure 11

    Sort Order

  28. Click Next

  29. In order to prevent the key column from appearing, ensure that Hide key column is checked

    Figure 12

    Set Column Width

  30. Click Next

  31. Set the label for the lookup field to Position

    Figure 13

    Lookup Field Label, Data Integrity and Multiple Values Designation

  32. Click Finish
  33. Close and save the table.

Exercise 1 Verification

In order to verify that you have correctly performed all steps of exercise 1, proceed as follows:

  1. Open the Jobs table

  2. Add records to the table as shown below. Observe how the Status field is limited to the values you specified when setting up the table.

    Figure 14

    Status Field

  3. Save and close the Jobs table
  4. Open the Contacts table
  5. Add a few records to the table – see suggested records below (do not worry about fields not shown). Note that the records below are in a vertical layout rather than the horizontal layout of your table. This is simply because there is not enough width in this page to view the records in a horizontal layout.

  6. Observe how the Status and Referral drop-down fields are limited to the values you specified when setting up the table. Also, note how the Position field is limited to position titles from the Jobs table.

    Figure 15

    Data for Contacts Table

  7. Save and close the Contacts table
  8. Open the Main form by double-clicking on it in the Navigation Pane
  9. Click on the Address Book tab

  10. Click on each of the names you entered in step 5 above. Observe how the details associated with each applicant are displayed in the window.

    Figure 16

    Contact Card

< previous | next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents