Select All but only Select some Distinct

[Guest]

I can't find anyone with the same issue so I am posting this question. I
would like to design an SQL query that selects a bunch of fields from a table
but uses only certain fields to Select distinct records. This is what I
hypothisize the SQL might look like:

SELECT tblCasesMain.CaseNum, tblCasesMain.OldCaseNum,
tblCasesMain.CaseStatus, tblCasesMain.Incident, tblNameslst.LastName,
tblNameslst.FirstName, tblNameslst.Company, tblCasesMain.OpenDate,
tblCasesMain.CloseDate, tblNameslst.TIN, tblBalances.TracerNum,
tblCasesMain.PendingDate, tblBalances.InitBal, tblPaymentslst.PaymentAmount,
tblCasesMain.Exposure
SELECT DISTINCT tblCasesMain.CaseNum, tblNameslst.LastName,
tblNameslst.FirstName, tblNameslst.Company
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
ORDER BY tblCasesMain.CaseNum, tblNameslst.LastName, tblNameslst.FirstName,
tblNameslst.Company;

Of course this doesn't work at all due to syntax error.

This is what my current output looks like:

Case Number Last Name First Name Company
06-0001 Dude Slick
06-0002 Doe Jane
06-0002 Doe Jane
06-0002 Doe Jane
06-0002 Doe Jane
06-0002 Doe Jane
06-0003 Tief Inc
06-0003 Ade Jade

I want it to look like:

Case Number Last Name First Name Company
06-0001 Dude Slick
06-0002 Doe Jane
06-0003 Tief Inc
06-0003 Ade Jade

Because I wan't to still be able to use the query to search for all the
fields but only to display matches where the above fields are unique. I am
not sure if this is possible with SQL and I am trying to avoid doing any more
coding at this point, but if that is the only option please do tell me.

 

[John Spencer]

You might be able to use a totals query to get what you want. As long as
you don't care which values for the other fields gets returned.

Something Like the following.

SELECT tblCasesMain.CaseNum,
tblNameslst.LastName,
tblNameslst.FirstName,
tblNameslst.Company,
First(tblCasesMain.OldCaseNum) as fOldCaseNum,
First(tblCasesMain.CaseStatus) as fCaseStatus,
First(tblCasesMain.Incident) as fMainIncident,
First( tblCasesMain.OpenDate) as fOpenDate,
First(tblCasesMain.CloseDate) as fCloseDate,
First(tblNameslst.TIN) as fTIN,
First(tblBalances.TracerNum) as fTracerNum,
First(tblCasesMain.PendingDate) as fPendingDate,
First(tblBalances.InitBal) as fInitBal,
First(tblPaymentslst.PaymentAmount) as fPaymentAmount,
First(tblCasesMain.Exposure) as fExposure
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
ORDER BY tblCasesMain.CaseNum, tblNameslst.LastName, tblNameslst.FirstName,
tblNameslst.Company;