On May 8, 1:20*pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> Here is one way:
>
> SELECT P.people_code_id, G.giving_amount, G.fiscal_year
> FROM People AS P
> JOIN GivingSummary AS G
> * ON P.people_code_id = G.people_org_code_id
> WHERE EXISTS (SELECT *
> * * * * * * * * * * *FROM GivingSummary AS G2
> * * * * * * * * * * *WHERE G2.people_org_code_id =G.people_org_code_id
> * * * * * * * * * * * * *AND G2.giving_amount >= 500
> * * * * * * * * * * * * *AND G2.fiscal_year <> '');
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

Hi,

Thanks for the response. That is perfect. This leads to one more
question. Now that I am selecting the right group of people and
gifts, I need the select statement to adjust based on the number of
years the users want to report on. I've created a prompt where the
user enters a starting fiscal year. I then need to pull individuals
who have given a gift of $500 or more in any year starting with the
fiscal year the user entered. So, somehow I need to tell the query to
look to the user entered fiscal year and then run the exists statement
for years greater than or equal to that year.

For example, in the example I gave above the donor gives the following
gifts:
in 2006 they give $250
in 2007 they give $550
in 2008 they give $50

If at the prompt the user enters 2006, then all 3 gifts above would
appear. If at the prompt the user enters 2008, then the person would
not appear on the list because they do not have a gift above $500 in
the time period requested.

I tried adding in something along the lines of below, but it doesn't
seem to be working. Any suggestions?

WHERE EXISTS (SELECT *
FROM GivingSummary AS G2
WHERE G2.people_org_code_id =
GIVINGSUMMARY.people_org_code_id
AND G2.giving_amount >= 500
AND G2.fiscal_year <> ''
AND G2.FISCAL_YEAR >= @FiscalYear)