1
votes

I'm building a rather complex database for my limited (yet slowly growing) knowledge of database and programming. So, I really appreciate your help.

The database keeps track of customers, buildings, rooms, and equipment which is in rooms.

I have a search form which filters a union query I've made that queries almost the entire database. The SQL is below:

SELECT tblcustomer.organizationfk, 
   tblcustomer.shopnamefk, 
   tblcustomer.officesymfk, 
   tblcustomer.lastname, 
   tblcustomer.firstname, 
   tblfacilitymgr.buildingfk, 
   tblrooms.roomspk, 
   tblbuilding.buildingname, 
   tblrooms.roomname, 
   tblcabinet.cabinetname, 
   tblequipment.cabinetfk, 
   tblequipment.equipmentnamefk, 
   tblequipment.equipmentbrandfk, 
   tblequipment.equipmentnetworktypefk 
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) 
   LEFT JOIN (tblcabinet 
              LEFT JOIN tblequipment 
                     ON tblcabinet.cabinetpk = tblequipment.cabinetfk) 
          ON tblrooms.roomspk = tblcabinet.roomsfk 
UNION 
SELECT tblcustomer.organizationfk, 
   tblcustomer.shopnamefk, 
   tblcustomer.officesymfk, 
   tblcustomer.lastname, 
   tblcustomer.firstname, 
   tblrooms.buildingfk, 
   tblrooms.roomspk, 
   tblbuilding.buildingname, 
   tblrooms.roomname, 
   tblcabinet.cabinetname, 
   tblequipment.cabinetfk, 
   tblequipment.equipmentnamefk, 
   tblequipment.equipmentbrandfk, 
   tblequipment.equipmentnetworktypefk 
FROM   ((tblbuilding 
     INNER JOIN tblrooms 
             ON tblbuilding.buildingpk = tblrooms.buildingfk) 
    LEFT JOIN (tblcabinet 
               LEFT JOIN tblequipment 
                      ON tblcabinet.cabinetpk = tblequipment.cabinetfk) 
           ON tblrooms.roomspk = tblcabinet.roomsfk) 
   INNER JOIN (tblcustomer 
               INNER JOIN tblroomspoc 
                       ON tblcustomer.customerpk = tblroomspoc.customerfk) 
           ON tblrooms.roomspk = tblroomspoc.roomsfk;

The search form looks like this:

Search Form

The code for cmdsearch is here:

 Option Compare Database
 Option 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

My search form works perfectly. The problem I'm having is that some searches returns duplicate results. This is because the union query contains duplicate results. I do not know of a way to produce a query which contains all the info I need, and not make duplicate results. Because, one customer can be the facility manager for multiple buildings, one building can have multiple facility managers. One customer can be the point of contact for multiple rooms, one room can have multiple POCs. Equipment can only be in one room.

To clarify, if Building "A" , room "1100" has three POCs then if I search for building A, room 1100 I see three results. I need to only see one result.

I have two text boxes on my form (hidden so user doesn't see them). txtBuildingID and txtRoomsID. Everything else on the form requeries based on those two text boxes. I need the combination of those two text boxes to be unique. That is because, if I search for only building "A" and building A has three rooms, I should see Building ID 1 / Room ID 1... Building ID 1 / Room ID 2, etc.

The reason for this is because I want to filter using multiple criteria, but display only a unique building id / room id. This is because if I search for "Smith" and he is the POC for building A, Room 1100, I want to see that building / room and all information about that room. Because, if smith doesn't answer his phone, I can call "Jones".

I do not care which record gets deleted, so long as I have unique records between the two text boxes. I don't know enough about SQL, but from what little I know I don't think this can be done with SQL. I think I need to use perhaps DCount to count the records and then remove duplicates. I have no idea how to do this. I've spent days on google, I haven't even come close to a solution. At best, if I ever get the syntax right I can count the number of duplicates (more than 1 unique record between buildingfk and roomspk), but then I'm not sure how to delete duplicates. Or if I need to specify which duplicate to delete. Personally as long as they are deleted, I don't care.

I asked a friend of mine who is very good w/ programming, and it stumped him. He advised I ask here. So I really appreciate your help here. Query

1

1 Answers

0
votes

I'm making some progress.

I've been able to successfully count the number of records.

The code I use is here:

If DCount("*", "qryRecordSet", "BuildingFK = " & Me.txtBuildingID & " And      RoomsPK = " & Me.txtRoomsID > 1) Then
              MsgBox "There are duplicates!" & vbCrLf & vbCrLf
              'Code to remove duplicate records here
              End If

The MsgBox is only for troubleshooting purposes. But I have tested it and confirmed it works. Now, I need to apply this to the form filter, somehow. That would be Me.Filter. Currently, that is Me.Filter = strWhere.

Basically, I need to further filter out strWhere so there are no more duplicates. How do i do that?