Use of DISTINCT?

[ibgreat]

I have text search field on a form that is set up to provide the results from two related tables. The search looks for values in the tblAgency.AgencyName and tsubAgencySubName for matches.

tblAgency
AgencyID
AgencyName
AgencyActive

tsubAgency
AgencySubID
AgencyID
AgencySubName
....
AgencySubActive

The search is no problem. But the results are. I want to only list unique values of tblAgency.AgencyName in the resulting listbox. Using DISTINCT returns unique values across all the columns. Unfortunately, AgencySubID and AgencySubName have to be included in the query to get the results. Do I need to apply a filter or query the query after the initial results are returned? Not sure how to go about this within the VBA. This is how the SQL statement is currently being built.


Dim SQL As String

SQL = "SELECT DISTINCT tblAgency_S.AgencyID, tblAgency_S.AgencyName, tsubAgency_S.AgencySubID, "
SQL = SQL & "tsubAgency_S.AgencySubName, tsubAgency_S.CityAgency, tsubAgency_S.StateAgency "
SQL = SQL & "FROM tblAgency_S LEFT JOIN tsubAgency_S ON tblAgency_S.AgencyID = tsubAgency_S.AgencyID "

'add the txtSearchFor criteria if a value is entered
If Not IsNull(Me.txtSearchFor) Then
SQL = SQL & "WHERE (AgencySubName like '*" & Me.txtSearchFor & "*' OR AgencyName like '*" & Me.txtSearchFor & "*') "
End If

SQL = SQL & "ORDER BY AgencyName"

Me.lstAgencyResultsSimple.RowSource = SQL

[ibgreat]

:anyone:

[CreganTur]

From the research I've done it looks like DISTINCT can be used only on a statement level- it affects all records returned- it won't work on a single field in a statement.

That being said, the easiest way around this would probably to save the original query (without the DISTINCT keyword) as an actual Access Querry. Then just query the query for the AgencyID you actually want.

[Slyboots]

I'm at home, and don't have a query engine available here, but I believe the solution is to use a nested query, like this:

SELECT DISTINCT tblAgency_S.AgencyID from
(SELECT tblAgency_S.AgencyID, tblAgency_S.AgencyName, tsubAgency_S.AgencySubID,tsubAgency_S.AgencySubName, tsubAgency_S.CityAgency, tsubAgency_S.StateAgency
FROM tblAgency_S LEFT JOIN tsubAgency_S ON tblAgency_S.AgencyID = tsubAgency_S.AgencyID)

The query in parentheses returns the entire result set, and then the DISTINCT query returns the results you need.

S

[ibgreat]

I believe the solution is to use a nested query,

That makes sense. It worked on one with several criteria settings being pulled from the form. ( I didn't end up being able to use CreganTur's suggestion due to the multiple variables.) I have to try it in one other place, but I think that was it.

Many thanks!