Filter Id that contain specific values in columns in MS Access

Question

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

score 1 · Accepted Answer · answered Sep 18 '21 at 12:28

1

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:

answered Sep 18 '21 at 12:28

forpas

160,666
10
38
76

Hi, Thank you for the advice. I am new to MS Access and tried to transpose in SAS it works for me, but how could I transpose the table in MS Access. Do I have to use union? but I have more than 100 columns. – KApril Sep 18 '21 at 12:50
@KCApril yes you can use UNION ALL. Check this: https://stackoverflow.com/questions/18536639/how-to-unpivot-a-crosstab-like-table – forpas Sep 18 '21 at 12:56
Hi, one last question, can I use "not in" operator to select the values other way round? example, ```where KH1 NOT IN ('A3', 'B1', 'B2',,.. so on ``` – KApril Sep 18 '21 at 15:09
1

@KCApril if you want the rows where none of the column contain A1 and A2: `WHERE Nz(KH1, '') NOT IN ('A1', 'A2') AND Nz(KH2, '') NOT IN ('A1', 'A2') AND ...` – forpas Sep 18 '21 at 15:15
Wow that's amazing, Access SQL is little bit different from MS SQL. Thanks a ton! – KApril Sep 18 '21 at 15:47
Hi, May I please ask another question? If I want to use case when in MS Access where none of the column contain A1 and A2, could you please let me know how. I tried ```IIF``` but I am not successful executing the query. – KApril Sep 18 '21 at 21:47
@KCApril Access does not support CASE expressions. Instead of `CASE WHEN expr THEN truepart ELSE falsepart END` you use `IIF(expr, truepart , falsepart)`: https://support.microsoft.com/en-us/office/iif-function-32436ecf-c629-48a3-9900-647539c764e3 – forpas Sep 19 '21 at 08:20

score 1 · Answer 2 · answered Sep 18 '21 at 14:04

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

Filter Id that contain specific values in columns in MS Access

2 Answers2