Sage MAS 90 and 200 Extended Enterprise Suite

[timt]

Greetings,

I am attempting to pull, from MAS, all items and their descriptions into either access or excel. In Microsoft  Query I pull in my two tables from MAS CI_item and CI_Extended Description. MS Query will draw the relation between the two tables, when I bring the fields that I want into view it will only show items that have a and extended description key. The problem is is that I have some items that don' t have an extended description key. I want to see all items that have both a short description and and extended description in one query.

I know that this can be done by breaking the relationship and having two queries and then sort each query respectively in the end however, I really need them in one query.

Tim

[docterry · Accepted solution]

The tables are linked by item code.  Every item has a record in the CI_Item but only some items have a record in CI_ExtendedDescription.  It sounds like you query is set up with an inner join but you need an outer join, like ci_item left outer join ci_extendeddescription on ci_item.itemcode = ci_extendeddescription.itemcode.  The goal is to return all the rows from ci_item along with any matching rows from itemdescription.

[robertcwood]

+1 What docterry said. You'll need to change the relationship type to a left outer join.

[timt]

Thanks so much.