SharePoint MultiValue Field Data Extraction and UNION Query

[Mike Hill]

I am trying to extract data from a SharePoint list that are of type
multivalue. I am able to extract the data correctly from a single column
using [Column].Value in my select statement. However, I have 32 multi-value
columns in the table that need to each be converted into a single results
column, I am trying to normalize the multivalue tables into a more usable
structure. I have created a UNION query that works with two of the columns
but as soon as I add a third column I receive an error message, "the number
of columns in the two selected tables or queries of a union query do not
match." My example would look like this:

SharePoint List A:
SharePointMVField1
SharePointMVField2
SharePointMVField3

Access SQL:
SELECT SharePointMVField1.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField1.value IS NOT NULL
UNION
SELECT SharePointMVField2.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField2.value IS NOT NULL
UNION
SELECT SharePointMVField3.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField3.value IS NOT NULL;

I am able to create the query with a single UNION statement but no more than
that. I can create two queries each with as single UNION statement and with
the third query to UNION the results of the first two queries no issues. I
have even tried using the following with no results:

SELECT IIF(IsNull(SharePointMVField3.value),"",SharePointMVField3.value) AS
MyColumnName

This seems like a limitation of the built-in multivalue capability of Access
2007 but wanted to see if anyone had any other thoughts. I guess that I
could write VBA but was hoping for an easier out.

 

[Mike Hill]

The multivalue field is a SharePoint implementation that I am trying to
extract data from. This is not a field I have added myself in Access so I do
not have the ability to create a proper intersection table solution. In my
scenario I have source data input in SharePoint and I am using Access to
extract the data and pump it into an external MS SQL DB. I have two options
that I see right now:

1) Create subqueries made up of two sql statements combined by a UNION.
Then combine all of the subqueries into a single UNION statement. Tested
this and it works but seems a little clunky having 16 queries to feed a
single query. The speed is actually pretty fast.

2) Write VBA code to extract the data and populate it into the I also
built this based on the following
http://msdn.microsoft.com/en-us/library/bb258183.aspx. This works but is
substantially slower than the UNION queries. Right now I have minimal data
in the system and I fear this will be an issue going forward. I could
probably tweak the code to make it more efficient.

My rough estimate is the VBA solution takes 2-3minutes to execute where the
UNION Query takes <5 seconds.

Mike

One concern about using the multivalue field type is that the actual storage
of the data is essentially not accessible.

Is there a chance you could create and use a "junction/resolver/relation"
table you explicitly define? That way, you control what goes in/out, and
can modify the table structure as needed.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

I am trying to extract data from a SharePoint list that are of type
multivalue. I am able to extract the data correctly from a single column
using [Column].Value in my select statement. However, I have 32 multi-value
columns in the table that need to each be converted into a single results
column, I am trying to normalize the multivalue tables into a more usable
structure. I have created a UNION query that works with two of the columns
but as soon as I add a third column I receive an error message, "the number
of columns in the two selected tables or queries of a union query do not
match." My example would look like this:

SharePoint List A:
SharePointMVField1
SharePointMVField2
SharePointMVField3

Access SQL:
SELECT SharePointMVField1.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField1.value IS NOT NULL
UNION
SELECT SharePointMVField2.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField2.value IS NOT NULL
UNION
SELECT SharePointMVField3.value AS MyColumnName
FROM [SharePoint List A]
WHERE SharePointMVField3.value IS NOT NULL;

I am able to create the query with a single UNION statement but no more than
that. I can create two queries each with as single UNION statement and with
the third query to UNION the results of the first two queries no issues. I
have even tried using the following with no results:

SELECT IIF(IsNull(SharePointMVField3.value),"",SharePointMVField3.value) AS
MyColumnName

This seems like a limitation of the built-in multivalue capability of Access
2007 but wanted to see if anyone had any other thoughts. I guess that I
could write VBA but was hoping for an easier out.

 

[Jeff Boyce]

Mike

One concern about using the multivalue field type is that the actual storage
of the data is essentially not accessible.

Is there a chance you could create and use a "junction/resolver/relation"
table you explicitly define? That way, you control what goes in/out, and
can modify the table structure as needed.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/