Return only 1 result with 1 to many tables

[redekopp]

Good afternoon, I am trying to create a search form that will return only one EmployeeID no matter how many times that employeeID shows up in various tables.



The image above shows the Table Employees, and then 3 subsequent tables for Certs/Skills/ProfessionalCerts.

Each of those 3 subsequent tables could have many different employees per certs, and each employee could have many different certs from each of those tables.

I want to be able to search by all criteria, AKA find someone who is a journeyman plumber from the certs table and also an IT specialist from the Professional Cert table, but only returning that employee once.



My expected result is one employee, but this is my returned result:





Anyone have any idea how I can get that to return just 1 result? I tried Group By but that didnt work either.

Thank you!

[Bob Fitz]

Have you tried Group By and remove the "Skill" column

[redekopp]

Just gave it a go and that appeared to work, thanks for that.

The question now is assuming that I need all of those columns (there are actually 4 more tables to add that are the same as the other 3 in terms of how they act), so I would need those fields as well in order for the search form to have something to run against. (Skill, etc)

So how can I have the fields available to run against, whilst still returning only 1 result?

Thanks!

[Bob Fitz]

Another way to return just one record might be to just enter 1 in the "Return" Top box shown in the ribbon.

[redekopp]

I tried that, but I think it is also only returning 1 employee, when there could be many different employees that match that description

Actually, its because the person needed to have a record in all 3 tables or they wouldnt show up, so I suppose that this is still an issue that they need to have a record in the skills table if the field is an option.

Any way to get around it so that I can use all the tables, and the main fields from all the tables selectively?

[CJ_London]

Actually, its because the person needed to have a record in all 3 tables or they wouldnt show up

you would use a left join between tblEmployees and all the other tables

[ssanfu]

Other possible options:

- Maybe a 7 query UNION query, then a Group By query with the union query as the source.

- Could brute force it: Use VBA to enter data into a temp table (records temp, table permanent)

[redekopp]

you would use a left join between tblEmployees and all the other tables

Hi Ajax, I gave that a go but its still creating 3 of the one employee. See Screenshots

[CJ_London]

Not sure what your objective is, but remove the skill column and either group by or use SELECT DISTINCT

It looks to me like your tables are not constructed correctly (looking at your first post), there is no link between cert, profcert or skill which are all basically the same 'object'. They should all be in one table with an additional column 'QualType' or similar - basically your table name meaning.

Properly constructed, I don't see the need for your sub queries

[redekopp]

Not sure what your objective is, but remove the skill column and either group by or use SELECT DISTINCT

It looks to me like your tables are not constructed correctly (looking at your first post), there is no link between cert, profcert or skill which are all basically the same 'object'. They should all be in one table with an additional column 'QualType' or similar - basically your table name meaning.

Properly constructed, I don't see the need for your sub queries

Hi thanks for the response, I am by no means an expert so I am sure you are right that I had originally built it wrong, I have attached more screenshots to kind of outline what it looks like in a database and what I am trying to accomplish:

The client form:

As you can see a bunch of subforms to collect the type of cert to this particular client, or the type of tickets or skills to this employee.

The Search Form:

The ability to search by skills/tickets/certs all at once and find an employee that matches that particular skillset.

The Search forms qry:

Definitely where everything has gone wrong. I am open to rebuilding tables if that's what it takes.

Here is the code that runs the search form (although a lot is commented out currently during testing)

'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Dim i As Variant
    Dim criteria As String
    
    
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    'If Not IsNull(Me![cbostatus].ItemsSelected) Then
        
           ' Build criteria string from selected items in list box.
           ' criteria = "("
           ' For Each i In Me![cbostatus].ItemsSelected
           '    If criteria <> "(" Then
           '       criteria = criteria & " OR "
           '    End If
           '    criteria = criteria & "([Employment Status]= """ & Me![cbostatus].ItemData(i) & """)"
          '  Next i
                                  
       ' If criteria = "(" Then
       ' criteria = ""
        'strWhere = strWhere & "([Employment Status] = """ & Me.cbostatus & """) AND "
       ' strWhere = strWhere
       ' Else
        
      '  strWhere = criteria & ") AND "
       ' End If
   ' End If
     If Not IsNull(Me.txtStartAge) Then
        strWhere = strWhere & "((Int((Date()-[Birthdate])/365.25)) >= " & Me.txtStartAge.Value & "   ) AND "
    End If
         If Not IsNull(Me.txtEndAge) Then
        strWhere = strWhere & "((Int((Date()-[Birthdate])/365.25)) <= " & Me.txtEndAge.Value & "   ) AND "
    End If
    
    
    If Not IsNull(Me.txtFirstName) Then
       strWhere = strWhere & "([FirstName] like ""*" & Me.txtFirstName & "*"") AND "
    End If
    
    If Not IsNull(Me.txtLastName) Then
       strWhere = strWhere & "([LastName] like ""*" & Me.txtLastName & "*"") AND "
    End If
    
    If Not IsNull(Me.cboCity) Then
       strWhere = strWhere & "([City] like ""*" & Me.cboCity & "*"") AND "
    End If
    
    If Not IsNull(Me.txtTreatyNum) Then
       strWhere = strWhere & "([Treaty#] like ""*" & Me.txtTreatyNum & "*"") AND "
    End If
    
    If Not IsNull(Me.Province) Then
       strWhere = strWhere & "([Province] like ""*" & Me.Province & "*"") AND "
    End If
    
    If Not IsNull(Me.EmployeeNumber) Then
       strWhere = strWhere & "([EmployeeNumber] like ""*" & Me.EmployeeNumber & "*"") AND "
    End If
    
    If Not IsNull(Me.FundingSource) Then
       strWhere = strWhere & "([FundingSource] like ""*" & Me.FundingSource & "*"") AND "
    End If
    
    If Not IsNull(Me.LabourReady) Then
       strWhere = strWhere & "([LabourReady] like ""*" & Me.LabourReady & "*"") AND "
    End If
    
    If Not IsNull(Me.SocialInsurance) Then
       strWhere = strWhere & "([SocialInsurance] like ""*" & Me.SocialInsurance & "*"") AND "
    End If
    
    If Not IsNull(Me.cboGender) Then
       strWhere = strWhere & "([Gender] like ""*" & Me.cboGender & "*"") AND "
    End If
    
    If Not IsNull(Me.cboAbStatus) Then
       strWhere = strWhere & "([Aboriginal Status] like ""*" & Me.cboAbStatus & "*"") AND "
    End If
    
    If Me.TCMOnly.Value = -1 Then
    
       strWhere = strWhere & "([STCBand] = True) AND "
      
    Else
        If Not IsNull(Me.cboBand) Then
            strWhere = strWhere & "([Band Name] = " & Me.cboBand & ") AND "
        End If
    End If
    
    
    If Not IsNull(Me.DriversLicense) Then
       strWhere = strWhere & "([DriversLicense#] like ""*" & Me.DriversLicense & "*"") AND "
    End If
    
    If Not IsNull(Me.DriversClass) Then
       strWhere = strWhere & "([Class] like ""*" & Me.DriversClass & "*"") AND "
    End If
      
    
    
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    If Me.Active = -1 Then
        strWhere = strWhere & "([Active] = True) AND "
    ElseIf Me.Active = 0 Then
        strWhere = strWhere & "([Active] = False) AND "
    End If
    
    If Me.STCMember = -1 Then
        strWhere = strWhere & "([STCMember] = True) AND "
    ElseIf Me.STCMember = 0 Then
        strWhere = strWhere & "([STCMember] = False) AND "
    End If
    
    If Me.FollowUp = -1 Then
        strWhere = strWhere & "([Follow Up] = True) AND "
    ElseIf Me.FollowUp = 0 Then
        strWhere = strWhere & "([Follow Up] = False) AND "
    End If
    
    
  


    'Another text field example. Use Like to find anywhere in the field.
    'If Not IsNull(Me.txtFilterMainName) Then
    '    strWhere = strWhere & "([OrgLegalName] Like ""*" & Me.txtFilterMainName & "*"") AND "
    'End If




    'Number field example. Do not add the extra quotes.
    'If Not IsNull(Me.cboFilterLevel) Then
    '    strWhere = strWhere & "([AppStatus] = " & Me.cboFilterLevel & ") AND "
    'End If
    
    
    'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
    'If Me.chkLetter = -1 Then
    '    strWhere = strWhere & "([letter] = True) AND "
    'ElseIf Me.chkLetter = 0 Then
    '    strWhere = strWhere & "([letter] = False) AND "
    'End If
   
        
    'Date field example. Use the format string to add the # delimiters and get the right international format.
   ' If Not IsNull(Me.txtHireStartDate) Then
  '      strWhere = strWhere & "([StartDate] >= " & Format(Me.txtHireStartDate, conJetDate) & ") AND "
  '  End If
    
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
  '  If Not IsNull(Me.txtHireEndDate) Then   'Less than the next day.
   '     strWhere = strWhere & "([StartDate] < " & Format(Me.txtHireEndDate + 1, conJetDate) & ") AND "
  '  End If
    
   ' If Not IsNull(Me.txtLastStartDate) Then
     '   strWhere = strWhere & "([lastworkdate] >= " & Format(Me.txtLastStartDate, conJetDate) & ") AND "
   ' End If
    
   ' If Not IsNull(Me.txtLastEndDate) Then   'Less than the next day.
    '    strWhere = strWhere & "([lastworkdate] < " & Format(Me.txtLastEndDate + 1, conJetDate) & ") AND "
  '  End If
    


    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailing " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
        
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Thank you!

[CJ_London]

The ability to search by skills/tickets/certs all at once and find an employee that matches that particular skillset.

but you are not searching for skills, so you need to leave them off your query - or perhaps build your query along the lines of the code you have show

I advised the left join because you said 'its because the person needed to have a record in all 3 tables or they wouldnt show up'

The rest of it is a separate issue. If you are not using a table (e.g. skills), you need to exclude it from your query. Which makes your query building more difficult. If you are going to include it you will get duplicates.

Only other suggestion is to use a group by query and use first or max or min or last for the columns that cause the duplicates

[redekopp]

but you are not searching for skills, so you need to leave them off your query - or perhaps build your query along the lines of the code you have show

I advised the left join because you said 'its because the person needed to have a record in all 3 tables or they wouldnt show up'

The rest of it is a separate issue. If you are not using a table (e.g. skills), you need to exclude it from your query. Which makes your query building more difficult. If you are going to include it you will get duplicates.

Only other suggestion is to use a group by query and use first or max or min or last for the columns that cause the duplicates

I mean yeah, not every single time will someone search by skill or cert or tickets, just like they might not search by last name or age every time.

Leaving it out isn't an option as they want to pick and choose what they search by each time. Clearly I have either designed it super wrong and there is a better way, or it's just a limitation in Access.

I will try out the group by suggestion, thank you!

[redekopp]

Update, I made one table for all the ticket/skiills etc information and it appears to be working!