Microsoft Office Tutorials and References
In Depth Information
Data File needed for the Review Assignments: Recruits.mdb ( cont. from Tutorial 2 )
Elsa needs information from the Recruits database, and she asks you to query the database
by completing the following:
Build on what you
learned in the tutorial
by practicing those
skills using the same
1. Open the Recruits database located in the Brief\Review folder provided with your
2. Create a select query based on the Student table. Display the StudentID, FirstName,
and LastName fields in the query results; sort in ascending order based on the
LastName field values; and select only those records whose Nation value equals
Ireland. ( Hint : Do not display the Nation field values in the query results.) Save the
query as StudentsFromIreland , run the query, and then print the query datasheet.
3. Use the StudentsFromIreland datasheet to update the Student table by changing the
FirstName field value for StudentID OMA9956 to Richard. Print the query datasheet,
and then close the query.
4. Define a one-to-many relationship between the primary Recruiter table and the related
Student table. Resize the field lists, as necessary, to display all the field names. Select
the referential integrity option and the cascade updates option for the relationship.
5. Use Design view to create a select query based on the Recruiter and Student tables.
Select the fields FirstName, LastName, City, and Nation from the Student table, and
the fields BonusQuota, Salary, and SSN from the Recruiter table, in that order. Sort in
ascending order based on the Nation field values. Select only those records whose
SSN equals “977-07-1798.” ( Hint : Do not display the SSN field values in the query
results.) Save the query as WolfeRecruits , and then run the query. Resize all columns
in the datasheet to fit the data. Print the datasheet, and then save and close the query.
6. Use Design view to create a query based on the Recruiter table that shows all
recruiters with a BonusQuota field value between 40 and 50, and whose Salary field
value is greater than 35000. ( Hint : Refer to Figure 3-19 to determine the correct com-
parison operator to use.) Display all fields except SSN from the Recruiter table. Save
the query as BonusInfo , and then run the query.
7. Switch to Design view for the BonusInfo query. Create a calculated field named
RaiseAmt that displays the net amount of a 3% raise to the Salary values. The expres-
sion for the calculated field will begin with the Salary field, and add to it the result of
multiplying the Salary field by .03. Display the results in descending order by
RaiseAmt. Save the query as a new query named SalariesWithRaises , and then run
8. Switch to Design view for the SalariesWithRaises query, and then change the format
of the calculated field to the Standard format, with no decimal places. Also change
the caption property of the calculated field to “Raise Amt.” Run the query. Resize all
columns in the datasheet to fit the data, print the query datasheet, and then save and
close the query.
9. In the Database window, copy the StudentsFromIreland query, and then paste it with
the new name StudentsFromHollandPlusYoungerStudents . Open the new query in
Design view. Modify the query to display only those records with a Nation field value
of Holland or with a BirthDate field value greater than 1/1/85. Also, modify the query
to include the Nation field values in the query results. Save and run the query. Resize
all columns in the datasheet to fit the data, print the query datasheet, and then save
and close the query.