OUTER and INNER JOIN Issue

[ria.arora]

Hi,

My query is not returning correct result of outer join I tried to write query in different way but no success.

Please find below my query:

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]SELECT ProdCat.ProductName, Sum(tbl_Consol_Weekly_Product.RevenueInUSD) AS Revenue FROM tbl_Consol_Weekly_Product, tbl_Bankers_Mapping, 
(SELECT tbl_Product_Categories.ProductName FROM tbl_Consol_Weekly_Product RIGHT JOIN tbl_Product_Categories ON tbl_Consol_Weekly_Product.[Report Type] = tbl_Product_Categories.ProductName GROUP BY tbl_Product_Categories.ProductName) AS ProdCat
WHERE tbl_Consol_Weekly_Product.[Private Banker] = tbl_Bankers_Mapping.[GMIS Revenue Producer]
AND tbl_Consol_Weekly_Product.[Report Type] = ProdCat.ProductName[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]AND tbl_Bankers_Mapping.[Team] = "Taiwan"
GROUP BY ProdCat.ProductName[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]

I have 11 tbl_Product_Categories.ProductName but do not have data in tbl_Consol_Weekly_Product for all 11 ProductName but I want to print all the 11 ProductName. If I remove the condition tbl_Bankers_Mapping.[Team] = "Taiwan" then it works but the movment I add tbl_Bankers_Mapping.[Team] = "Taiwan" in the condition I'm getting only those ProductName which have data in table.

Any ides how to show all the 11 ProductName whether we have data or not?

 

[vbaInet]

So what extra criteria do you think you need to add under tbl_Bankers_Mapping.Team in order fot it to show those that don't exist?