Excluding records in a query using multiple tables

[Aimee88]

I am trying to produce a list of those who donated to our orchestra in FY00 or FY01 *but not* in FY02 and FY03. (FY=Fiscal Year ending)

The query [FY00 or FY01 but not FY02 Donations Query] produces a list of those that donated in FY00 or FY01 but not FY02. However a few of those did give for this year (FY03), and so should be off this list of lapsed donors.

FYI: This query (with the far too long name) draws the member name, etc. from my main table and the donation data from the donation table, which includes every instance of giving by each donating memeber.

My *unsuccessful* attempts at this include:

1) Copying, then ammending the successful query, making it exclude FY02 *and* FY03 donors.

2) Running a query on the first query using the following:
SELECT [FY00 or FY01 but not FY02 Donations Query].TITLE, [FY00 or FY01 but not FY02 Donations Query].FIRSTNAME, [FY00 or FY01 but not FY02 Donations Query].LASTNAME, [FY00 or FY01 but not FY02 Donations Query].Date, [FY00 or FY01 but not FY02 Donations Query].Amount, [FY00 or FY01 but not FY02 Donations Query].[Fiscal Year]
FROM [FY00 or FY01 but not FY02 Donations Query]
WHERE (Donations.[Fiscal Year]="FY03&quot=False
ORDER BY Master.LASTNAME;

Any ideas?

Thanks.

Aimee

 

[cmmrfrds]

Simple example using NOT IN and subquery.

select fiscalyear, name from table
where fiscalyear IN (2000, 2001)
and name NOT IN (select name from table
where fiscalyear IN (2002, 2003))

 

[gol4]

assuming your table looks something like this
firstname lastname date amount

select lastname , firstname, max(date)
from table
where max(date)) between 2000startdatehere and 2001enddatehere
group by lastname , firstname , max(date)

will produce a list of those who donated to your orchestra in FY00 or FY01 *but not* in FY02 and FY03.

 

[Aimee88]

Thanks for the suggestions, but I'm not sure just what to do with them. My query(s) pull from two tables. The Master table stores name, address, etc. The Donations table stores every instance of a donation. I don't need all the donations from FY00 and FY01. I just need to know the people who have records from FY00 and FY01 but do not have records for FY02 and FY03. The following query succesfully gives me a record of those that gave in FY00 or FY01 but not FY02. I need to also exclude any on the list that gave in FY03. Right not there are about six people that skipped FY02, but have already given for FY03. How can I exclude them?


FY00 or FY01 but not FY02 Donations Query


SELECT Master.TITLE, Master.FIRSTNAME, Master.LASTNAME, Donations.Date, Donations.Amount, Donations.[Fiscal Year]
FROM Master INNER JOIN Donations ON Master.MemberID = Donations.MemberID
WHERE (((Donations.[Fiscal Year])="FY01" Or (Donations.[Fiscal Year])="FY00&quot AND ((Exists (Select * From Donations
Where Donations.MemberID=Master.MemberID
And (Donations.[Fiscal Year]="FY02&quot))=False))
ORDER BY Master.LASTNAME;


Can you tell me how to alter either this or the query in my original post? Did either of you (cmmrfrds or gol4) mean for me to alter my query with your information? I don't know enough to do that, evidently. A little more help please?

Thanks.

Aimee

 

[gol4]

Amiee,
What I am suggesting is to create a new query
if you select the max(date) they gave and group by their name it will tell you who has not given in 02 or 03

if I gave in 03 my max date will be 03
here is based one based on the fiscal year field Not test so may need some editing but hopefully you get the idea.

SELECT Master.TITLE, Master.FIRSTNAME, Master.LASTNAME,
max(Donations.[Fiscal Year])
FROM Master INNER JOIN Donations ON Master.MemberID = Donations.MemberID
GROUP Master.TITLE, Master.FIRSTNAME, Master.LASTNAME,
max(Donations.[Fiscal Year])
HAVING max(Donations.[Fiscal Year])) Between "fy01" And "fy02"

 

[Aimee88]

gol4,

Thanks. I think I'm starting to understand. However, I'm not great at trouble shooting these things yet. The query you wrote out for me is returing "syntax error in Group By".

I tried a couple things, but didn't get anywhere. I also saw the post about learning SQL and checked out some websites. I plan to spend some time there as soon as I can.

Aimee

 

[Aimee88]

Thanks for your help, all! I accomplished it using mostly the design view, but used the max concept (which is new to me). I had to use it with the dates, though, because in my fiscal year system FY99 would be perceived as more "max" than FY00, I would think.

Aimee

 

[Aimee88]

I'm back. I was sure that it had worked, using the max function, but when I attempted to use the query yesterday, I find that it is listing people whose latest gift was within the date range, but also any other (usually just one) gift given in the date range. I need to merge to a letter that says, &quot;Your last gift of <insert data>.....&quot; If I merge with this query, I'll get more than one letter for each person. I could then sort them by hand, but it seems I should be able to do this in Access. Any more help would be greatly appreciated.

In case it isn't clear, I have a master table with name, address phone, and then a donations table that stores each record of a donation.

Thanks.

Aimee