Re: distinct
If you remove it from the SELECT columns part you need to add it to the GROUP BY columns part!
SELECT tblCasesMain.CaseNum, First(tblCasesMain.OldCaseNum) AS FirstOfOldCaseNum, First(tblCasesMain.CaseStatus) AS FirstOfCaseStatus, First(tblCasesMain.Incident) AS FirstOfIncident, tblNameslst.LastName, tblNameslst.FirstName, tblNameslst.Company, First(tblCasesMain.OpenDate) AS FirstOfOpenDate, First(tblCasesMain.CloseDate) AS FirstOfCloseDate, First(tblCasesMain.PendingDate) AS FirstOfPendingDate, First(tblBalances.InitBal) AS FirstOfInitBal, First(tblCasesMain.Exposure) AS FirstOfExposure
FROM ((tblCasesMain LEFT JOIN tblPaymentslst ON tblCasesMain.CaseNum = tblPaymentslst.CaseNum) LEFT JOIN tblBalances ON tblCasesMain.CaseNum = tblBalances.CaseNum) LEFT JOIN tblNameslst ON tblCasesMain.CaseNum = tblNameslst.CaseNum
GROUP BY tblCasesMain.CaseNum, tblNameslst.LastName, tblNameslst.FirstName, tblNameslst.Company, tblPaymentslst.PaymentAmount
ORDER BY tblCasesMain.CaseNum, tblNameslst.LastName, tblNameslst.FirstName, tblNameslst.Company;
You might also want to add it to the ORDER BY column list...
Edited 2 time(s). Last edit at 04/05/2006 04:22AM by Sebastien Caisse.