Microsoft Office Tutorials and References
In Depth Information
8. Create a query that contains all records from the tblGuest table and all matching
records from the tblReservation table. Display all fields from the tblGuest table
and all fields except GuestID from the tblReservation table. Save the query as
qryGuestsAndReservations , run the query, and then close it. Create a crosstab query
based on the qryGuestsAndReservations query. Use the City field values for the row
headings, the People field values for the column headings, and the sum of the
RentalRate field as the summarized value, and include row sums. Save the query as
qryReservationsCrosstab , resize the columns in the query recordset to their best fit,
and then save and close the query.
9. Create a find duplicates query based on the tblProperty table. Select Location and
Country as the fields that might contain duplicates, and select the remaining fields
in the table as additional fields in the query recordset. Save the query as
qryDuplicateLocations , run the query, and then close it.
10. Create a find unmatched query that finds all records in the tblGuest table for which
there is no matching record in the tblReservation table. Display the GuestFirstName,
GuestLastName, City, and Phone fields from the tblGuest table in the query recordset.
Save the query as qryGuestsWithoutReservations , run the query, and then close it.
11. Modify the qryTopRentalCost query to use the Top Values property to select the top
30% of the records. Save and run the query, and then close it.
12. In the tblReservation table, change the PropertyID field data type to Lookup Wizard.
Select the PropertyID, PropertyName, and Location fields from the tblProperty table,
sort in ascending order by PropertyName, do not hide the key column, resize the
lookup columns to their best fit, select PropertyID as the field to store in the table,
and accept the default label for the look up column. View the tblReservation
datasheet, resize the PropertyID column to its best fit, test the lookup field without
changing a field value permanently, and then close the table.
13. Open the tblGuest table in Design view. Change the StateProv field data type to
Lookup Wizard using a list of values that you enter. In the Lookup Wizard dialog
box, create two columns and type the following pairs of values: Illinois and IL ,
Indiana and IN , and Ontario and ON . Resize the lookup columns to their best fit,
select Col2 as the field to store in the table, and accept the default label for the
lookup column. View the tblGuest table datasheet, resize the lookup column to its
best fit, test the lookup field without changing permanently field values, and then
save and close the table.
14. Define a field validation rule for the Bedrooms field in the tblProperty table.
Acceptable field values for the Bedrooms field are values between 3 and 25, includ-
ing those two values. Display the message Value must be between 3 and 25 when a
user enters an invalid Bedrooms field value. Save your table changes and then test
the field validation rule for the Bedrooms field; be certain the field values are the
same as they were before your testing.
15. Define a table validation rule for the tblReservation table to verify that StartDate field
values precede EndDate field values in time. Use an appropriate validation message.
Save your table changes, test the table validation rule, making sure any tested field val-
ues are the same as they were before your testing, and then close the table.
16. Designate the Level.02\Case4 folder as a trusted folder. ( Note: Check with your
instructor before adding a new trusted location.)
17. Close the Vacation database without exiting Access, make a backup copy of the
database, open the Vacation database, compact and repair the database, close the
database, and then exit Access.