Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Access 2013 qry from a SharePoint 2013 list

I have a list in SharePoint 2013 that has a choice field that allows multiple selections. The users click checkboxes to make their selections. Some check one box, others up to ten checkboxes. In the column in the SharePoint list called "Type", the selections are divided by a comma if there is more than one selection. As an example: Choice1, Choice2, Choice3.

I can't create a View of the SharePoint list where it is grouped by Type which is a choice field. I need to create a query in Access that can show each of the Types a company is and then group the data by type. So a company may show up in several Type groups.
0
JT8337
Asked:
JT8337
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Given the denormalized nature of your data (i.e. storing multiple data items in a single column), you'll have to jump through some serious hoops to do stuff like this. You'd be much, much better off storing your data in a normalized fashion (and NOT on Sharepoint, which is a content management system).

That said, you can often use the IN keyword to determine if a value exists in a string value. For example:

SELECT * FROM SomeTable WHERE YourField IN ('a', 'b', 'c')

If you have 10 choices, then you could build up a UNION query:

SELECT SomeTable.*, 'Type A' AS CompanyType FROM SomeTable WHERE YOurField LIKE '*a*'
UNION
SELECT SomeTable.*, 'Type B' AS CompanyType FROM SomeTable WHERE YourField LIKE '*b*'
etc etc

You'd then have a listing that would show the data in SomeTable, with a new "column" for each "type". You could then use that query to create another query, and do your Grouping in the second query.
0
 
JT8337Author Commented:
Thanks Scott. I will try that tonight.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now