Solved

Access 2013 qry from a SharePoint 2013 list

Posted on 2014-07-31
4
37 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

829 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