The WHERE clause is limiting the results of your query based on table A. It
therefore does not return the records where the A fields are null.
Try:
WHERE (a.source_type = 'LY01') OR (a.source_type Is Null)
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"k k" <k_rallabandi@hotmail.com> wrote in message
news:40b6e03e$0$202$75868355@news.frii.net...[color=blue]
>
> The following query has failed to return all the records.
>
> SELECT b.Account_desc, b.Account,
> IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
> a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
> IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
> outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
> where a.source_type = 'LY01'
>
> There are total 143 records in Actual_account_Tbl. But the above query
> returned only 135 records i.e., only those records satisfy the condition
> "a.Account_desc = b.Account_desc" are returned.
>
> As per right outerjoin in the above statement I suppose to get all the
> records from table 'b', and blank data from table 'a' if it doesn't
> satisfy the condition.
>
> Why it is not consistant?[/color]
