Solved

MS Access 2007 Quering Multivalue field in a linked Table wriiten in IBM Universe

Posted on 2009-04-04
1
581 Views
Last Modified: 2012-05-06
I have linked a Product Master Table written in IBM/Universe using Universe/ODBC. I am able to perform lots of queries and forms in MS ACCESS . The linked table has a field called UM (Units of Measure) and the UM field has mutivalue fields stored as shown below.
 
CS    1
EA    16
 
The first and second lines are not consistently in the same order, because of selling practice. MS Access when queried for the field containing multivalue looks only for the first line. However in the native Universe database, retrieve (aka query) shows the results like this
PMID    UM    F64
40021    CS    1    
            EA    16
As you observe PMID does not get repeated for EA.
 
I am looking to see the query results like this
PMID    UM    F64
40021    CS    1
40021    EA    16
40023    EA    9    
40023    CS    1
 
Any help is very much appreciated.  
0
Comment
Question by:harrywils
[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
1 Comment
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 24068119
The treatment of multi-valued column types is defined by the database driver. Maybe there is an option setting that will result in common data being repeated over all values.  In doing some research about your question I found indications that the driver puts CR+LF between values, which will have all sorts of odd behaviors in Access.

You have shown an example of "the native Universe database, retrieve (aka query) shows the results like this". What does a query result look like in Access? Is it possible that CR+LF characters appear between values in a multi-value column? If that is the case then there is a pretty straightforward solution.

Here is what appears to be an excellent resource for understanding how IBM Universe works with an ADO.NET driver. It is intended for .NET programmers but the concepts are universal to all driver-based access to the data.

http://gigamegatech.com/category/programming/ibm-universe-u2/

"7) Last, but not least, multivalues.  As described in my previous article there are a few different ways to configure your dictionary to return multivalues, but the method that IBM recommends is to list the multivalued fields in a special "association" dictionary entry."
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

626 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