UK Number USA Number USA VIP Number Support Email

Create a new table in Datasheet view with the following options: Rename the default primary key ID field to State and change the Data Type to Short Text.

by

 

  1. Create a new table in Datasheet view with the following options:
    1. Rename the default primary key ID field to State and change the Data Type to Short Text. (Tip: State should remain the primary key.)
    2. Change the field size of the State field to 2.
    3. Add a new field with the name StateName and change the data type to Short Text.
    4. Save the table with the name States.
  2. With the States table still open in Datasheet view, add the four records shown in Table 1.

Table 1: States table

 

 

State StateName
MA Massachusetts
ME Maine
NH New Hampshire
VT Vermont

 

  1. Resize the StateName field so that all field values are completely visible. Save and close the States
  2. Open the Guides table in Datasheet view. Add a new field following the PostalCode field with the field name YearsExperience and the Number data type. Close the table.
  3. Open the Trips table in Design view and make the following changes to the Distance
    1. Change the data type from Short Text to Number
    2. Change the Caption property to Distance (Miles)
  4. With the Trips table still open in Design view, make the following changes to the GroupSize field:
    1. Change the data type from Short Text to Number
    2. Change the Decimal Places property from Auto to 0.
    3. Change the Caption property to Maximum Group Size
  5. With the Trips table still open in Design view, change the Field Size property for the State field to 2. Save the changes to the Trips table and then close it. (Note: Because there were changes to data type and field sizes, the “Some data may be lost” warning message appears. The data fits within the valid ranges, so ignore this message and continue saving the table.)
  6. Import the data from the Excel file xlsx available for download from the SAM website. Append the records into the Trips table. Do not create a new table and do not save the import steps.

 

 

  1. Open the Trips table in Datasheet view, navigate to the third record (which has a TripID field value of CLNHP1) and enter KS01 for GuideID field value. Navigate to the previous record (which has a TripID field value of BPMEH1) and enter RH01 for the GuideID field value. Close the table.
  2. Open the Guide Listing Query in Design view and add a criterion to select only those records with a State field value of NH. Run the query, then save, and close the query.
  3. Use the Simple Query Wizard to create a query based on the Trips table with the following options:
    1. Include all fields from the Trips table in the query.
    2. Make sure the Detail option button is selected.
    3. Save the query with as Trips Query (which is the default name).

Close the query.

  1. Create a simple form for the Trips Save the form as Trips Form and close the form. (Hint: Use the Form button to create this form.)
  2. Create the simple report shown in Figure 1 for the Trips (Note: Your report may show a different date and time than Figure 1.) Do not add any totals and do not resize any columns. Save the report as Trips Report and close the report.

Figure 1: Trips Report

 

 

 

  1. Open the Guide Update Form in Form view and navigate to the second record, which has the GuideID of BR01. Change the Address field value for this record to 140 Oak Stump Rd. and then close the Guide Update Form.
  2. Open the Guide Contact Numbers report in Layout view and change the report title from Guide Contact to Guide Contact Numbers. Save and close the report.
  3. Open the Reservations table in Datasheet view. Add a new field following the TripDate field with the field name GroupSize and the Number data type.