losing entry from linked tables? 1

[mgason]

To be honest I had no idea what to name this post.
Here is the problem.
Tables....

Ingredient table
Ing column
powderA
powderB
powderC
oilB
Cut-bag#1 (does not display)

RawMaterial table
Code Description
powderA mylar elastomar
powderB black nerosene
powderC serio resin
oilB exon 045

I have 2 tables Ingredient and Raw_material in my database. (see above)

Ingredient.Ing which is a string matches RawMaterial.Code

RawMaterial also has a field "Description".

Ing is linked to Code in my report.

I display the Ingredients.Ing field, next to that I display the RawMaterial.Desription field.

The displaying of descriptions works fine but for one problem.
The problem is that Ingredient.Ing has more values than RawMaterial.Code

If there is no matching RawMaterial.Code for an Ingredient.Ing the Ingredient.Ing does not display.
I need all the Ingredient.Ing values to display with a blank for description where a matching RawMaterial.Code does not exist.

How can I achieve this? What is the best way?
Should I be using a SQL expression.
Database is Micrososft SQL server 2005

one extra fact, the extra entries in the Ingredients.Ing field do in fact all include the string "bag#" somewhere.

thanks mark

 

[lbass]

You should use a left join FROM the ingredients table TO the raw material table, with no selection criteria on the raw material fields.

-LB

 

[mgason]

How do you do that?

 

[lbass]

Go into the database expert->linking screen->select the link until it turns blue->right click->link options->left outer join.

-LB

 

[mgason]

Thanks,
that does the trick. I did not know that dialog existed!!
Now I need to go and learn the difference between Inner, Left Outer and Right Outer etc.

thanks again
mark