Microsoft Access Projects

On Your Own 18-1

1. Open the data file Donations18

located in the Chapter 18\On Your

Own folder. Save the database as

DonationsList18.

2. In the Donor table, for Donor ID

36001, change the Title field to an

appropriate title, change the First

Name field value to your first name,

and change the Last Name field value

to your last name.

3. Create one-to-many relationships

between the primary Agency table

and the related Donation table, and

between the primary Donor table and

the related Donation table. For each

relationship, enforce referential

integrity and cascade updates to

related fields. Save and close the

Relationships window.

4. In the Donor table, delete the record

for Donor ID 36028. (Be sure to

delete the related record first.)

5. Create a query based on the Agency

table that includes the Agency,

FirstName, LastName, and City

fields. Save the query as

AgenciesByCity, and then run it.

6. Modify the AgenciesByCity query

design so that it sorts records in

ascending order first by City and then

in ascending order by Agency. Save

and run the query.

7. In the AgenciesByCity query datasheet,

change the contact for the Carpenter

After-School Center to Valerie

Jackson. Close the query.

8. Create a query in Design view that

displays the DonorID, FirstName,

and LastName fields from the Donor

table, and the Description and

DonationValue fields from the

Donation table for all donations over

$100. Sort the query in descending

order by DonationValue. Save the

query as BigDonors, and then run the

query.

9. Save the BigDonors query as

BigCashDonors. Modify the

BigCashDonors query to display only

records with cash donations of more

than $100. Do not include the

Description field values in the query

results. In the query datasheet,

calculate the sum of the cash

donations. Save and close the query.

On Your Own

10. Create a query in Design view that

displays the Agency from the Agency

table, and the DonationID,

DonationDate, and Description fields

from the Donation table. Save the

query as TrailsDonations, and then

run the query.

11. Use the Selection button in the Sort

& Filter group on the Home tab to

filter the TrailsDonations query

datasheet to display only the records

for donations to Eastern Wyoming

Trails.

12. Format the TrailsDonations query

datasheet to use an alternate row

color of the Purple, Accent 4, Lighter

80% theme color. (Hint: Use the

Alternate Row Color button in the

Text Formatting group on the Home

tab to select the row color.) Resize the

columns to best fit the complete field

names and values. Save the

TrailsDonations query.

13. Save the TrailsDonations query as

FurnitureOrHousewares. Modify the

FurnitureOrHousewares query to

display donations of furniture or

housewares and to list the Agency ID

instead of the Agency name. Sort the

records in ascending order first by

Description and then in ascending

order by AgencyID. Run the query,

adjust the column widths, save the

query, and then close it.

14. Create a query in Design view that

displays the DonorID, Agency,

Description, and DonationValue

fields for all donations that require a

pickup. Do not display the Pickup

field in the query results. Save the

query as PickupCharge. Create a

calculated field named Net Donation

that displays the results of subtracting

$9.25 from the Donation-Value field

values. Display the results in

ascending order by DonationValue.

Format the calculated field with the

Currency format. Run the query,

resize the columns in the query

datasheet to their best fit, save the

query, and then close it.

15. Create a query in Design view based

on the Donation table that displays

the sum, average, and count of the

DonationValue field for all

donations. (Hint: Use the Count

function to count the number of

rows.) Enter appropriate column

names for each field. Format the sum

and average values using the Standard

On Your Own

format and two decimal places. Save

the query as DonationStats, and then

run the query. In the query datasheet,

resize the columns to their best fit,

save the query, and then close it.

16. Create a copy of the DonationStats

query named

DonationStatsByAgency. Modify the

DonationStatsByAgency query to

display the sum, average, and count

of the DonationValue field for all

donations grouped by Agency, with

Agency appearing as the first field.

Sort the records in descending order

by the donation total. Save the query,

run the query, and then close it.

17. Compact and repair the

DonationsList18 database, and then

close it. (Do not save the contents of

the Clipboard.)

On Your Own