Query with multiple parameters but don't bring up duplicate rows?

[magpieros]

Hi all. I've been noodling with creating a contact list of contractors and one of the functions I need it to serve is bringing up lists of contractors based on their discipline (HVAC, electrical, etc.). I was advised a little while ago on the pitfalls of MVFs and adjusted to using a junction table, which so far has worked very well. I haven't tried to tackle bringing that information into my main continuous form yet, as I know that'll require more tussle with VBA than I'm up for at the moment, but I've been trying to make a query that can break them out into smaller forms.

The query functions, but the problem is that I'm getting duplicate records when querying multiple parameters. As an example, a lot of commercial plumbing contractors also do HVAC, so these would likely be searched together, but when I do the records come up twice. I've searched around and I've tried putting SELECT DISTINCT and DISTINCTROW in my query and it hasn't changed the result. Any advice?

 

[Gasman]

Perhaps use concatenation to bring all the disciplines together for each contractor?

Code Snippets

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[MajP]

To be clear you want to search for companies meeting more than one criteria. For example return all distinct companies that do both HVAC and Plumbing? You can do that in a query but to build a search form to do that you will likely have to generate the sql string in code. I assume you would have a multi select listbox where you select 1 to N job types and return those companies doing all N job types.
The reason you have to do it in code is that part of the query requires a literal number.

SELECT DISTINCT c.CompanyName
FROM tblCompanies AS c
INNER JOIN tblCompanyJobs AS cj ON c.CompanyID = cj.CompanyID
INNER JOIN tblJobTypes AS j ON cj.JobTypeID = j.JobTypeID
WHERE j.JobTypeName IN ("Plumbing","HVAC")
GROUP BY c.CompanyName
HAVING COUNT(DISTINCT j.JobTypeName) = 2

You would have to modify this query to figure out how many items selected in the multiselect list box and adjust the count. Then you would have to adjust the IN portion for the selection. If that is what you want we can demo, but be better if you provided your tables.

 

[magpieros]

Perhaps use concatenation to bring all the disciplines together for each contractor?

This is great, thanks! This is actually exactly how I want to display the disciplines in my overview as well, but the response I got for how to go about it from Reddit was pretty confusing and didn't provide a lot of context. I'll try this out!

 

[Ken Sheridan]

The attached little demo file uses a multi-select list box in the way described by MajP to select multiple parameters. A report is then opened, with the choice of returning all rows which match all or any of the parameters. The methodology used is similar to tMajP's query, but in my case referencing the list box's ItemsSelected.Count property.

 

[magpieros]

To be clear you want to search for companies meeting more than one criteria. For example return all distinct companies that do both HVAC and Plumbing?

You would have to modify this query to figure out how many items selected in the multiselect list box and adjust the count. Then you would have to adjust the IN portion for the selection. If that is what you want we can demo, but be better if you provided your tables.

Yeah, the SQL I have for my query right now is

SELECT
    tblContractors.ContractorID, tblContractors.CompanyName, tblContractors.PrimaryContact,
    tblContractors.Phone, tblContractors.Phone2, tblContractors.Email, tblContractors.Address,
    tblContractors.City, tblContractors.County, tblContractors.State, tblDisciplineJunction.*, tblDisciplines.*
FROM
    (
        tblContractors
        INNER JOIN tblDisciplineJunction ON [tblContractors].[ContractorID] = [tblDisciplineJunction].[ContractorID]
    )
    INNER JOIN tblDisciplines ON [tblDisciplineJunction].[DisciplineID] = [tblDisciplines].[DisciplineID]
WHERE
    ((InStr (
                [Enter Discipline(s), separated by commas],
                [DisciplineName]
            )) > "0"
    );

This is my list of disciplines and an example of my junction. In an ideal world, I get the concatenation working and I can put the disciplines in-line in the overview. I want people to be able to search for all of the disciplines for their project at once and work off of a single list, but if avoiding duplicates means coming up with individual queries then I may just restrict the discipline lookup to a single discipline at a time. I hope this all makes sense and I've given the information you're looking for, I really appreciate your help.

 

[RonPaii]

You can use a sub-select to git rid of the district

SELECT tblCompanies.CompanyName
FROM tblCompanies
WHERE tblCompanies.CompanyID
In (SELECT JobTypeID.CompanyID
   FROM JobTypeID
   WHERE JobTypeID.JobTypeName  IN ("Plumbing","HVAC"));

 

[Ken Sheridan]

RonPaii's solution restricting the outer query by means of a subquery is much better than simple joins. However, it does not return the Disciplines available from each contractor. This can be overcome by calling a concatenation function. The following is an example of mine which uses the highly efficient GetString method of the ADO Recordset object:

Public Function GetList(strTable As String, strColumn As String, strSortColumn As String, strDelim As String, Optional strFilter As String = "True") As String

    Const NOCURRENTRECORD = 3021
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strList As String
  
    strSQL = "SELECT " & strColumn & " FROM " & strTable & " WHERE " & strFilter & " ORDER BY " & strSortColumn
 
    Set rst = New ADODB.Recordset
  
    With rst
        Set .ActiveConnection = CurrentProject.Connection
        .Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
      
        On Error Resume Next
        strList = .GetString(adClipString, , strDelim, strDelim)
        .Close
        Select Case Err.Number
            Case 0
            ' no error so remove trailing delimiter
            ' and return string
            GetList = Left(strList, Len(strList) - Len(strDelim))
            Case NOCURRENTRECORD
            ' no rows in table so return
            ' zero length string
            Case Else
            ' unknown error
            GetList = "Error"
        End Select
    End With
  
End Function

Another problem is that the IN operator does not accept a parameter as its argument. Microsoft published the following module as a solution to this many years ago:

' Module basInParam
' The functions in this module were published by Microsoft, but
' the article in question is no longer available.
' They are used in this demo database to simulate the use of the
' IN operator in a query, but by accepting a value list as a parameter
' rather than a literal value list as the IN operator requires.
' The parameter in this case is a hidden control in a form
' from which a report is opened.

Option Compare Database
Option Explicit


Function GetToken(stLn, stDelim)
  
    Dim iDelim As Integer, stToken As String
  
    iDelim = InStr(1, stLn, stDelim)
  
    If (iDelim <> 0) Then
        stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
        stLn = Mid$(stLn, iDelim + 1)
    Else
        stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
        stLn = ""
    End If
  
    GetToken = stToken
  
End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the IN operator.
'============================================================
Function InParam(Fld, Param)

    Dim stToken As String
    'The following two lines are optional, making queries
    'case-insensitive
    Fld = UCase(Fld)
    Param = UCase(Param)
  
    If IsNull(Fld) Then Fld = ""
    Do While (Len(Param) > 0)
        stToken = GetToken(Param, ",")
        If stToken = LTrim$(RTrim$(Fld)) Then
            InParam = -1
            Exit Function
        Else
            InParam = 0
        End If
    Loop

End Function

The InParam function is called in a query like this:

InParam(ColumnNameGoesHere,ParameterGoesHere) = TRUE

Normally the parameter would be a reference to a text box control in a form rather than a parameter prompt as described in the remark line above. Note that text values in the value list in the control do not need to be delimited with quotes.

Alternatively the SQL statement can be built in code, using the IN operator and concatenating the value list into the string expression. In this case text values must be delimited with quotes.

Calling the Instr function is not something I'd recommend, as it can return specious substring matches.

My demo file, which I attached to my last post, removes any duplication by returning the list of 'disciplines' (fruit in the demo) in a multi-column subreport, highlighting the selected items by means of conditional formatting, as in the following image, in which the search was for Apples and Oranges.:

 

[RonPaii]

Ken said
"Another problem is that the IN operator does not accept a parameter as its argument."

Instead of a function, wouldn't it be great if Microsoft allow us to pass IN as a parameter.

 

[Ken Sheridan]

Instead of a function, wouldn't it be great if Microsoft allow us to pass IN as a parameter.

If only!

 

[magpieros]

Much appreciated, thanks!

 

[MajP]

Here is a form demo with the concat and search in one. This is basically the same approach as Ken's just packaged differently.

If you search for both Plumbing and HVAC

If you want either plumbing or hvac