Using Group By / Count Subquery with Make Table Query

NealBanerjee — Thu, 14 Feb 2019 00:36:00 GMT

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

topic Using Group By / Count Subquery with Make Table Query in Geoprocessing Questions

Using Group By / Count Subquery with Make Table Query