Solved

Access 2013 qry from a SharePoint 2013 list

Posted on 2014-07-31
4
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access vba Print  PDF First page only 16 34
Batch convert csv to xlsx 10 60
Problem to macro 5 22
Day Count issue - Days360? 12 28
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

738 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