Microsoft Office Tutorials and References
In Depth Information
3. Use Design view to create a select query that, for all pledges with a TotalPledged field
value of greater than 200, displays the DonorID (from the Donor table), FirstName,
LastName, PledgeID, TotalPledged, and FundName fields. Sort the query in ascending
order by TotalPledged. Save the query as LargePledges , and then run the query.
4. Use the LargePledges datasheet to update the Pledge table by changing the
TotalPledged field value for PledgeID 2976 to 750. Print the query datasheet, and
then close the query.
5. Use Design view to create a select query that, for all donors who pledged less than
$150 or who donated to the Whale Watchers fund, displays the PledgeID,
PledgeDate, TotalPledged, FirstName, and LastName fields. Save the query as
PledgedOrWhaleWatchers , run the query, and then print the query datasheet.
Change the query to select all donors who pledged less than $150 and who donated
to the Whale Watchers fund. Save the revised query as PledgedAndWhaleWatchers ,
and then run the query. Close the query.
6. Use Design view to create a select query that displays the DonorID (from the Donor
table), TotalPledged, PaymentMethod, PledgeDate, and FundName fields. Save the
query as PledgesAfterCosts . Create a calculated field named Overhead that displays
the results of multiplying the TotalPledged field values by 15% (to account for over-
head costs). Save the query, and then create a second calculated field named
NetPledge that displays the results of subtracting the Overhead field values from the
TotalPledged field values. Format the calculated fields as Fixed and set an appropriate
caption for the NetPledge field. Display the results in ascending order by TotalPledged.
Save the modified query, and then run the query. Resize all datasheet columns to
their best fit, print the query results, and then save and close the query.
7. Use the Pledge table to display the sum, average, and count of the TotalPledged field
for all pledges. Then do the following:
a. Specify column names of Total Pledge, Average Pledge, and Number of Pledges.
b. Change properties so that the values in the Total Pledge and Average Pledge
columns display two decimal places and the Fixed format.
c. Save the query as PledgeStatistics , run the query, resize all datasheet columns to
their best fit, and then print the query datasheet. Save the query.
d. Change the query to display the sum, average, and count of the TotalPledged field
for all pledges by FundName. ( Hint : Use the Show Table button on the Query
Design toolbar to add the Fund table to the query.) Save the query as
PledgeStatisticsByFund , run the query, print the query datasheet, and then close
8. Close the Redwood database.
Case Problem 4
Data File needed for this Case Problem: GEM.mdb ( cont. from Tutorial 2 )
GEM Ultimate Vacations Griffin and Emma MacElroy want to analyze data about their
clients and the luxury properties they rent. Help them query the GEM database by com-
pleting the following:
Challenge yourself by
including a new type of
query, for a luxury prop-
erty rental agency.
1. Open the GEM database located in the Brief\Case4 folder provided with your Data Files.
2. Define the necessary one-to-many relationships between the database tables, as fol-
lows: between the primary Guest table and the related Reservation table, and between
the primary Property table and the related Reservation table. ( Hint : Add all three
tables to the Relationships window, and then define the two relationships.) Select the
referential integrity option and the cascade updates option for each relationship.