Applying an Access Query to SharePoint Data
Stoneridge Software uses Implementation Project SharePoint sites to organize information and drive our client’s implementations. One of the pages on the site holds a Requirements list. SharePoint provides a way to filter each column on the list. One of our project managers asked for a way to filter across multiple columns at the same time, looking for the occurrence of a word or phrase. This can be done by applying an access query to SharePoint data.
The tool we will use is the Open with Access button on the Requirements page:
After clicking the Open with Access button, we see:
Click OK. Access will open in your bottom tray. Maximize Access to see:
After clicking on Query Design,
Double click the columns you want to see and/or filter by:
Now to the heart of the matter: we want to see if one or more of several columns contain our search text: we want to enter the search text once per run. The syntax we will use is: Like "*" & [Search text] & "*" in the Criteria and Or cells.
Here is the SQL this produced:
SELECT Requirements.ID, Requirements.Requirement, Requirements. Process, Requirements. Priority, Requirements. Status, Requirements.Detail
FROM Requirements
WHERE (((Requirements.Requirement) Like "*" & [Search text] & "*")) OR (((Requirements.Process) Like "*" & [Search text] & "*")) OR (((Requirements.Detail) Like "*" & [Search text] & "*"));
Fill in the parameter after you click Run:
Here are the results: 49 rows out of 388 in the Requirements table:
When you are done with the query, you can close access and save the query:
Open Access to find the database and query when you need it again:
