Select Where Greater Than Count
I'd love a bit of help with this chestnut.
I have a table (let us call it 'table_details') related to another table (let us call it 'table_parent') in a many to one relationship. That is, a parent can have many details. Table_details has a field called [GROUP], in which I am interested.
I need to extract from table_details only those records where there are 3 or more distinct [GROUP]s related to any given parent via the foriegn key field.
I can count how many table_details records relate to each parent thus:
Group By table_detail.TheForiegnKey
Although, this doesn't give me a distinct count, which is what I need.
My main problem however is that I can't seem to get anything useful out of table_detail by including the query detailed above as a subquery. I've tried the following but I just can't seem to get 'InnerCountOfGroups' returned as part of the outer query:
SELECT DetailOuter.ThePrimaryKey, DetailOuter.TheForiegnKey
FROM table_detail AS DetailOuter
WHERE ((Select count(DetailInner.[_GROUP_]) AS InnerCountOfGroups
FROM table_detail AS DetailInner
WHERE DetailInner.TheForiegnKey = DetailOuter.TheForiegnKey
GROUP BY DetailInner.TheForiegnKey))
GROUP BY DetailOuter.TheForiegnKey, DetailOuter.ThePrimaryKey;
I've tried a HAVING clause instead of the red WHERE clause above, but that gave me no joy. I believe I need to return InnerCountOfGroups so that I can have an outer WHERE clause that filters based on InnerCountOfGroups being greater than 3.
Any assistance greatly appreciated. Perhaps there's another way to crack this chestnut.
If it matters (which I suspect it might) I am using Access to front end an SQL Server db.
- Dave Cody