Solved

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

Posted on 2009-04-04
1
579 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

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