Solved

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

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

809 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