Nz(null values in queries, 0)

[Guest]

I am trying to use the NZ function to return null values from my query. The
query is off a table called "Training Course" and another table related
(through training course pk [Course ID] ) called "Extra Equipment".

It simply uses an expression to sum a few values in the "Extra Equipment"
table that correspond to the PK from Training Course. What i need it to do is
return the value '0' alongside the Course ID when there is no corresponding
data in the Extra Equipment table. At the moment even with the code below it
only returns the [Course ID]'s and corresponding values of that data which in
"Extra Equipment"

ExtraEquipSumCost: Sum(Nz([Cost],0)

Current SQL:
SELECT DISTINCTROW [Training Course].[Course ID], Nz(Sum(Nz([Cost],0)),0) AS
ExtraEquipSumCost
FROM [Training Course] INNER JOIN [Extra Equipment] ON [Training
Course].[Course ID] = [Extra Equipment].[Course ID]
GROUP BY [Training Course].[Course ID];

 

[John Spencer]

You need to change the join from an INNER JOIN to an (OUTER) LEFT JOIN.

SELECT DISTINCTROW [Training Course].[Course ID]
, Nz(Sum(Nz([Cost],0)),0) AS ExtraEquipSumCost
FROM [Training Course] LEFT JOIN [Extra Equipment] ON [Training
Course].[Course ID] = [Extra Equipment].[Course ID]
GROUP BY [Training Course].[Course ID];

Inner Join means that there MUST be records in BOTH tables for a row
(record) to be returned

LEFT Join means that there must be a record in the LEFT side of the join and
if there is no record on the right side then one record will be returned
with null for all the values in the right side table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John i've never got my head around SQL...

I didn't need to include OUTER in it did i that is the default join type..
yes?

You need to change the join from an INNER JOIN to an (OUTER) LEFT JOIN.

SELECT DISTINCTROW [Training Course].[Course ID]
, Nz(Sum(Nz([Cost],0)),0) AS ExtraEquipSumCost
FROM [Training Course] LEFT JOIN [Extra Equipment] ON [Training
Course].[Course ID] = [Extra Equipment].[Course ID]
GROUP BY [Training Course].[Course ID];

Inner Join means that there MUST be records in BOTH tables for a row
(record) to be returned

LEFT Join means that there must be a record in the LEFT side of the join and
if there is no record on the right side then one record will be returned
with null for all the values in the right side table.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am trying to use the NZ function to return null values from my query. The
query is off a table called "Training Course" and another table related
(through training course pk [Course ID] ) called "Extra Equipment".

It simply uses an expression to sum a few values in the "Extra Equipment"
table that correspond to the PK from Training Course. What i need it to do
is
return the value '0' alongside the Course ID when there is no
corresponding
data in the Extra Equipment table. At the moment even with the code below
it
only returns the [Course ID]'s and corresponding values of that data which
in
"Extra Equipment"

ExtraEquipSumCost: Sum(Nz([Cost],0)

Current SQL:
SELECT DISTINCTROW [Training Course].[Course ID], Nz(Sum(Nz([Cost],0)),0)
AS
ExtraEquipSumCost
FROM [Training Course] INNER JOIN [Extra Equipment] ON [Training
Course].[Course ID] = [Extra Equipment].[Course ID]
GROUP BY [Training Course].[Course ID];