Access Query for TopN by Group
<p>I've reviewed quite a bit of the sites (e.g. Allen Brown) for creating a query that produces top 5 (or N) values by group.  I think I am getting hung up on the creation of a subquery because I'm referencing a previous query not a table.</p>

<p>I have a query started which counts by month the number of PIs (qryPICountbyMonth).  Currently the below gives a data mismatch expression error:</p>

<pre><code>SELECT qryPI.EventMonth, qryPI.PI_Issue, Count(qryPI.PI_Issue) AS
CountOfPI_Issue
FROM qryPI
GROUP BY qryPI.EventMonth, qryPI.PI_Issue
HAVING (((Count(qryPI.PI_Issue)) In (Select Top 5 [PI_Issue] From [qryPI]
Where [EventMonth]=[qryPI].[EventMonth] Order By [PI_Issue] Desc)))
ORDER BY qryPI.EventMonth DESC , Count(qryPI.PI_Issue) DESC;
</code></pre>

<p>It is built off a a separate query, qryPI</p>

<pre><code>SELECT tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1) AS EventMonth, tblPI.PI_Issue
FROM tblPI
WHERE (((tblPI.EventDate) &gt;= #4/1/2016# And (tblPI.EventDate) &lt;= #5/31/2016#))
GROUP BY tblPI.EventDate, Format([EventDate],'yyyy-mm',1,1), tblPI.PI_Issue;
</code></pre>

<p>I'm hoping to have it generate the top 5 counts of PI_Issue by EventMonth.  If I haven't provided enough info let me know.</p>