Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now