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.
