sql - How to perform count in access across three tables? -

i having 3 tables, sake of simplicity let's category (1...many) topic (1...many) post

what trying achieve categoryid , total number of topics in category total number of posts.

the best result made using following query:

select category.id, count(topic.id) topiccount, count(post.id) postcount ((category)  left join topic  on topic.categoryid = category.id)  left join post on post.topicid = topic.id group category.id 

unfortunately, if have 6 topics in table associated category getting '7' result. did research on , seems have use distinct keyword inside count access not support , not find appropriate way in subqueries.

thank help!

you 1 more record, because not counting topics, counting topic-post joined records. use following :

select category.id, count(topic.id), nz(sum(numofposts),0) (category left join (     select topic.id, count(post.id) numofposts, topic.categoryid     topic left join post on topic.id = post.topicid     group topic.id, topic.categoryid ) tp on category.id=tp.categoryid) group category.id 

the nz there ensure in empty topics don't null sums