How can I avoid duplicates in a query programmatically?

[TryingMyBest]

I'm creating a search form for my application and have copied code from one of the example search forms that I found on this forum.

So, here is my code so far:

Private Sub cmdSearch_Click()
'On Error Resume Next

    Dim sSql As String
    Dim sCriteria As String
        sCriteria = "WHERE 1=1"

        If Me.cboStatus <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.AuditStatus = """ & cboStatus & """"
        End If
        
        If Me.cboProgrammeName <> "" Then
            sCriteria = sCriteria & " AND qryAuditListSub.ProgrammeName like """ & cboProgrammeName & "*"""
        End If
        
        If Me.cboProject <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.ProjectName = """ & cboProject & """"
        End If
        
        If Me.cboTeam <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.TeamName = """ & cboTeam & """"
        End If
        
        If Me.cboLocation <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.Location = """ & cboLocation & """"
        End If
        
        If Me.cboAuditor <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.FullName = """ & cboAuditor & """"
        End If
        
        If Me.cboProcess <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.ProcessName = """ & cboProcess & """"
        End If
        
        If Me.cboAuditee <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.Auditee = """ & cboAuditee & """"
        End If
        
        If Me.cboResponsiblePerson <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.ResponsiblePerson = """ & cboResponsiblePerson & """"
        End If
        
        If Me.cboNCRStatus <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.NCRStatus = """ & cboNCRStatus & """"
        End If
        
        If Me.cboNCCategory <> "" Then
                sCriteria = sCriteria & " AND qryAuditListSub.DeficiencyLevel = """ & cboNCCategory & """"
        End If
        
        If Me.txtConductedStartDate <> "" And Me.txtConductedEndDate <> "" Then
            sCriteria = sCriteria & " AND qryAuditListSub.AuditDate between #" & Format(txtConductedStartDate, "dd-mmm-yyyy") & "# and #" & Format(txtConductedEndDate, "dd-mmm-yyyy") & "#"
        End If
                    
        If Me.txtCompletedStartDate <> "" And Me.txtCompletedEndDate <> "" Then
            sCriteria = sCriteria & " AND qryAuditListSub.AuditClosureDate between #" & Format(txtCompletedStartDate, "dd-mmm-yyyy") & "# and #" & Format(txtCompletedEndDate, "dd-mmm-yyyy") & "#"
        End If
   
    
        sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        Forms![frmAuditListMain]![frmAuditListSub].Form.RecordSource = sSql
        Forms![frmAuditListMain]![frmAuditListSub].Form.Requery
        
End Sub

Here is the SQL code for qryAuditListSub:

SELECT DISTINCTROW TBLAuditData.AuditNumber, TBLAuditData.AuditStatus, TBLProgrammeData.ProgrammeName, TBLProjectData.ProjectName, TBLTeamData.TeamName, TBLAuditRecords.ProcessName, TBLLocation.Location, TBLPeopleData.FullName, TBLAuditData.Auditee, TBLAuditData.ResponsiblePerson, TBLAuditData.AuditDate, TBLAuditData.AuditClosureDate, TBLNCRRecords.NCRStatus, TBLNCRRecords.DeficiencyLevel
FROM ((((((TBLAuditData LEFT JOIN TBLProgrammeData ON TBLAuditData.ProgrammeNumber = TBLProgrammeData.ProgrammeID) LEFT JOIN TBLProjectData ON TBLAuditData.ProjectName = TBLProjectData.ProjectID) LEFT JOIN TBLTeamData ON TBLAuditData.TeamName = TBLTeamData.TeamID) LEFT JOIN TBLPeopleData ON TBLAuditData.AssignedAuditor = TBLPeopleData.PersonID) LEFT JOIN TBLLocation ON TBLAuditData.AuditLocation = TBLLocation.LocationID) LEFT JOIN TBLAuditRecords ON TBLAuditData.AuditNumber = TBLAuditRecords.AuditNumber) LEFT JOIN TBLNCRRecords ON TBLAuditData.AuditNumber = TBLNCRRecords.AuditNumber
WHERE ((([ProgrammeName] & "") Like IIf([Forms]![frmAuditListMain]![cboProgrammeName] Is Null,"*",[Forms]![frmAuditListMain]![cboProgrammeName] & "*")));

The problem that I have is that I want to either return multiple rows for the one audit or only one row for the one audit depending on the search criteria. The ProcessName, NCRstatus, and DeficiencyLevel fields can have a many-to-one relationship with the AuditNumber field.

If the person chooses to search using any of the fields that return many records I want to see all of the records for each AuditNumber, otherwise I want to see only one row for each AuditNumber.

SELECT DISTINCT isn't working because there are, for example, many processes per audit so even if the person searches for all audits by ProgrammeName they will see many rows for the one AuditNumber. How can I rework this? Is there a fundamental flaw in the design of my database?

Thanks for your help.

Jo

 

[TryingMyBest]

Almost Fixed!

I've messed a bit and am getting the results I want with the addition of the following code

        If Me.cboProcess <> "" And Me.cboNCRStatus = "" And Me.cboNCCategory = "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCRStatus <> "" And Me.cboProcess = "" And Me.cboNCCategory = "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCCategory <> "" And Me.cboProcess = "" And Me.cboNCRStatus = "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        ElseIf Me.cboProcess <> "" And Me.cboNCRStatus <> "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus] from qryAuditListSub " & sCriteria
        ElseIf Me.cboProcess <> "" And Me.cboNCCategory <> "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCRStatus <> "" And Me.cboNCCategory <> "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        Else
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate] from qryAuditListSub " & sCriteria
        End If
        
            Forms![frmAuditListMain]![frmAuditListSub].Form.RecordSource = sSql
            Forms![frmAuditListMain]![frmAuditListSub].Form.Requery

It's not pretty but it works. Still looking for a better way though

My next question is can I programatically reduce the size of the columns in my datasheet?

Jo

 

[TryingMyBest]

OK I told a fib



The code doesn't work

I have modified it as follows but still it skips past all of the statements until it gets to the last one. When debugging, all of the conditions are met as expected but the code doesn't execute the correct part.

        If Me.cboProcess <> "" [COLOR=Red]And Me.cboNCRStatus = Null And Me.cboNCCategory = Null[/COLOR] Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCRStatus <> "" And Me.cboProcess = Null And Me.cboNCCategory = Null Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCCategory <> "" And Me.cboProcess = Null And Me.cboNCRStatus = Null Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        ElseIf Me.cboProcess <> "" And Me.cboNCRStatus <> "" And Me.cboNCCategory = Null Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus] from qryAuditListSub " & sCriteria
        ElseIf Me.cboProcess <> "" And Me.cboNCCategory <> "" And Me.cboNCRStatus = Null Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCRStatus <> "" And Me.cboNCCategory <> "" And Me.cboProcess = Null Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        ElseIf Me.cboNCRStatus <> "" And Me.cboNCCategory <> "" And Me.cboProcess <> "" Then
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [ProcessName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate], [NCRStatus], [DeficiencyLevel] from qryAuditListSub " & sCriteria
        Else
            sSql = "SELECT DISTINCT [AuditNumber], [AuditStatus], [ProgrammeName], [ProjectName], [TeamName], [Location], [FullName], [Auditee], [ResponsiblePerson], [AuditDate], [AuditClosureDate] from qryAuditListSub " & sCriteria
        End If

I've highlighted the problem code in the first line....it's the same with all of the statements though.
Please help

 

[TryingMyBest]

Got It!!!!!

I needed the IsNull Statement rather than "= Null"

 

[modest]

I just viewed this thread... you cannot compare a value to null because null doesn't exist. It's sorta like dividing a number by 0... it can't be done. Neither can comparing a number to null.

TryingMyBest,
I see that you got this, so I'm just stating it for anyone else who may come across this thread.