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.

 

[jmersing]

If the end result of your query is going to end up in an access report. You can use a header to group by for each loan. There is an icon in the toolbar in the report design view that will alow you to sort and group, select the option for Group Header to Yes on your Loan ID Field. It will now display the number once.

If you have more than one record in your table with two different results on the same row in any other field, you will never eliminate the duplicate number.

HTH

 

[seand]

Thanks for the reply that helps a bit on reporting for loans only but does not fit with the overall plan of our database and reporting but will look into it further, ideally though we would need to run the query and filter it using SQL prior to compiling reports, many thanks.

 

[jmersing]

Where will you view this information on a Form? Because we could also show only the one loan id on a form. Or is it that you only want to see it in a query results window?

I'm not sure if you totaly understood what I was saying regarding distinct. Imagine this let's say you have table with your friends phone number, and address in it, if we gave your friend an ID we could easily query and get a distinct result, but let's say you have another friend who lives in the same house. When you try to query on the phone number or address, the query will return both names with the address and phone number listed twice.

So this is a case where you would want to have a many to one relationship between tables. I could have my addressID as the primary key that stores all address information in one table, and then a second table with my friend's ID. I could then query the address up distinct or add one friend to the query, it would then return just one address with that one friend.

Let me know if we are on the same page. We can do this in a form if you would like to, send me a PM to discuss.
Jim

 

[seand]

Hi Jim thanks for that, I sent you a PM but I felt for the benefit of users on the forum I thought it would be best to reply here as well.

In your example I have two freinds who live at the same address with the same phone number and postal address AND have the same id, I want to run a query searching for the ID and only display the first instance of that ID but still display all the address and phone details of that person as well, I hope that makes sense? it will not be on a form it will be shown in the query, many thanks.

 

[jmersing]

Actually I meant different friend IDs, the same ID for the address and phone number. All friends shold have an ID.

When you created the employee table did you add a distinct ID for each or use an auto number field? Because if so you could sort the query by that field so the first instance appears.

If not this might not be possible

 

[seand]

Each loan has a unique number called "LoanID" from the "dbo_Liabilities" table and you can have multiple owners for each loan i.e. husband and wife both have the loan.

The client has a unique number from the "dbo_Contacts" table called "ContactID" which is linked to the "LoanID" in the "Liabilities" table.

What I am trying to do is run a query from the "Liabilities" table for all unique loan numbers, BUT I need to also display the client details related to that "LoanID" but only the first instance of the "LoanID".

Here is my simple code but it shows both instances of the "LoanID" and this is what I am trying to elimiate.


SELECT DISTINCT dbo_Liabilities.InitialPrincipal, dbo_Liabilities.Status, dbo_Contacts.FirstName, dbo_Contacts.LastName
FROM (dbo_AssetOwners INNER JOIN dbo_Liabilities ON dbo_AssetOwners.AssetID = dbo_Liabilities.LoanID) INNER JOIN dbo_Contacts ON dbo_AssetOwners.ContactID = dbo_Contacts.ContactID;

Is this any help? many thanks

 

[jmersing]

OK, so in the end you want to have the query results like this?

LoanID ContactID LastName FirstName Status Principal
12345 111 Smith John Ok $1000
112 Smith Mary Ok $1000
123456 151 Jones Tom Ok $5000
152 Jones Mary Ok $5000
153 Jones Tom Jr. Ok $5000

Paste this in a browser to view it:

Book1
	A	B	C	D
1	LoanIDContactIDLastNameFirstNameStatusPrincipal
2	12345111SmithJohnOk$1000
3	112SmithMaryOk$1000
4	123456151JonesTomOk$5000
5	152JonesMaryOk$5000
6	153JonesTomJr.Ok$5000
Sheet1

Paste this into Internet Explorer to view the sample

Please clarify
Thanks
Jim

 

[seand]

You got it, but only show one instance of the "LoanID" i.e. only show John Smith and Tom Jones with duplicate loan numbers removed

 

[jmersing]

OK I think I kno what you mean try this:
SELECT DISTINCT dbo_CTRL_Lender.LoanID, Last(dbo_CTRL_LenderID.ContactID) AS LastOfContactID, Last(dbo_CTRL_LenderID.Lastname) AS LastOfLastname, Last(dbo_CTRL_LenderID.FirstName) AS LastOfFirstName, Last(dbo_CTRL_LenderID.Status) AS LastOfStatus, Last(dbo_CTRL_Lender.Principal) AS LastOfPrincipal
FROM dbo_CTRL_Lender LEFT JOIN dbo_CTRL_LenderID ON dbo_CTRL_Lender.LoanID = dbo_CTRL_LenderID.LoanID
GROUP BY dbo_CTRL_Lender.LoanID;

Let me know, this will force only one instance of the loan ID and the last value of the contactID. "Last" can be switched with "First" if the order is incorrect.