Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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
Document1Criteria1
Document1Criteria2
Document1Criteria3
Document1Criteria4
Document2Criteria16
Document2Criteria13


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.

1 Reply
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.