Solved

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

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

746 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

12 Experts available now in Live!

Get 1:1 Help Now