Last year but not this

[Guest]

I have a donation table that lists ID, gift amount, and gift date. I want to
create a query that tells me who gave last year but not in the current one.
The year in question is a fiscal year that runs from July 1-June 30. Any
ideas?

 

[Guest]

Open your query in design view and add a column like --
Fiscal Year: DatePart("yyyy",DateAdd("m",-6,[GiftDate]))

Add criteria like --
[Enter Fiscal Year - 2004]

 

[Jeff Boyce]

Dan

One approach would be to use a "chain" of queries (yes, it isn't elegant,
but it does work).

First, find all IDs that DID give "last" year.

Then find all IDs.

Then find all IDs NOT in the first list.

Each of these are queries.

Regards

Jeff Boyce
<Office/Access MVP>

 

[John Vinson]

I have a donation table that lists ID, gift amount, and gift date. I want to
create a query that tells me who gave last year but not in the current one.
The year in question is a fiscal year that runs from July 1-June 30. Any
ideas?

Two Subqueries would work. A criterion on the Donor table like

WHERE DonorID
IN(SELECT DonorID FROM Donations
WHERE DonationDate
BETWEEN DateSerial(Year(Date()) - IIF(Month(Date()) >= 7, 3, 2), 7, 1)
AND DateSerial(Year(Date()) - IIF(Month(Date()) >= 7, 2, 1), 6, 30))
AND DonorID
NOT IN(SELECT DonorID FROM Donations
WHERE DonationDate
BETWEEN DateSerial(Year(Date()) - IIF(Month(Date()) >= 7, 2, 1), 7, 1)
AND DateSerial(Year(Date()) - IIF(Month(Date()) >= 7, 1, 0), 6, 30))



John W. Vinson[MVP]

 

[Guest]

Sorry to get back so late I've been on vacation. This approach worked. Thank
you.

 

[Guest]

I want to thank your for your post. Sorry I'm so tardy I was on vacation. I
think this would work but, alas, I am don't know how to iuse it. I'd like to
learn if you could suggest how to. The 'chain of queries' that Jeff Boyce
suggested worked so its not crucial that I have the explanation. But I'm sure
it would deepen by knowledge of Access.

 

[John Vinson]

I want to thank your for your post. Sorry I'm so tardy I was on vacation. I
think this would work but, alas, I am don't know how to iuse it. I'd like to
learn if you could suggest how to. The 'chain of queries' that Jeff Boyce
suggested worked so its not crucial that I have the explanation. But I'm sure
it would deepen by knowledge of Access.

This is Jeff's "chain of queries" all combined into one query.

Just change the table and fieldnames to match yours and put the whole
shebang into the SQL window of a new query; or, you can put

IN (SELECT... )

on the Criteria line of the query design window.

John W. Vinson[MVP]

 

[Guest]

I have found the following to work in design view:
Field: Year

Criteria: Between DateSerial(Year(Date())-1,1,1) And
DateSerial(Year(Date()),1,0)

I realize after a year you probably already have it worked out. Just
posting for others seeking advice.