Missing Records in Query

[MooseMan]

I have two tables tblDefault and tblDefaultDetails. I have one and only one record for each producer in tblDefault that records the original Default amount at the end of each crop year.

In tblDefaultDetails I have a monthly report for each administrator recording the amount repaid from the original default amount.

There is a one to many relationship between tblDefault and tblDefaultDetails.

My problem is that when I run a query which calculates the amount outstanding I get incorrect data when there is an original default amount in tblDefault and no monthly report in tblDefaultDetails.

I have tried Nz and other possibilities with no luck. This is serious inthat it happens often that there will be a record of original default values without a monthly record in the DefaultDetails table.

I have included my code for anyone that might be able to help.

SELECT tblDefaultDetails.AdministratorID, tblDefaultDetails.CropYear, tblDefaults.TotalDefaultCY, Sum(tblDefaultDetails.TotalRepaidPostCY) AS SumOfTotalRepaidPostCY, Sum(tblDefaultDetails.PrincipalAmountReceivedFromAAFC) AS SumOfPrincipalAmountReceivedFromAAFC, [TotalDefaultCY]-(Nz([SumOfTotalRepaidPostCY])+Nz([SumOfPrincipalAmountReceivedFromAAFC])) AS Expr1
FROM tblDefaults INNER JOIN tblDefaultDetails ON tblDefaults.DefaultID = tblDefaultDetails.DefaultID
GROUP BY tblDefaultDetails.AdministratorID, tblDefaultDetails.CropYear, tblDefaults.TotalDefaultCY;

Thanks in advance for your help

 

[r937]

you get incorrect data? probably you get missing data, since you're using an inner join, and that will only return tblDefault rows with matching tblDefaultDetails rows

use a left outer join instead, and make sure you substitute zero for null in every column from tblDefaultDetails

rudy

 

[MooseMan]

Your right Rudy I get missing Data.

this is all fairly new to me could you be a little more specific as to how I create a left outer join and substitute o's.

I really appreciate your help

Bruce

 

[r937]

[tt]select DD.AdministratorID
, DD.CropYear
, D.TotalDefaultCY
, sum( iif(isnull(DD.TotalRepaidPostCY)
,0,DD.TotalRepaidPostCY)
) as sumOfTotalRepaidPostCY
, sum( iif(isnull(DD.PrincipalAmountReceivedFromAAFC)
,0,DD.PrincipalAmountReceivedFromAAFC)
) as sumOfPrincipalAmountReceivedFromAAFC
, D.TotalDefaultCY
- sum( iif(isnull(DD.TotalRepaidPostCY)
,0,DD.TotalRepaidPostCY)
)
- sum( iif(isnull(DD.PrincipalAmountReceivedFromAAFC)
,0,DD.PrincipalAmountReceivedFromAAFC)
)
as Expr1
from tblDefaults D
inner
join tblDefaultDetails DD
on D.DefaultID = DD.DefaultID
group
by DD.AdministratorID
, DD.CropYear
, D.TotalDefaultCY[/tt]