Select distinct

[access7]

Good morning

I'm hoping someone may help, I am trying to avoid duplicates in one of my queries....

Can the SELECT DISTINCT be used in a query in access 2010.

I have tried looking at the SQL view and inserting it at the start (after the SELECT) but that makes no difference... I wouldnt know where to put it in the query view??

Please find the SQL version below... would really appreciate any help

SELECT Tbl_Correspondence.CorrRef, Tbl_Correspondence.CompanyRef, Tbl_Company.Company, Tbl_Correspondence.DateOfCorr, Tbl_Correspondence.TimeofCorr, Tbl_Correspondence.CorrType, Tbl_Correspondence.Contact, Tbl_Correspondence.Notes, Tbl_Correspondence.CreatedBy, Tbl_Correspondence.CreatedWhen, Tbl_Diary.ActionType, Tbl_Diary.DueDate, Tbl_Diary.CompletionDate, Tbl_Diary.DiaryActionID, Tbl_Diary.Complete, Tbl_DiaryCC.CompleteCC, Tbl_DiaryCC.CCUser, Tbl_Diary.PassTo, Tbl_Diary.PassFrom
FROM Tbl_Company INNER JOIN ((Tbl_Correspondence LEFT JOIN Tbl_Diary ON (Tbl_Correspondence.CompanyRef = Tbl_Diary.CompanyRef) AND (Tbl_Correspondence.DiaryActionID = Tbl_Diary.DiaryActionID)) LEFT JOIN Tbl_DiaryCC ON Tbl_Correspondence.DiaryActionID = Tbl_DiaryCC.DiaryActionID) ON Tbl_Company.CompanyRef = Tbl_Correspondence.CompanyRef
WHERE (((Tbl_Correspondence.CompanyRef)=ICompanyRef()))
ORDER BY Tbl_Correspondence.DateOfCorr, Tbl_Correspondence.TimeofCorr;

 

[access7]

I have tried changing the properties in my query to Unique Record 'Yes' - which makes the SQL look like this...

SELECT DISTINCTROW Tbl_Correspondence.CorrRef, Tbl_Correspondence.CompanyRef, Tbl_Company.Company, Tbl_Correspondence.DateOfCorr, Tbl_Correspondence.TimeofCorr, Tbl_Correspondence.CorrType, Tbl_Correspondence.Contact, Tbl_Correspondence.Notes, Tbl_Correspondence.CreatedBy, Tbl_Correspondence.CreatedWhen, Tbl_Diary.ActionType, Tbl_Diary.DueDate, Tbl_Diary.CompletionDate, Tbl_Diary.DiaryActionID, Tbl_Diary.Complete, Tbl_DiaryCC.CompleteCC, Tbl_DiaryCC.CCUser, Tbl_Diary.PassTo, Tbl_Diary.PassFrom
FROM Tbl_Company INNER JOIN ((Tbl_Correspondence LEFT JOIN Tbl_Diary ON (Tbl_Correspondence.CompanyRef = Tbl_Diary.CompanyRef) AND (Tbl_Correspondence.DiaryActionID = Tbl_Diary.DiaryActionID)) LEFT JOIN Tbl_DiaryCC ON Tbl_Correspondence.DiaryActionID = Tbl_DiaryCC.DiaryActionID) ON Tbl_Company.CompanyRef = Tbl_Correspondence.CompanyRef
WHERE (((Tbl_Correspondence.CompanyRef)=ICompanyRef()))
ORDER BY Tbl_Correspondence.DateOfCorr, Tbl_Correspondence.TimeofCorr;

But that is not getting rid of my duplicates either...??

 

[access7]

I have just read somewhere that it wont work properly if some of the fields have Null values, could this be the problem?