Union Query Error: Multi-valued Field

[Knicks]

Ms Access 2007-2010 format

I have imported queries and tables from another database. The query in question works in the original database but when I import it into a new database file the Union query does not work. I have tested the underlying nest queries and they do work. It appears that the only problem is in the union query.

I get the message "Multi-valued field [issue responsibility internal] can't be used in a Union Query". The original tables this field represents are in Sharepoint linked and the initial nested queries work fine. Here is the code, any suggestions would help.....its possible its just some quirk or import problem & corruption. The initial creator of these queries seemed to use a whole bunch of reserved characters and other non-conforming standards, but it does work in the original 2007-2010 database.

SELECT
[Issue ID], [Category], [MSC Code], [Style],[Style Name],[Season_Year],[Splr Loc Code],[PO #],[Fty primary LO cd],[Defect Name],[ISSUE - VALIDATION],[Product Cost],[Shipping Cost],[Labor Cost],[Material Cost],[Number of Units B-Grade],[Number of Units C-Grade],[Number of Units Rework], [B GRADE Cost], [C GRADE Cost],[Location],[Action Taken],[Apparel or Equipment],[Issue],[Issue Type],[Issue Responsibility Internal],[Issue Responsibility External],[Specify Other],[Accountability % Internal],[Accountability % External],[Total Cost],[EURO TO USD],[Currency],[Product Safety Issue],Format([Issue Created],"Short Date")As [Issue_Created]
FROM [CSC Combined Query]
UNION SELECT
[Issue ID], [Category],[MSC Code],[Style],[Style Name],[Season_Year],[Splr Loc Code],[PO #],[Fty primary LO cd],[Defect Name],[ISSUE - VALIDATION],[Product Cost],[Shipping Cost],[Labor Cost],[Material Cost],[Number of Units B-Grade],[Number of Units C-Grade],[Number of Units Rework], [B GRADE Cost], [C GRADE Cost],[Location],[Action Taken],[Apparel or Equipment],[Issue],[Issue Type],[Issue Responsibility Internal],[Issue Responsibility External],[Specify Other],[Accountability % Internal],[Accountability % External],[Total Cost],[EURO TO USD],[Currency],[Product Safety Issue],Format([Issue Created],"Short Date")As [Issue_Created]
FROM [NON CSC Combined Query]
UNION SELECT
[Issue ID], [Category],[MSC Code],[Style],[Style Name],[Season_Year],[Splr Loc Code],[PO #],[Fty primary LO cd],[Defect Name],[ISSUE - VALIDATION],[Product Cost],[Shipping Cost],[Labor Cost],[Material Cost],[Number of Units B-Grade],[Number of Units C-Grade],[Number of Units Rework], [B GRADE Cost], [C GRADE Cost],[Location],[Action Taken],[Apparel or Equipment],[Issue],[Issue Type],[Issue Responsibility Internal],[Issue Responsibility External],[Specify Other],[Accountability % Internal],[Accountability % External],[Total Cost],[EURO TO USD],[Currency],[Product Safety Issue],Format([Issue Created],"Short Date")As [Issue_Created]
FROM [Customer Combined Query];

 

[lameid]

A quick search on querying multi-valued fields...

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx

I've taken the conventional wisdom of Access is a database and don't touch multi-valued fields with a 10' pole but apparently that is what you have gotten from sharepoint. You just have to deal with it. You might check the underlying tables to figure out if on or all of the tables have the same multivalued fields, and of course datatypes.