SELECT DISTINCT

[seand]

Hi Everyone I really need help with a SELECT DISTINCT query. I have one table called Loans and in that table I need to select unique loan numbers from the "LoanID" column" but still need to list all other columns in the table here is my query

SELECT DISTINCT dbo_Liabilities.LoanID, dbo_Contacts.ShowAsName, dbo_CTRL_Lender.Lender, dbo_Liabilities.InitialPrincipal, dbo_Liabilities.Status, dbo_Liabilities.DateCommenced, dbo_AssetOwners.OwnerName
FROM (dbo_CTRL_Lender INNER JOIN dbo_Liabilities ON dbo_CTRL_Lender.ID = dbo_Liabilities.LenderLkp) INNER JOIN (Advisers INNER JOIN (dbo_AssetOwners INNER JOIN dbo_Contacts ON dbo_AssetOwners.ContactID = dbo_Contacts.ContactID) ON Advisers.ContactID = dbo_Contacts.OwnerID) ON dbo_Liabilities.LoanID = dbo_AssetOwners.AssetID
WHERE (((dbo_Liabilities.Status)="Application Pending"));

But is till return duplicate "LoanID" as this loan is linked to more than one person but I only want this loan to show up once, **PLEASE** help me questions welcome, many thanks.

 

[seand]

WOW ! thanks for your effort, I see where you are going and I have tried to modify the code to the correct tables and names but with no success . I was trying to get an idea of where I should be going but I think it would be better to email you a screen dump of my tables to give you idea and the relationships between tables, I have is a screen dump of my tables and how they are related. I think we are just about there, could I email this? I am new to this board so I don't know whether I can upload the screen sump into this message it is only 96KB thanks [/img]