Searching for last gift...

[kpsony]

I work for a non-profit, and I'm trying to do a query on what everyones last gift amount given was... I cant seem to figure out a code. Is this even possible?

I am fairly new to access, but do know the basics.

Any help would be incredible.

Thanks,

Brian

 

[kpsony]

THis is my query name: qryNormalizedGifts and this is the code:

SELECT [First Name] As FirstName, [Last Name] As LastName, [PRIMARY ADDRESS 2],  [PRIMARY CITY],  [PRIMARY STATE],  [PRIMARY ZIP], [Gift 1 Amount] As GiftAmount, [Gift 1 Date] As GiftDate FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE], [PRIMARY ZIP], [Gift 2 Amount], [Gift 2 Date] FROM[The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 3 Amount], [Gift 3 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 4 Amount], [Gift 4 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 5 Amount], [Gift 5 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 6 Amount], [Gift 6 Date] FROM [The Trevor Project Database]
UNION
SELECT [First Name], [Last Name],[PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 7 Amount], [Gift 7 Date] FROM [The Trevor Project Database]
UNION SELECT [First Name], [Last Name], [PRIMARY ADDRESS 1], [PRIMARY CITY], [PRIMARY STATE],  [PRIMARY ZIP], [Gift 8 Amount], [Gift 8 Date] FROM [The Trevor Project Database];

 

[dhookom]

Create a new query (qgrpMaxGiftDate) based on qryNormalizedGifts:
SELECT FirstName, LastName, Max(GiftDate) as MaxDate
FROM qryNormalizedGifts
GROUP BY FirstName, LastName;

Then create a new query based on qrtyNormalizedGifts and qgrpMaxGiftDate. Join the FirstName to FirstName, LastName to LastName, and MaxDate to GiftDate. This new query will display only the most recent gift record for each name.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kpsony]

Duane,
I found out that our DB has been recording the gifts under another table. They are not under Gift 1, Gift 2, etc.

They are under Gift ID, Donor ID, Date, Gift Amount, Gift Fund.

Now how do I convert that query to fit that?

 

[lespaul]

Since you are now working with a table that is normalized, you don't need the long UNION query anymore. Depending on the version of Access you are using, this query may work:

SELECT DonorID, [Date], [Gift Amount] FROM tblName INNER JOIN (SELECT DonorID, Max([Date]) From TblName) As A ON tblName.DonorID = A.DonorID AND tblName.[Date] = A.[Date]

If that doesn't work, you have two choices. Depending on how many records you are working with, you can use a correlated subquery (will take longer to run)

SELECT DonorID, [Date], [Gift Amount] FROM tblName As T1 WHERE [Date] = (SELECT Max([Date]) FROM tblName T2 WHERE T1.DonorID = T2.DonorID)

or create a query that just has the max dates:

SELECT DonorID, Max([Date]) From tblName Group BY DonorID

save that as qryMaxDates

then a second query:

SELECT DonorID, [Date], [Gift Amount] From tblName AS T1 INNER JOIN qryMaxDates on T1.DonorID = qryMaxDates.DonorID AND T1.[Date] = qryMaxDates.[Date]

Leslie

 

[dhookom]

Apparently someone has already found out about normalization. All of the union query stuff should be wasted time since the result of the union query would be similar to your existing table.

You could use something like
SELECT *
FROM tblAnotherTable
WHERE [Date] =
(SELECT Max([Date])
FROM tblAnotherTable a
WHERE a.[Donor ID] = tblAnotherTable.[Donor ID]);

Duane
MS Access MVP
Find out how to get great answers faq219-2884.