Hi All,

I have this group by query(sql code below) that I want to change so
that it returns the Top 5 CountOfQuestion for each LOB, I've tried
several varations but have not been successful. What would the sql
code look like to get the top 5 for each lob???

SELECT tblHeaderPerm.LOB, tblQuestionsPerm.QCode,
tblQuestionsPerm.Question, Count(tblQuestionsPerm.Question) AS
CountOfQuestion
FROM tblHeaderPerm INNER JOIN tblQuestionsPerm ON tblHeaderPerm.AID =
tblQuestionsPerm.AID
WHERE (((tblQuestionsPerm.QCorrect)="no"))
GROUP BY tblHeaderPerm.LOB, tblQuestionsPerm.QCode,
tblQuestionsPerm.Question
ORDER BY tblHeaderPerm.LOB, Count(tblQuestionsPerm.Question) DESC;

thanks
bobh.

bobh wrote:
I should think you would need a counter column (calculated) where you
could also add to the where clause and CounterColVal <= 5. I've seen
column counters created in a SQL statement in this newsgroup using
Dcount(). Ex:
Dcount("ID" < [ID] And "CustomerID" = [CustID] And "OrdDate" <= [OrdDate])

Who knows, maybe you'll need to get the col count from Query 1 and your
above be
Select * From Query1 GroupBy...OrderBy...

On Jan 5, 7:06*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
thanks for your reply................
I actually ended up using medthod 2 of this - http://support.microsoft.com/kb/153747

has anyone else used this???
bobh.

bobh wrote:

Glad it works. I'm curious...are your tables quite small? I wonder how
it'd work with a fairly large dataset. I expect if it was it'd take
some time.

On Jan 11, 6:58*pm, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
right now it goes pretty quick. The process I used is the user
selects several pieces of criteria and I built the sql code and pass
it to a query shell using a querydef. That query is the source of a
groupby make table query and that temp table via a query, which has
the Top function in it, is the source of the Top 3 Error report.
Today it runs quickly but theres only 150,000 records in the detail
table, I don't know what the performance of this report will be when
the detail table grows really big but for now it is good. I did
however put in a popup message when a user selects this report that
says
'Compiling data, Please wait'

bobh.