How to do a SELECT DISTINCT that includes the primary key

[Seth Schrock]

I know that I can't actually use the SELECT DISTINCT in a query that includes the primary key value and expect it to remove duplicated values, but I'm looking for that kind of affect.

I have a combo box that is based on a table that has four fields:
AppPermissionID, PK
LoanType_fk
County_fk
Appraiser_fk
This is basically a three-way join table.

Normally, I have the records filtered by both the loan type and the county to get the appraisers that can do appraisals for the selected loan type in the selected county. However, there are certain situations where I want this filter removed. If I simply remove the WHERE clause in the row source of my combo box, then I get each appraiser listed multiple times. I want each appraiser to be listed only once, but the combo box is bound to the AppPermissionID field so I can't just do a SELECT DISTINCT query on the Appraiser_fk field. I'm thinking that I saw a solution one time that used the Count() function, but I can't find it again nor can I figure it out.

[Rabbit]

How do you choose which AppPermissionID you want? If it doesn't matter, you can just use a min or max aggregate query on AppPermissionID grouping by the Appraiser_fk.

Having said that, I question the logic of binding to the AppPermissionID when really the user is choosing an Appraiser_fk. If the user is choosing an Appraiser_fk, then the data should reflect that, not an arbitrary AppPermissionID.

[Seth Schrock]

Here is the thread that helped me come up with this solution: Many-to-Many-to-Many Relationship Problem. I see your point, though. I will see what I can come up with. I think that I might have an idea and it would solve this problem.

In this case, I don't think that it really matters which AppPermissionID I get since the filter is off and I can still get the appraiser that is tied to any of the AppPermissionIDs.

I will work on your solution to get the functionality out since it wouldn't cause a big design change, but I will also continue to work on getting the logic better.

[Seth Schrock]

Wow! That was so easy to fix the logic so that I'm not bound to the AppPermissionsID. I can't believe that I didn't come up with that sooner. Evidently I was looking for a hard solution, and I sure found it :)

Thanks Rabbit for inspiring me to fix the logic.

[Rabbit]

Glad to help!