Query MS Access Database Table with Multiple Column Values Represented by Multiple Rows

[Destiny_Kelley]

I have created a MS Access database for filtering documents that meet certain criteria. Most documents have multiple criteria, so I added multiple rows for the same document for each different criteria value. I also have another column for the logic.

The table looks similar in structure to this:

Name              Criteria

Document1	Criteria1
Document1	Criteria2
Document1	Criteria3
Document1	Criteria4
Document2	Criteria16
Document2	Criteria13

There is another column for the logic:

Name             Logic

Document1	[Criteria] = 'Criteria1' OR [Criteria] = 'Criteria2' AND [Criteria] = 'Criteria3' OR [Criteria] = 'Criteria4'
Document2	[Criteria] = 'Criteria16' OR [Criteria] = 'Criteria13'

I have created an Access form for users to select criteria that apply to their projects and then receive the documents that meet their criteria in an Access report. I have grouped the criteria into a list box allowing for multiple selections by users. I found a VBA solution that seems to work but I would like to have a SQL solution that works as well to test the VBA version.

For instance, a user might select the following from the list box:

Criteria1, Criteria10, Criteria20

I want to compare the user's selection to the logic conditions in the table.

I have come up with a query that selects all documents that have at least one of the conditions met:

SELECT Name, Criteria, Logic
FROM 
  (SELECT Name, Criteria, Logic FROM Table) AS A
INNER JOIN 
  (SELECT Name FROM Table WHERE Criteria IN ('Criteria1', 'Criteria10', 'Criteria20') AS B
ON A.Name = B.Name

The results of the above query include criteria that are present for the document but not in the selections made by the user so that the full logic statement can be tested for the document.

From this query, I need to test the full logic statements using SQL. I am a beginner in SQL and any help or insights you can offer would be greatly appreciated.

I looked into PIVOT tables but I do not know how to make it work.

[#2]

Be careful: Document1.Logic has a mix of AND and OR. According to the precedence rules, AND is evaluated before OR. When in doubt, use parentheses.

The general pattern is:
select * from myDocuments
where Criteria = Forms!myCriteriaForm!myCriteriaControlName1
or Criteria = Forms!myCriteriaForm!myCriteriaControlName2

I don't know how to do this in pure SQL. It would probably be very contorted, if at all possible, and that would defeat the purpose of a simple way to check that the VBA logic is correct.
Maybe better to setup certain test scenarios so you can convince yourself of the correctness.