Filter Id that contain specific values in columns in MS Access

user_12029940 0
Viewed 103

I am trying to filter rows from a table in MS Access using Query but with no success. I have below table below, I would like to generate a query based on the values "A1" and "A2" in any of the columns KH1 to KH6 columns. The results should look like below. I have used or condition in query but it is generating blank table in MS Access. Any help is much appreciated.

Table:

ID  KH1 KH2 KH3 KH4 KH5 KH6

001 A2  B1  C1  D1  E1  F1
002 F1  A1  B1  D1  E1  G1
003 A1  B1  D1  E1  
004 XX  XX  XX  XX  XX  XX
005 AA  AB  AC  A2  XX
006 XX  XX  XX  A1  
007 XX  XX  XX  XX  A1  XX
008 XX  XX  AE  XX  XX  AC

Result:

ID  KH1 KH2 KH3 KH4 KH5 KH6

001 A2  B1  C1  D1  E1  F1
002 F1  A1  B1  D1  E1  G1
003 A1  B1  D1  E1  
005 AA  AB  AC  A2  XX
006 XX  XX  XX  A1  
007 XX  XX  XX  XX  A1  XX
sql-in ms-access where-clause vba
2 Answers

You need a WHERE clause that checks each of the 6 columns:

SELECT *
FROM tablename
WHERE KH1 IN ('A1', 'A2') 
   OR KH2 IN ('A1', 'A2')
   OR KH3 IN ('A1', 'A2')
   OR KH4 IN ('A1', 'A2')
   OR KH5 IN ('A1', 'A2')
   OR KH6 IN ('A1', 'A2');

This does not scale well if there are many columns to be checked.

It would be easier if you redesigned the table so that each row contains only 1 of the KH? columns.
Something like this:

ID   nr KH
001  1  A2
001  2  B1
001  3  C1
001  4  D1
001  5  E1
001  6  F1
user_104988280

You could use DAO for this:

Public Function SelectRecords()

    Dim Records     As DAO.Recordset
    Dim Field       As DAO.Field
    
    Set Records = CurrentDb.OpenRecordset("Select * From YourTable")
    
    While Not Records.EOF
        For Each Field In Records.Fields
            Select Case Field.Value
                Case "A1", "A2"
                    Exit For
            End Select
        Next
        If Not Field Is Nothing Then

            ' Output record.
            Debug.Print Records.Fields(0).Value, Field.Name, Field.Value

            Set Field = Nothing
        End If
        Records.MoveNext
    Wend
    Records.Close
    
End Function
user_35272970
Related