Query doesn't return empty Brand fields

Question

I am having trouble pulling data from a table in a query and returnig all results if i have a blank field in 'Brand'.

I have two queries, this is the first, it pulls essentially the same data but only returns the ID numbers of Brand and Leader not the resolved names from the related tables.

SELECT Review.ID, Review.Claim, [TSO Leader].LeaderID, Review.Consultant, Review.TSO, Review.[Claim Review Date], Review.Brand, Review.[Discussion Point], Review.[Review Notes], Review.[Review Decision], Review.Categories, Review.[Product Type]
FROM [TSO Leader] INNER JOIN Review ON [TSO Leader].LeaderID = Review.[TSO Leader]
ORDER BY [TSO Leader].LeaderID;

This query pulls resolved 'TSO Leader' and 'Brand' but if the brand is empty then it wont return the result. How can I edit it to show Brand fields even if they are null.

This is the second query that doesn't pull null fields from Brand.

SELECT Review.ID, Review.Claim, Review.Consultant, Review.TSO, Review.[Claim Review Date], Review.[Discussion Point], Review.[Review Notes], Review.[Review Decision], Brand.Brand, Categories.Reason, [TSO Leader].LeaderName
FROM [TSO Leader] INNER JOIN (Categories INNER JOIN (Brand INNER JOIN Review ON Brand.BrandID=Review.Brand) ON Categories.CategoriesID=Review.Categories) ON [TSO Leader].LeaderID=Review.[TSO Leader];

EDIT: From Mitch's comment below attempted to expand result set via outer join but I receive a syntax error. This is the updated from.

FROM [TSO Leader] INNER JOIN (Categories INNER JOIN (Brand FULL OUTER JOIN Review ON Brand.BrandID=Review.Brand) ON Categories.CategoriesID=Review.Categories) ON [TSO Leader].LeaderID=Review.[TSO Leader];

Inner join will do that - see codinghorror.com/blog/2007/10/… — Mitch
it would be, yes. However, I didn't want to add a answer with just a link. — Mitch
If it is just the review record that could be missing, a left Join (or left outer join I forget the exact syntax) should work — Bruce Martin

sayth sayth · Accepted Answer · 2013-07-17T01:09:53

I was able to retrieve the records with a right join. Access kept complaining that putting an outer join where I have the right one was a syntax error. It appears access should use Outer joins as I read from the docs here http://office.microsoft.com/en-us/access-help/creating-an-outer-join-query-in-access-HA001034555.aspx

SELECT Review.ID, Review.Claim, Review.Consultant, Review.TSO, Review.[Claim Review Date], Review.[Discussion Point], Review.[Review Notes], Review.[Review Decision], Brand.Brand, Categories.Reason, [TSO Leader].LeaderName
FROM [TSO Leader] INNER JOIN (Categories INNER JOIN (Brand RIGHT JOIN Review ON Brand.BrandID=Review.Brand) ON Categories.CategoriesID=Review.Categories) ON [TSO Leader].LeaderID=Review.[TSO Leader];

Query doesn't return empty Brand fields

1 Answers