Query help to eliminate duplicates

[ron513]

I need assistance with a query.
The query will include fields from two tables, “Client” and “Donations” with a one to many relationship.

The results I would like to receive from the query are the records from the Client table that do not have a donation within the last six months.

My query SQL is:
SELECT DISTINCT Client.SSNbrClient, Client.LastName, Client.FirstName, Donation.Date, Donation.Amount
FROM Client INNER JOIN Donation ON Client.SSNbrClient = Donation.SSNbrClient
WHERE (((Donation.Date)<DateAdd("m",-6,Date())))
ORDER BY Client.SSNbrClient, Donation.Date;

Sample results:

SS# Last First Date Amount
111-11-1111 Jackson Tom 1/5/2002 $500.00
111-11-1111 Jackson Tom 2/15/2003 $200.00
111-11-1111 Jackson Tom 3/25/2004 $100.00
222-22-2222 Franks Jane 6/12/2002 $100.00
222-22-2222 Franks Jane 1/12/2004 $250.00
333-33-3333 Smith Sam 2/25/2004 $500.00

My problem is:
The query identifies the correct records but I would like to eliminate the duplicate records leaving only the ones with the most current donation date.
Such as:

SS# Last First Date Amount
111-11-1111 Jackson Tom 3/25/2004 $100.00
222-22-2222 Franks Jane 1/12/2004 $250.00
333-33-3333 Smith Sam 2/25/2004 $500.00

I thought the keyword, distinct would work, but it did not.
Your help will be greatly appreciated.

Regards

Ron

 

[BobStubbs]

I would try writing the query the other way round, i.e. make the Donations table the target of the SELECT DISTINCT operation.

If you do this, I think you will only ever see one donation record per client - no duplicates.

You could try building a Select Query based on the Donations table, get this to display the results you want, then look in SQL view to see the code generated by Access.

One other thing - can you have a client record for a client who has never donated? If so, such clients would not appear in your list.

Bob Stubbs

 

[PHV]

the records from the Client table that do not have a donation within the last six months ...only the ones with the most current donation date
Something like this ?
SELECT D.SSNbrClient As [SS#], M.LastName As [Last], M.FirstName As [First], M.LastDate As [Date], D.Amount
FROM Donation D INNER JOIN
(SELECT Client.SSNbrClient, Client.LastName, Client.FirstName, Max(Donation.Date) As LastDate
FROM Client INNER JOIN Donation ON Client.SSNbrClient = Donation.SSNbrClient
GROUP BY Client.SSNbrClient, Client.LastName, Client.FirstName
HAVING Max(Donation.Date)<DateAdd("m",-6,Date())
) M ON (D.SSNbrClient = M.SSNbrClient) AND (D.Date=M.LastDate)
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ron513]

Bob, PHV

Thanks for replying.

PHV, I tried your code suggestion and it worked great. I don’t fully understand it and would like to know how and why it works. If possible could you give an explanation in beginners terms that may help me understand it better? It appears two new tables, “D” and “M”, are created on the fly, is that correct? Thanks again.

Ron