Eliminating Duplicate Records

[barrybazouk]

I Have the following query:

SELECT tblDelegates.DelFirstName, tblDelegates.DelSurname, tblSessions.SessionID, Format([tblDelegates.DateSubmitted],'dd/mmm/yyyy hh:nn') AS [DateTime], tblDelegates.DelID, tblCourses.CourseName FROM (tblCourses INNER JOIN (tblDelegates INNER JOIN tblSessions ON tblDelegates.SessionID = tblSessions.SessionID) ON tblCourses.CourseID = tblSessions.CourseID) INNER JOIN (tblAdmin INNER JOIN tblDelegates_Audit ON tblAdmin.AdminID = tblDelegates_Audit.AdminID) ON tblDelegates.DelID = tblDelegates_Audit.DelID WHERE (((tblDelegates_Audit.auditDate) Between #9/1/2008# AND #9/30/2008#) AND ((tblDelegates.DelNewAct)=0)) GROUP BY tblDelegates.DelID, tblDelegates.DelFirstName, tblDelegates.DelSurname, tblSessions.SessionID, tblDelegates.DateSubmitted, tblCourses.CourseName ORDER BY tblDelegates.DelID

In total 201 records are shown.

Now in the tblDelegates_Audit table there is a field called tblDelegates_Audit.auditDate, which I need to select and order on.
So my query changes to

SELECT tblDelegates.DelFirstName, tblDelegates.DelSurname, tblSessions.SessionID, Format([tblDelegates.DateSubmitted],'dd/mmm/yyyy hh:nn') AS [DateTime], tblDelegates.DelID, tblCourses.CourseName, tblDelegates_Audit.auditDate
FROM (tblCourses INNER JOIN (tblDelegates INNER JOIN tblSessions ON tblDelegates.SessionID = tblSessions.SessionID) ON tblCourses.CourseID = tblSessions.CourseID) INNER JOIN (tblAdmin INNER JOIN tblDelegates_Audit ON tblAdmin.AdminID = tblDelegates_Audit.AdminID) ON tblDelegates.DelID = tblDelegates_Audit.DelID
WHERE (((tblDelegates_Audit.auditDate) Between #9/1/2008# And #9/30/2008#) AND ((tblDelegates.DelNewAct)=0))
GROUP BY tblDelegates.DelFirstName, tblDelegates.DelSurname, tblSessions.SessionID, tblDelegates.DelID, tblCourses.CourseName, tblDelegates.DateSubmitted, tblDelegates_Audit.auditDate
ORDER BY tblDelegates.DelID;

If I do this then it brings up more results - 399. The tblDelegates.DelID can be linked to many tblDelegates_Audit.DelID, and this bring up the extra records, how can I set it so it brings Unique tblDelegates_Audit.DelID.

Please can you help.

 

[redneckgeek]

If I do this then it brings up more results - 399. The tblDelegates.DelID can be linked to many tblDelegates_Audit.DelID, and this bring up the extra records, how can I set it so it brings Unique tblDelegates_Audit.DelID.

So which tblDelegates_Audit.auditdate do you want to see? Do you want to see the first one, or the most recent one, or does it not really matter?

 

[barrybazouk]

Hello there,

I would like to see the most recent record for tblDelegates_Audit.auditdate.

Thank you.