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:
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