Query of donors by number of donations made

[Guest]

A charitable organization that I am helping with has a list of Donors
(containing personal details) and a separate table of Donations made, the two
tables being linked by DonorID numbers.

Now they want to run the following two queries that I am having trouble with:

1. For each Donor, list the number of checks written in a specified
calendar year
2. For each specified calendar year, show a list of Donors that made more
than 10 donations

Your help with the expressions/formulas that can yield the above queries
would be greatly appreciated.

Thanks!

 

[Roland Hall]

A charitable organization that I am helping with has a list of Donors
(containing personal details) and a separate table of Donations made, the
two
tables being linked by DonorID numbers.

Now they want to run the following two queries that I am having trouble
with:

1. For each Donor, list the number of checks written in a specified
calendar year
2. For each specified calendar year, show a list of Donors that made more
than 10 donations

Your help with the expressions/formulas that can yield the above queries
would be greatly appreciated.

What do you have so far?

 

[Guest]

Create a query with Donors and Donations, joined 1:1 by DonorID, and bring in
the fields for Donor Name (from Donors), Donation Year and Donation Type
(from Donations). Click on your group function and change the Group By to
Count for Donation Type. In your criteria for Donation Year, type this:

[Enter the Donation Year]

This text in brackets can be whatever you want the prompt to say. Having a
prompt relieves you from having to modify the query in design view every time
you want to run the query for a different year.

Set the criteria for Donation Type to be however "Check" is entered
(assuming it's from a list/combo box or this is consistency in data entry).

If you don't have Donation Year, you can make a field based on Donation Date
like this:

DonationYear: Year([field name for donation date])

and use the same criteria prompt.

Save this query. For part two, write another query based on the one you
just saved and set the criteria for CountOfDonationType to >=10.

 

[Guest]

Nothing, I am going to try Pendragon's suggestions.

 

[Guest]

I must have misunderstood you because the query did not work; it counted each
check as 1 unit instead of aggregating the number of checks written by the
donor. I may have added the [Count} function to the wrong column. Let me add
a few particulars about this data base (which was created years ago by
who-knows who):

1) There is only one Donation Type: by check (I guess donors want a receipt
for their tax records)
2) The date of the check is entered in the format of ##/##/####, in a single
column. As far as I'm concerned, it would be acceptable to these people to
enter a Starting date and an Ending date for the checks received when they
get the prompt.

When I tried to create a Donation Year as you suggested, I got a syntax
error, which tells me that I am messing this one up in more than one place!

Thanks for your patience

 

[Guest]

Never mind my previous post of utter confusion! I figured out what I was
doing wrong and the queries now work perfectly. Thanks for your help.

Create a query with Donors and Donations, joined 1:1 by DonorID, and bring in
the fields for Donor Name (from Donors), Donation Year and Donation Type
(from Donations). Click on your group function and change the Group By to
Count for Donation Type. In your criteria for Donation Year, type this:

[Enter the Donation Year]

This text in brackets can be whatever you want the prompt to say. Having a
prompt relieves you from having to modify the query in design view every time
you want to run the query for a different year.

Set the criteria for Donation Type to be however "Check" is entered
(assuming it's from a list/combo box or this is consistency in data entry).

If you don't have Donation Year, you can make a field based on Donation Date
like this:

DonationYear: Year([field name for donation date])

and use the same criteria prompt.

Save this query. For part two, write another query based on the one you
just saved and set the criteria for CountOfDonationType to >=10.

A charitable organization that I am helping with has a list of Donors
(containing personal details) and a separate table of Donations made, the two
tables being linked by DonorID numbers.

Now they want to run the following two queries that I am having trouble with:

1. For each Donor, list the number of checks written in a specified
calendar year
2. For each specified calendar year, show a list of Donors that made more
than 10 donations

Your help with the expressions/formulas that can yield the above queries
would be greatly appreciated.

Thanks!