Using Group By / Count Subquery with Make Table Query

02-13-2019 04:36 PM
Occasional Contributor

I have a table (tblImprv) with two fields - an unique ImprvID and an associated ParcelID. There can be 1 or more improvements.  What I am trying to do is create a query for this table that returns the ParcelIDs and the number of improvements.   I realize I can do this by summarizing the data on ParcelID and returning the count, but was wondering if something like this can be done using the 'Make Table Query' using a subquery.  The general SQL syntax I would use would be something like that shown below, but Im not clear how to implement in Make Table Query tool or if it can event be done.  Ultimately I want to only select the parcels with 2 or more improvements, but I cant even get past this first step

(SELECT ParcelID, Count(ImprvID) AS CntImpv FROM tblImprv GROUP BY ParcelID;)

Example tblImprv and query result


Tags (2)
0 Kudos
0 Replies