Solved

Access 2013 qry from a SharePoint 2013 list

Posted on 2014-07-31
4
21 Views
Last Modified: 2016-05-24
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
Comment
Question by:JT8337
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40233819
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
 

Author Comment

by:JT8337
ID: 40235590
Thanks Scott. I will try that tonight.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Outlook Free & Paid Tools
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now