Query for finding a match from multiple value criteria

[IE_STU]

Hello, this is my first port here..



I am designing a database which keeps track of door access levels at a college, using Access 2010. A door access level is programmed to a key card, which grants access to a number of different doors throughout the college.

So, what I have is an "LevelID" and a "DoorID", where each LevelID has zero to many DoorIDs associated with it, as well as each DoorID has zero to many LevelIDs associated with it.

example table:

LevelID DoorID
1 1
1 2
1 5
1 6

etc..

All the data has been inputted into Access 2010 successfully, however I am having difficulty in developing one of the main functions of the database..

What I want to do is have a checklist of each DoorID displayed on a form, and when any combination of DoorIDs are checked, Access will search to see if a LevelID is associated with that combination. This information can tell me whether a new LevelID is needed to be created.

Thanks in advance,

IE_STU

[Beetle]

You only gave us one example table, but you would need at least three in this model. Without knowing how you have your tables structured it's hard to give advice. Can you provide more insight as to your tables?

[IE_STU]

Thanks for the quick response,

I do have 3 tables in total, however the other two tables just assign names to the respective IDs

1)
LevelID LevelName

2)
DoorID Door Name

3)
*One provided in previous post

That is all I have so far, with the addition of some forms developed for editing/adding entries..

Where should I go from here?

[Beetle]

OK, just to clarify what you want, you want to be able to select two or more Doors from a list and then query to see if all of the Doors selected appear in the junction table with the same LevelID?

If the above is true, then what do you want to happen if only some of the selected Doors meet the condition? For example, you select three Doors from the list. Two of them appear in the table with the same LevelID but the third one does not.

Or, you want something else?

[IE_STU]

Yes, that is what I would like to do..

Whenever only some of the doors meet the condition of a level, I just want a simple "no match available". From here, I can create another access level that incorporates this new combination.

[June7]

Suggestion:

Unbound listbox on form that shows all the doors. Code will loop through the listbox and if row selected search table for the combination. If no match found for any row return the message. Like:

    With Me.lstDoors
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                If IsNull(DLookup("LevelID","tablename", "DoorID=" & .ItemData(varItem) Then
                      MsgBox "No match available."
                      Exit Sub
                End If
            End If
        Next
    End With

[IE_STU]

Not much of a programmer myself..

Which field would I input this code?

[June7]

Suggest code go in the Click event of a command button.

Create button on form, select [Event Procedure] in the Click event property, click the ellipses (...) to open the VBA editor, type or copy/paste code in the procedure.

[IE_STU]

Code works great!

However, I figured what I said earlier wasn't exactly everything I wanted. I want to see if the combination inputted finds and exact LevelID. Additionally, I also want to show the available LevelIDs (as a list, for example ID#1 and ID#2 have this exact combination), this will tell me whether duplicates are present

Thanks again,

[Beetle]

The code from #6 simply loops through the list box selections and determines if any single door has at least one associated level in the junction table. As I understand the problem that's not what you wanted. You can accomplish what you want with a sub query. I'm going to attach a sample db here for demonstration but I'll also try to explain the logic here as well, so you can understand what's going on in the example and try to duplicate it in your app. This is very similar to a post I answered in another forum about Actors and Movies, so a lot of this is a repeat of that post

Going back to the junction table itself, let's use the following example data;




Doors 1, 2 and 3 all relate to Level 1 and 3, but only Doors 1 and 2 relate to Level 2. So in the list box on our form, if we select Doors 1, 2 and 3 we only want to return Levels 1 and 3. If We just do a simple Select query joining the Levels table and the junction table, we get the following results;




This is not what we want, but it gives a clue as to how to proceed. The number of times a row is returned is equal to the number of Doors that meet the criteria so we can use this to our advantage by counting rows. We can use this query as the basis for a sub query. The query we wrap it in will then count the number of times a row is returned and compare that to the number of items selected in the list box. In other words, if we selected 3 items in the list box, then we ultimately only want records that were returned 3 times by the sub query. So suppose we have a search form based on a query of the Levels table with a List box and command buttons in the header for searching. We can do all this in code and just apply the results as a filter to the Search form's record source. Here is the code;

Private Sub cmdSearch_Click()

    Dim intCount As Integer
    Dim lngDoorID As Long
    Dim strDoors As String
    Dim strFilter As String
    Dim strSQL As String
    Dim vItem As Variant
    
    'Determine how many Doors were selected
    intCount = Me.lstDoors.ItemsSelected.Count
    
    If intCount = 0 Then 'No Doors selected
        MsgBox "Please select at least one Door."
    Else
        Select Case intCount
            Case 1 '1 Door selected
            
                'Return the DoorID
                For Each vItem In Me.lstDoors.ItemsSelected
                    lngDoorID = Me.lstDoors.ItemData(vItem)
                Next
                
                'Simple query to return all Levels with this Door
                strSQL = "SELECT tblLevels.LevelID FROM tblLevels " _
                          & "INNER JOIN tblDoorLevels ON tblLevels.LevelID = tblDoorLevels.LevelID " _
                          & "WHERE tblDoorLevels.DoorID=" & lngDoorID
                
            Case Is > 1 'Multiple Doors selected
            
                'Build a comma separated string of DoorIDs
                For Each vItem In Me.lstDoors.ItemsSelected
                    strDoors = strDoors & Me.lstDoors.ItemData(vItem) & ", "
                Next
                
                'Remove the trailing comma & space
                strDoors = Left(strDoors, Len(strDoors) - 2)
                
                'Query with a subquery to select Levels where the Count of records
                'equals the number of Doors selected in the list box
                strSQL = "SELECT T.LevelID FROM " _
                       & "(SELECT tblLevels.LevelID FROM tblLevels " _
                       & "INNER JOIN tblDoorLevels ON tblLevels.LevelID = tblDoorLevels.LevelID " _
                       & "WHERE tblDoorLevels.DoorID In (" & strDoors & ")) AS T " _
                       & "GROUP BY T.LevelID " _
                       & "HAVING Count(T.LevelID)=" & intCount
        End Select
        
        'See if the query returns anything
        With CurrentDb.OpenRecordset(strSQL)
            If .RecordCount = 0 Then 'No records found
            
                MsgBox "There are no Levels that match the Doors selected."
                
            Else 'Records found
            
               'Place the results of the query in a filter
                strFilter = "LevelID In(" & strSQL & ")"
                
                'Apply the filter to the form
                Me.Filter = strFilter
                Me.FilterOn = True
                
            End If
        End With
    End If
    
End Sub

See the attached sample file for demonstration.

[IE_STU]

I'm very close to what I need..

I still don't know how to manipulate the code to determine whether or not an exact combination is found from what doors are selected. For example, I choose only one door, DoorID = 1 (which is the Main Entrance). The search command still returns every LevelID with DoorID=1 (which is almost every LevelID). But, what I want is to see if any LevelID has only DoorID=1 associated with it, no others.

Thanks,

[Beetle]

OK, time for another sub query. In this case the sub query selects all LevelID's from the junction table where the DoorID is not equal to the single door selected in the list box. So, for example, if we have the following data in the junction table;

LevelID  DoorID
   1        1
   1        2
   2        1

In the above case the sub query would select Level1 but would not select Level2

The outer query then selects LevelID's from the junction table that are not in the sub query. You could do this with a Not In clause, but I have chosen to do it here with a Left Join using Null criteria as a demonstration of another way it can be done. Sometimes (not in this case but in some cases) this is easier than building the criteria for a Not In clause so this is just an example. The modified code is below. The only thing that has changed is the SQL string for the first case in the Select Case statement. I have highlighted the section in red.

Private Sub cmdSearch_Click()

    Dim intCount As Integer
    Dim lngDoorID As Long
    Dim strDoors As String
    Dim strFilter As String
    Dim strSQL As String
    Dim vItem As Variant
    Dim blnNoRecords As Boolean
    
    'Determine how many Doors were selected
    intCount = Me.lstDoors.ItemsSelected.Count
    
    If intCount = 0 Then 'No Doors selected
        MsgBox "Please select at least one Door."
    Else
        Select Case intCount
            Case 1 '1 Door selected


                'Return the DoorID
                For Each vItem In Me.lstDoors.ItemsSelected
                    lngDoorID = Me.lstDoors.ItemData(vItem)
                Next


                'Query with a subquery to select Levels that
                'are related to only this Door and no others
                strSQL = "SELECT tblDoorLevels.LevelID " _
                       & "FROM tblDoorLevels LEFT JOIN " _
                       & "(SELECT tblDoorLevels.LevelID " _
                       & "FROM tblDoorLevels " _
                       & "WHERE tblDoorLevels.DoorID <> " & lngDoorID & ") AS T " _
                       & "ON tblDoorLevels.LevelID = T.LevelID " _
                       & "WHERE T.LevelID Is Null;"




            Case Is > 1 'Multiple Doors selected
            
                'Build a comma separated string of DoorIDs
                For Each vItem In Me.lstDoors.ItemsSelected
                    strDoors = strDoors & Me.lstDoors.ItemData(vItem) & ", "
                Next
                
                'Remove the trailing comma & space
                strDoors = Left(strDoors, Len(strDoors) - 2)
                
                'Query with a subquery to select Levels where the Count of records
                'equals the number of Doors selected in the list box
                strSQL = "SELECT T.LevelID FROM " _
                       & "(SELECT tblLevels.LevelID FROM tblLevels " _
                       & "INNER JOIN tblDoorLevels ON tblLevels.LevelID = tblDoorLevels.LevelID " _
                       & "WHERE tblDoorLevels.DoorID In (" & strDoors & ")) AS T " _
                       & "GROUP BY T.LevelID " _
                       & "HAVING Count(T.LevelID)=" & intCount
        End Select
        
        'See if the query returns anything
        With CurrentDb.OpenRecordset(strSQL)
            If .RecordCount = 0 Then 'No records found
            
                MsgBox "There are no Levels that match the Doors selected."
                
            Else 'Records found
            
               'Place the results of the query in a filter
                strFilter = "LevelID In(" & strSQL & ")"
                
                'Apply the filter to the form
                Me.Filter = strFilter
                Me.FilterOn = True
                
            End If
        End With
    End If
    
End Sub

So if you select a single Door it will return Levels that are associated with only that Door (if there are any). If you select multiple Doors it will only return Levels that are associated with all Doors selected.

HTH

[IE_STU]

Yes, that works for the single door scenario!

Now, say if I choose multiple doors from the list. I only want the level(s) shown that correspond to those exact doors chosen.

For example,

LevelID DoorID
1 1
1 2
1 3
2 1
2 2
3 1

I choose doors 1 and 2, I only want the query search to return level 2..

[Beetle]

OK, then we can just sort of combine the logic of the two previous examples. In this case it is no longer necessary to differentiate between a single list box selection and a multiple selection. The Select Case statement can be eliminated and we can just build a query based on any number of selections in the list box. Here is the modified code;

Private Sub cmdSearch_Click()

    Dim intCount As Integer
    Dim strDoors As String
    Dim strFilter As String
    Dim strSQL As String
    Dim vItem As Variant
    
    'Determine how many Doors were selected
    intCount = Me.lstDoors.ItemsSelected.Count
    
    If intCount = 0 Then 'No Doors selected
        MsgBox "Please select at least one Door."
    Else
        'Build a comma separated string of DoorIDs
        For Each vItem In Me.lstDoors.ItemsSelected
            strDoors = strDoors & Me.lstDoors.ItemData(vItem) & ", "
        Next
                
        'Remove the trailing comma & space
        strDoors = Left(strDoors, Len(strDoors) - 2)
        
        strSQL = "SELECT tblDoorLevels.LevelID " _
               & "FROM tblDoorLevels LEFT JOIN " _
               & "(SELECT tblDoorLevels.LevelID " _
               & "FROM tblDoorLevels " _
               & "WHERE tblDoorLevels.DoorID Not In (" & strDoors & ")) AS T " _
               & "ON tblDoorLevels.LevelID = T.LevelID " _
               & "WHERE T.LevelID Is Null " _
               & "GROUP BY tblDoorLevels.LevelID " _
               & "HAVING Count(tblDoorLevels.LevelID)=" & intCount

        'See if the query returns anything
        With CurrentDb.OpenRecordset(strSQL)
            If .RecordCount = 0 Then 'No records found

                MsgBox "There are no Levels that match the Doors selected." 

            Else 'Records found
            
               'Place the results of the query in a filter
                strFilter = "LevelID In(" & strSQL & ")"
                
                'Apply the filter to the form
                Me.Filter = strFilter
                Me.FilterOn = True
                
            End If
        End With
    End If
    
End Sub

I hope this works, I'm starting to get a cramp in sql lobe of my abby normal brain.

[IE_STU]

Perfect!!

Thanks for all your help, this surely helped me with my VBA coding.