how to eliminate from a list duplicated records

[RickLV]

I'm trying to display a list of scheduled appointments, but with my query I cannot eliminate the any duplicates

In case of more than one contact (record) for the same sales lead I'd like to display only the most recent one. So in my example, record 1 and 2 belong to the same sales lead. Technically my agent has already contacted the customer on 03/10/07 so the first line (record) should not be displayed

23-23-03/03/07-community01-first-last-03/03/07-email-03/10/07-11:00
23-25-03/03/07-community01-first-last-03/10/07-email-03/19/07-12:00
01-24-03/12/07-community02-first-last-03/12/07-email-03/21/07-15:00
...

Any idea?

Thanks

rick

SELECT tblContacts.intLeadID, tblContacts.intContactID, tblLeads.dteLeadDateIn, tblCommunities.txtCommunityName, tblLeads.txtLeadFirst, tblLeads.txtLeadLast, tblContacts.dteLastContact, tblContacts.txtMethod, tblContacts.txtContactedBy, tblContacts.memContactComments, tblContacts.dteNextContactDate, tblContacts.dteNextContactTime, tblLeads.intStatusID
FROM (tblCommunities INNER JOIN tblLeads ON tblCommunities.intCommunityID = tblLeads.intCommunityID) INNER JOIN tblContacts ON tblLeads.intLeadID = tblContacts.intLeadID
WHERE (((tblContacts.dteNextContactDate) Is Not Null))
ORDER BY tblContacts.dteNextContactDate;

[NightShift58]

Use GROUP BY on that field. You'll also have to ORDER BY in such a way that the most recent entry appears first

[RickLV]

don't think I can use GROUP BY

[NightShift58]

Out of curiosity: Why not?

[RickLV]

I don't think I have any aggregate function, such as SUM or COUNT. I could GROUP BY intLeadID, however how am I going to handle fields like txtNextContact(date)?

I tried to use DISTINCT and this is returning just one record, however I don't want just any of the records, I need the lastest one entered.

If I have

id next appointment
1 03/10/07
1 03/12/07
1 03/15/07
2 03/15/07

i'd like to get two records out of my select statement: 3rd and 4th ones only.

[NightShift58]

The "rule" is slightly different.

You need GROUP BY to use an aggregate function.
You don't need an aggregate function to use GROUP BY.

You can safely use GROUP BY to filter all duplicates except one and by using ORDER BY ... DESC you can manipulate which one is picked (first or last, obviously).

[RickLV]

Thanks, I did not know that. I will try and let you know.