Linking 3 SQL Tables

[KingPhoenix]

Running CR XI - SQL 2000

Im linking 3 tables together... QM / QP / QR I need to get the furthest most quantity value.

So im looking at QM (the master record set) to get a list of job numbers (displayed on report) im then running an if statement to check the QR table, and if the value is above 0 to return its value, else to check the QP table, and if the value in QP is 0 then display the quantity on the QM table.

The problem is that the same job numbers appear in both QM and QP without fail, but QR only some of the job numbers appear. I have outer linked the database to show me the qty. in the fields, but if i put these into a formula, if the job number doesnt appear in table QR it just delivers a blank.... it doesnt check the QP or QM tables for the qty.

Any suggestions???

 

[GJParker]

It would help if you displayed the formula you are using, but I think you need to amend your formula tro cope with null values returned form your outer join i.e.

If {QP.Value} > 0 Then
{QP.Value}
Else If Not IsNull({QR.Value}) and {QR.Value}) > 0 Then
{QR.Value}
Else
{QM.Value}

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[KingPhoenix]

Gary your a star!!!

My formula now looks like

if isnull({QR.QR_QUANTITY}) then
if {QP.QP_JOB_QTY} > 0 then {QP.QP_JOB_QTY} else
{QM.QM_JOB_QTY} else
{QR.QR_QUANTITY}

Seems to work perfectly