SharePoint MultiValue Field Data Extraction and UNION Query

  • Thread starter Thread starter Mike Hill
  • Start date Start date
M

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.
 
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.

Jeff Boyce said:
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/

Mike Hill said:
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.
Click to expand...
Click to expand...
 
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/
 
Back
Top