Eliminating duplicates

[48Highlander]

I work for a non-profit and I am trying to determine the amount of the most recent donation (if any).

tblMain contains the person's ID, name and address, etc. and tblSponsorships contains SponsorID (i.e. the person's ID), Date, and Amount.

I cannot figure out how to create a query where there is only one row for a person that contains the date and the amount of the most recent donation. I think I need a UNION query.

Any help would be most appreciated.

 

[48Highlander]

I guess I should clarify: tblMain has one row per person and tblSponsorships can have many rows for any given person.

 

[Crowley16]

when you say amount of the most recent donation...

do you mean amount of the most recent donation for all patients?

you should have something along the lines of:

SELECT DISTINCT TOP 1 ID, [Date], Amount...
FROM tbl JOIN tbl
ORDER BY DESC [Date]

order by date descending so the top 1 only gets the most recent date...

Procrastinate Now!

 

[48Highlander]

That returns only the single most recent donation in the table. For each person, I want the most recent donation for that person.

Here's what I have so far:

SELECT tblMain.LastName, tblMain.FirstName, tblSponsorships.TypeCode, tblSponsorships.Date, tblSponsorships.Amount
FROM ((tblTitles RIGHT JOIN tblMain ON tblTitles.TitleCode = tblMain.TitleCode) LEFT JOIN tblCategories ON tblMain.Category = tblCategories.CategoryCode) LEFT JOIN tblSponsorships ON tblMain.ID = tblSponsorships.SponsorID
WHERE (((tblSponsorships.TypeCode)=4)
ORDER BY tblMain.LastName, tblMain.FirstName;

 

[Crowley16]

Ok...

SELECT DISTINCT person, amount...
FROM tbl JOIN tbl ON
WHERE fKey = (
SELECT TOP 1 pKey
FROM tbl as a
WHERE a.person = person
ORDER BY [date]
)


Procrastinate Now!

 

[48Highlander]

I thought I understood what you were trying to say but the following returns no records.

SELECT DISTINCT tblMain.ID, tblSponsorships.Date, tblMain.LastName, tblMain.FirstName, tblSponsorships.TypeCode, tblSponsorships.Amount
FROM tblMain LEFT JOIN tblSponsorships ON tblMain.ID = tblSponsorships.SponsorID
WHERE (((tblSponsorships.TypeCode)=4) AND ((tblSponsorships.SponsorID)=(SELECT TOP 1 tblSponsorships.Date FROM tblSponsorships 
ORDER BY  tblSponsorships.Date DESC,  tblMain.LastName, tblMain.FirstName)))
ORDER BY tblSponsorships.Date DESC , tblMain.LastName, tblMain.FirstName;

What am I doing wrong?

 

[Crowley16]

no...

you are returning the date field in your nested select statement, and using the date field to find the correct sponsorID...

you need to return the id field in your nested select, plus, there's no need to order by name in this since ur only returning 1 value which is not a name...

Procrastinate Now!

 

[48Highlander]

I am truly sorry Crowley16 but I am a SQL beginner. I changed the WHERE to:

WHERE (((tblmain.id)=(SELECT TOP 1 tblSponsorships.SponsorID FROM tblSponsorships
ORDER BY tblSponsorships.Date DESC)))

and I am still getting just one record - the most recent single donation in tblSponsorships.

Would you mind writing out the correct SQL to get the latest date and amount for each name? I would really appreciate it.