How to remove duplicates from Form Recordsource

[ittechguy]

I have a rather complicated database (for my skill level at least) which keeps track of customers, buildings, rooms, and equipment which is in the rooms. I've created a Union Query which combines two queries. One of customers and the rooms which they're a POC for (point of contact), the other of Customers and the buildings which they are the facility manager for). I will be adding my equipment table to both queries. I do not think its possible to use this kind of a query without creating duplicate data. And that is exactly the problem I'm running into.

I have a search form which filters this union query based on several criteria. Because one room can have multiple POCs and one customer can be the POC for many rooms, there is lots of duplicate data. In the example below, you can see that because three different people are either the Facility Manager for Building "A" or the Room Point Of Contact for Building A, Room 1100 there are three records that are showing. I need to filter this further so that only one record shows. And I do not care which one per se. The important thing is that I only want to see one record in a combination of txtBuildingID and txtRoomsID. For an example, I could search for Last Name "Smith" and he could be the POC for building "A" (tblFacilityMgr.BuildingFK 1), room "1100" (tblRoomsPOC.RoomsFK 1) and room "120." (tblRoomsPOC.RoomsFK 2) I should then see two records. One record would contain txtBuildingID "1" and txtRoomsID "1". The second record should contain txtBuildingID "1" and txtRoomsID "2."

I think I need to use DCount to filter my search results to remove the duplicates from the RecordSource. But I'm not sure exactly how. I've searched and I can't seem to find a situation on the webs that exactly matches what I'm trying to do. I figure I need to make my union query an actual query instead of an SQL statement in the Click event of cmdSearch. And then I need to somehow have it search for both [tblFacilityMgr.BuildingFK] and [tblRoomsPOC.RoomsFK] for duplicates and then display only unique records in the recordsource.

Private Sub cmdSearch_Click()Me.RecordSource = "SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblFacilityMgr.CustomerFK, tblFacilityMgr.BuildingFK, tblRooms.RoomsPK" _
& " FROM (tblBuilding INNER JOIN tblRooms ON tblBuilding.BuildingPK = tblRooms.BuildingFK) INNER JOIN" _
& " (tblCustomer INNER JOIN tblFacilityMgr ON tblCustomer.CustomerPK = tblFacilityMgr.CustomerFK) ON" _
& " tblBuilding.BuildingPK = tblFacilityMgr.BuildingFK" _
& " UNION SELECT tblCustomer.LastName, tblCustomer.FirstName, tblCustomer.OrganizationFK," _
& " tblCustomer.ShopNameFK, tblCustomer.OfficeSymFK, tblRoomsPOC.CustomerFK, tblRooms.BuildingFK, tblRoomsPOC.RoomsFK" _
& " FROM tblRooms INNER JOIN (tblCustomer INNER JOIN tblRoomsPOC ON tblCustomer.CustomerPK = tblRoomsPOC.CustomerFK)" _
& " ON tblRooms.RoomsPK = tblRoomsPOC.RoomsFK"
Dim strWhere As String
Dim lngLen As Long
     If Not IsNull(Me.cboSearchLastName) Then
          strWhere = strWhere & "[LastName] ='" & Me.cboSearchLastName & "' AND "
             End If
             If Not IsNull(Me.cboSearchFirstName) Then
          strWhere = strWhere & "[FirstName] ='" & Me.cboSearchFirstName & "' AND "
             End If
             If Not IsNull(Me.cboSearchOrganization) Then
          strWhere = strWhere & "[OrganizationFK] =" & Me.cboSearchOrganization & " AND "
             End If
             If Not IsNull(Me.cboSearchShopName) Then
          strWhere = strWhere & "[ShopNameFK] =" & Me.cboSearchShopName & " AND "
             End If
             If Not IsNull(Me.cboSearchOfficeSym) Then
          strWhere = strWhere & "[OfficeSymFK] =" & Me.cboSearchOfficeSym & " AND "
             End If
             If Not IsNull(Me.cboSearchBuildingName) Then
          strWhere = strWhere & "[BuildingFK] =" & Me.cboSearchBuildingName & " AND "
             End If
             If Not IsNull(Me.cboSearchRoomName) Then
          strWhere = strWhere & "[RoomsFK] =" & Me.cboSearchRoomName & " AND "
             End If
        Call MsgBox(strWhere, vbOKOnly, "Debug")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        strWhere = Left$(strWhere, lngLen)
        Call MsgBox(strWhere, vbOKOnly, "Debug")
         MsgBox "No Records Found."
      Dim Msg As VbMsgBoxResult
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

[llkhoutx]

A query using the count function will indicate which rows are duplicate, but it won't tell you which you need to delete. It coukd be the 1st, last, or some other. That's your real problem, that is, which to delete or filter out.

[ittechguy]

Thank you for your reply.

I don't care which one gets deleted, just as long as there is only records with a unique combination of building id and room id.

[llkhoutx]

Query the query identifying duplicate records, deleting the first occurrence of every duplicate. If 3 or more duplicates, repeat the process.

Alternatively, use cycle through DAO recordsets of duplicates deleting all but the first of each "duplicate."

[ittechguy]

I see. What would the syntax be for using DCount? I can figure out how to do it w/ one text box, but I need to compare BOTH txtBuildingID and txtRoomsID. The combination must be unique.

[llkhoutx]

Use a grouped query with a count column.

[ittechguy]

First, I've made several changes to my code because it wasn't quite working the way I needed it to.

Option Compare DatabaseOption Explicit  'always set this  It will point out errors with field/vaiable names




Private Sub cboSearchLastName_AfterUpdate()
    Me.cboSearchFirstName.Requery
End Sub


Private Sub cboSearchOrganization_AfterUpdate()
    Me.cboSearchShopName.Requery
End Sub


Private Sub cboSearchShopName_AfterUpdate()
    Me.cboSearchOfficeSym.Requery
End Sub


Private Sub cmdReset_Click()
    Me.cboSearchBuildingName = ""
    Me.cboSearchRoomName = ""
    Me.cboSearchOrganization = ""
    Me.cboSearchShopName = ""
    Me.cboSearchOfficeSym = ""
    Me.cboSearchLastName = ""
    Me.cboSearchFirstName = ""
    Me.FilterOn = False
End Sub
Private Sub txtBuildingID_AfterUpdate()
    Me.lstFacilityMgr.Requery
End Sub


Private Sub txtRoomsID_AfterUpdate()
    Me.lstRoomsPOC.Requery
End Sub
Private Sub cmdSearch_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Dim startStr As String
    If Not IsNullOrEmpty(Me.cboSearchLastName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[LastName] ='" & Me.cboSearchLastName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchFirstName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[FirstName] ='" & Me.cboSearchFirstName & "'"
    End If
    If Not IsNullOrEmpty(Me.cboSearchOrganization) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[OrganizationFK] =" & Me.cboSearchOrganization
    End If
    If Not IsNullOrEmpty(Me.cboSearchShopName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        
        strWhere = strWhere & startStr & "[ShopNameFK] =" & Me.cboSearchShopName
    End If
    If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[OfficeSymFK] =" & Me.cboSearchOfficeSym
    End If
    If Not IsNullOrEmpty(Me.cboSearchBuildingName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[BuildingFK] =" & Me.cboSearchBuildingName
    End If
    If Not IsNullOrEmpty(Me.cboSearchRoomName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[RoomsPK] =" & Me.cboSearchRoomName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentName) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[EquipmentNameFK] =" & Me.cboSearchEquipmentName
    End If
    If Not IsNullOrEmpty(Me.cboSearchEquipmentSerialNo) Then
        startStr = IIf(strWhere = "", "", " AND ")
        strWhere = strWhere & startStr & "[SerialNoFK] =" & Me.cboSearchEquipmentSerialNo
    End If
    Call MsgBox(strWhere, vbOKOnly, "Debug")
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
        'strWhere = Left$(strWhere, lngLen)
        MsgBox strWhere
         If DCount("*", "qryRecordSet", strWhere) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
                Me.FilterOn = False
                Me.cboSearchBuildingName = ""
                Me.cboSearchRoomName = ""
                Me.cboSearchOrganization = ""
                Me.cboSearchShopName = ""
                Me.cboSearchOfficeSym = ""
                Me.cboSearchLastName = ""
                Me.cboSearchFirstName = ""
         Else
            Me.Filter = strWhere
            Me.FilterOn = True
        End If
    End If
End Sub






Function IsNullOrEmpty(val As Variant) As Boolean
   'First conditional validates for Nothing
   'Second condition validates for an Empty String situation "" or "     "
   Dim ret As Boolean: ret = False
   If IsMissing(val) Then
      ret = True
   ElseIf (val Is Nothing) Then
      ret = True
   ElseIf (val & vbNullString = vbNullString) Then
      ret = True
   ElseIf (Len(Trim(val)) <= 0) Then
      ret = True
   End If
 
   IsNullOrEmpty = ret
End Function

Now, I don't think I can use SQL to get this done (but I started learning about this stuff 2 months ago... I'm likely wrong). Reason being, the form uses a query as its recordsource. That query is filtered (strWhere) by multiple search criteria. And then filtered results are displayed. I need to then further filter the filtered results (strWhere) to remove duplicates. Can that be done w/ a query?