Subquery to get Top 5...

B

Bob Barnes

I haven't written a Subquery yet...and need..

...the Top 5 "NumTotal" (DESC) by MktMgr..from this...

SELECT GoMarkets.MktMgr, Count(AllSummary.ANSWER) AS NumTotal,
AllSummary.QUESTION AS QuesNum, IIf(ANSWER=2,"No","") AS Ans,
AllSummary.AREAMGR, AllSummary.RegMkt, Questions.TheQuestion
FROM GoMarkets INNER JOIN (AllSummary LEFT JOIN Questions ON
AllSummary.QUESTION = Questions.QuestionNum) ON GoMarkets.TheMkt =
AllSummary.RegMkt
WHERE (((AllSummary.ANSWER)=2 Or (AllSummary.ANSWER)=3) AND
((AllSummary.LASTINSP) Between [Forms]![frmMain]![BegFin] And
[Forms]![frmMain]![EndFin]))
GROUP BY GoMarkets.MktMgr, AllSummary.QUESTION, AllSummary.AREAMGR,
AllSummary.RegMkt, Questions.TheQuestion
HAVING ((Not (AllSummary.RegMkt) Is Null))
ORDER BY Count(AllSummary.ANSWER) DESC , AllSummary.QUESTION;

TIA - Bob
 
P

pietlinden

I haven't written a Subquery yet...and need..

..the Top 5 "NumTotal" (DESC) by MktMgr..from this...

SELECT GoMarkets.MktMgr, Count(AllSummary.ANSWER) AS NumTotal,
AllSummary.QUESTION AS QuesNum, IIf(ANSWER=2,"No","") AS Ans,
AllSummary.AREAMGR, AllSummary.RegMkt, Questions.TheQuestion
FROM GoMarkets INNER JOIN (AllSummary LEFT JOIN Questions ON
AllSummary.QUESTION = Questions.QuestionNum) ON GoMarkets.TheMkt =
AllSummary.RegMkt
WHERE (((AllSummary.ANSWER)=2 Or (AllSummary.ANSWER)=3) AND
((AllSummary.LASTINSP) Between [Forms]![frmMain]![BegFin] And
[Forms]![frmMain]![EndFin]))
GROUP BY GoMarkets.MktMgr, AllSummary.QUESTION, AllSummary.AREAMGR,
AllSummary.RegMkt, Questions.TheQuestion
HAVING ((Not (AllSummary.RegMkt) Is Null))
ORDER BY Count(AllSummary.ANSWER) DESC , AllSummary.QUESTION;

TIA - Bob
Click to expand...

so did you write a select query based on this one that's sorted the
way you want and then put a 5 in the box at the top of the query grid
window? Or you could modify the SQL...
 
B

Bob Barnes

Can I get a Subquery in the QBE Grid?

I can Top 5 w/o a Subquery but that doesn't give the Top 5 by MktMgr.

Or..how about modifying the SQL?

[email protected] said:
I haven't written a Subquery yet...and need..

..the Top 5 "NumTotal" (DESC) by MktMgr..from this...

SELECT GoMarkets.MktMgr, Count(AllSummary.ANSWER) AS NumTotal,
AllSummary.QUESTION AS QuesNum, IIf(ANSWER=2,"No","") AS Ans,
AllSummary.AREAMGR, AllSummary.RegMkt, Questions.TheQuestion
FROM GoMarkets INNER JOIN (AllSummary LEFT JOIN Questions ON
AllSummary.QUESTION = Questions.QuestionNum) ON GoMarkets.TheMkt =
AllSummary.RegMkt
WHERE (((AllSummary.ANSWER)=2 Or (AllSummary.ANSWER)=3) AND
((AllSummary.LASTINSP) Between [Forms]![frmMain]![BegFin] And
[Forms]![frmMain]![EndFin]))
GROUP BY GoMarkets.MktMgr, AllSummary.QUESTION, AllSummary.AREAMGR,
AllSummary.RegMkt, Questions.TheQuestion
HAVING ((Not (AllSummary.RegMkt) Is Null))
ORDER BY Count(AllSummary.ANSWER) DESC , AllSummary.QUESTION;

TIA - Bob
Click to expand...

so did you write a select query based on this one that's sorted the
way you want and then put a 5 in the box at the top of the query grid
window? Or you could modify the SQL...
Click to expand...
 
Top