one-to-many query - how to pull only the last record

[ranieri71]

Table 1 is called DATA and contains names, address info
and Table2 is called DONATIONS and contains donations (amount, check number, etc).

I have a query with a one-to-many relationship (on the field "KEY"). So any one person can have multiple donations. What I am trying to do is send a letter "Dear John Smith, Last year you donated $10.00..."

Right now it IS pulling in everyone who has not donated... However, the query is pulling up "John Smith's" donation from 2008, 2007, and so on. How can I get it to pull up only his last donation and still pull up those who have never donated?

SELECT DONATIONS.DONATION_AMT, DONATIONS.CHECK_DATE, DATA.ACCT, DATA.FIRST_NAME, 
DATA.MID_NAM, DATA.LAST_NAME, DATA.CARE_OF, DATA.ADDR1, DATA.ADDR2, DATA.CITY, 
DATA.ST, DATA.ZIP, DATA.CODEB, DATA.EXP_YEAR
FROM DATA LEFT JOIN DONATIONS ON DATA.KEY = DONATIONS.KEY
WHERE (((DATA.ACCT)>99) AND ((DATA.EXP_YEAR)>"2007"));

FYI This query also filters out those with ACCT below 99 and EXP_YEAR above 2007 - so I am only sending to current members. This aspect is working well.

 

[pbaldy]

Here's a way to get the last donation:

http://www.baldyweb.com/LastValue.htm

You should be able to left join against the data table to also get those who haven't donated.

 

[ranieri71]

OK - I am following you. Something is wrong though, my query to get the last value, I followed as instructed but it is still pulling up all of the entries not just the last unless I remove the Donation_Amt. If I remove Donation_Amt it does pull up one instance of Key, Check Date. However, I really need the Donation Amt too! Any advice I can't figure out why it would do this.

 

[pbaldy]

In the sample my link is based on I tweaked it to do your type of thing. First query (qryMaxDates):

SELECT tblVehicles.vehicle, Max(VehicleMiles.DorDate) AS MaxDate
FROM tblVehicles LEFT JOIN VehicleMiles ON tblVehicles.vehicle = VehicleMiles.CarNum
GROUP BY tblVehicles.vehicle;

query based on that:

SELECT qryMaxDates.vehicle, VehicleMiles.*
FROM VehicleMiles RIGHT JOIN qryMaxDates ON (VehicleMiles.CarNum = qryMaxDates.vehicle) AND (VehicleMiles.DorDate = qryMaxDates.MaxDate);

If you're still stuck, post a sample db with your tables and I'll show you.

 

[ranieri71]

Yes, it is working for me now. Thank yoU!