Left Join Query Problem

[Pat Coleman]

Here is my query :

SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-PartsOnOpenSO.Allocated) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;


We are tring to determine Quantity 'Available' which is
(PartQOH.SumOfQTY-PartsOnOpenSO.Allocated)

The query runs OK and returns a correct values for [ Available ] as long as
there is a value in the field [PartsOnOpenSO.Allocated]

If the field [PartsOnOpenSO.Allocated] is blank, the there is no value
returned for the [Available] field, even though the [PartQOH.SumOfQTY] has a
value.

The query looks like this

PARTID SumOfQTY Allocated Available
35 304 4 300
25 150 50 100
26 200
32 68
56 12 3 9


So if there are no parts [Allocated] , I need [Available] to read the value
in the [SumOfQTY] field.

Any Ideas ???

 

[KARL DEWEY]

Try this ---
SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-IIF(PartsOnOpenSO.Allocated IS NULL, 0,
PartsOnOpenSO.Allocated)) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;

Or this --
SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-Nz(PartsOnOpenSO.Allocated,0)) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;

 

[Pat Coleman]

Thank you very much - both work perfect.

Try this ---
SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-IIF(PartsOnOpenSO.Allocated IS NULL, 0,
PartsOnOpenSO.Allocated)) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;

Or this --
SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-Nz(PartsOnOpenSO.Allocated,0)) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;


--
KARL DEWEY
Build a little - Test a little

Here is my query :

SELECT PartQOH.PARTID, PartQOH.SumOfQTY, PartsOnOpenSO.Allocated,
(PartQOH.SumOfQTY-PartsOnOpenSO.Allocated) AS Available
FROM PartQOH LEFT JOIN PartsOnOpenSO ON PartQOH.PARTID =
PartsOnOpenSO.PARTID;


We are tring to determine Quantity 'Available' which is
(PartQOH.SumOfQTY-PartsOnOpenSO.Allocated)

The query runs OK and returns a correct values for [ Available ] as long
as
there is a value in the field [PartsOnOpenSO.Allocated]

If the field [PartsOnOpenSO.Allocated] is blank, the there is no value
returned for the [Available] field, even though the [PartQOH.SumOfQTY]
has a
value.

The query looks like this

PARTID SumOfQTY Allocated Available
35 304 4 300
25 150 50 100
26 200
32 68
56 12 3 9


So if there are no parts [Allocated] , I need [Available] to read the
value
in the [SumOfQTY] field.

Any Ideas ???