Not returning all data in a query 1

[TashiraRonin]

I have a query regarding getting only partial data in a query


SELECT [Application Title and Key].[Application Assigned Number], IIf(IsNull([Suspension total days per assessment].[No of days assessment suspended]),0,[Suspension total days per assessment].[No of days assessment suspended]) AS [No of days assessment suspended], [Application Title and Key].[Application Eligibility Date], [Application Title and Key].[Endorsed by Minister], [Endorsed by Minister]-[Application Eligibility Date] AS [No of overall days], [No of overall days]-[No of days assessment suspended] AS [No of Days], Round([No of days]/7) AS [Length in weeks], [Application Title and Key].[Assessment Type], [Application Title and Key].Evaluator, [Application Title and Key].[Applicant Type]
FROM [Suspension total days per assessment] INNER JOIN [Application Title and Key] ON [Suspension total days per assessment].[Application Assigned Number]=[Application Title and Key].ID;

This returns the calculated length of time from the beginning to the end of an assessment minus the length of time where the assessment was suspended. ( which is pulled another query

However it only displays in the report those assessment where there was a set suspension dates.

How do I modify the query so that it shows all assessments including those for whom it has to do the subtraction calculation for suspensions?

 

[PHV]

Use an OUTER join (probably a RIGHT with your actual FROM clause).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886