Simple Query

[rickyzicky]

I have 2 tables. [Streets] and [donations].
The 2 are linked by a common field.

I simply need to build a query for those who have not donated this year. I can't build a query asking for "is null" on the amount field because it will pull out previous years record. I need to exclude these.

Any thoughts as to the best way to do this?

Street Table is names, addresses id# etc...
Donations table is date, amount, id#.

Thanks

 

[Isadore]

rick: Just restrict your date filed at the same time, e.g., >#12/31/02#

...and that should do it.

 

[THWatson]

Rick
Building on Isadore's suggestion, if you are wanting to follow the same exclusions into next year, you could try putting the following criteria in your date field...
Year([YourDonationDate])=Year(Date())

That should always limit donations to those made in the current year.

Tom

 

[dhookom]

I don't think these are the solutions you are looking for since you want only records where they haven't donated this year. Try SQL like:
SELECT [Name], [Address], Street.ID, Max(DonationDate) as LastDonationDate
FROM Street INNER JOIN Donations ON Street.ID = Donations.ID
HAVING Max(DonationDate)<DateSerial(Year(Date()),1,1);

BTW: Name and Date are poor field names since they are Access reserved words. If you are using punctuation in field names, I would suggest you find an alternative.

Duane
MS Access MVP

 

[Golom]

Isadore's and THWatson's recommendations look good but there's another possibility and that is that someone who has not donated this year does not have a record in the Donations table. If that's the case then you want something like:

   Select S.ID, S.Name, S.Address

   FROM Street As S LEFT JOIN Donations As D ON S.ID = D.ID

   GROUP BY S.ID, S.Name, S.Address

   HAVING Year(MAX(D.DonationDate)) < Year(Date())
          OR MAX(D.DonationDate) IS NULL

 

[rickyzicky]

dhookum

By changing your code to mine, gives me this:

SELECT Streets.OCCUPANT, Streets.STREET_NAM, Streets.STREET_NUM, Streets.SEQ, Max(Donations.DateEntered) AS LastDonationDate, Donations.Amount
FROM Streets INNER JOIN Donations ON Streets.SEQ = Donations.seq
GROUP BY Streets.OCCUPANT, Streets.STREET_NAM, Streets.STREET_NUM, Streets.SEQ, Donations.Amount
HAVING (((Max(Donations.DateEntered))<DateSerial(Year(Date()),1,1)));
---------------------------------------------
This returns only donations made in 2002 regardless if someone donated in 2003. This won't help.

I simply need to send a solicitation letter to those who have not donated this year.

I can produce a query that shows all donation in 2003.
How can I select the opposite records in the Streets Table?

 

[dhookom]

Try remove the Donations.Amount from the query. Notice, that I hadn't added this because it would cause issues.

Duane
MS Access MVP

 

[rickyzicky]

NO difference in results. Still shows only 2002.
Thanks.

RZ

 

[JonFer]

Golom's solution gives you what you want. It is similar to Duane's but with the Left Join and the &quot;OR IS NULL&quot; added. Don't include the donation amount unless you sum() or max() it.

 

[rickyzicky]

Well Goloms code starts off like:

SELECT Streets.seq, Streets.street_Nam, Streets.street_num
FROM Streets AS S LEFT JOIN Donations AS D ON S.SEQ = D.seq
GROUP BY Streets.street_Nam, S.SEQ, Streets_num
HAVING (((Year(Max(Donations.DateEntered)))<Year(Date()))) OR (((Max(Donations.DateEntered)) Is Null));

and ends up like this after it is run.
Once run it asks for several parameters and gives me all blank Records for columns Expr1, 2, 3

SELECT Streets.seq AS Expr1, Streets.street_Nam AS Expr2, Streets.street_num AS Expr3
FROM Streets AS S LEFT JOIN Donations AS D ON S.SEQ = D.seq
GROUP BY Streets.street_Nam, S.SEQ, Streets_num
HAVING (((Year(Max(Donations.DateEntered)))<Year(Date()))) OR (((Max(Donations.DateEntered)) Is Null));

 

[dhookom]

Golom had &quot;aliased&quot; your table names which your sql hadn't.

SELECT S.seq, S.street_Nam, S.street_num
FROM Streets AS S LEFT JOIN Donations AS D ON S.SEQ = D.seq
GROUP BY S.street_Nam, S.SEQ, Street_num
HAVING (((Year(Max(DateEntered)))<Year(Date()))) OR (((Max(DateEntered)) Is Null));


Duane
MS Access MVP