Group By problem with Left Join

[bb3261]

I have the following query:

SELECT plo_categories.description, plo_categories.category_id, plo_items.item_id, plo_items.description, plo_data.value, Count(plo_data.item_id) AS MyCount
FROM plo_categories INNER JOIN (plo_items INNER JOIN plo_data ON plo_items.item_id=plo_data.item_id) ON plo_categories.category_id=plo_items.category_id
GROUP BY plo_items.item_id, plo_data.value, plo_categories.category_id, plo_categories.description, plo_items.description
ORDER BY plo_items.item_id, plo_data.value DESC

which returns a message box of 'No current record' when run, even though I know there's appropriate data in all three tables of the join. If I take out the GROUP BY clause (and the COUNT) it returns all the data properly, or if I keep the GROUP BY clause and change the LEFT JOIN to an INNER JOIN, it returns the joined data properly, but the combination of the LEFT JOIN and the GROUP BY gives me that error. Shouldn't a LEFT JOIN return more records?

 

[bb3261]

I actually figured this one out on my own. If you do an outer join to a table that has Yes/No fields, and you wish to group by those fields, the NULLS that result from the other table not having matches on that field cause a problem, because Access is only expecting the value of those fields to be either Yes or No, not NULL. I changed the data type to "Text" and it works fine.