TOP 3 of each group

[rpw]

Hi,

I'd like to be able to print a report that prints the top 3 of each group.
However, when I try modifying the SQL to begin with "SELECT TOP 3", I get
only 3 records (apparently those that have the largest DesignerID number).
Here's my current SQL that lists all records by group:

SELECT tblSales.SalesAmount, tblDesigner.DesignerID,
tblDesigner.DesignerFirst, tblDesigner.DesignerLast, tblGroup.GroupName,
tblLocation.LocationName, tblSales.NumberSales, tblSales.NetLeads,
tblGroup.GroupID
FROM tblLocation INNER JOIN (tblGroup INNER JOIN (tblDesigner INNER JOIN
tblSales ON tblDesigner.DesignerID = tblSales.DesignerID) ON tblGroup.GroupID
= tblDesigner.GroupID) ON tblLocation.LocationID = tblDesigner.LocationID;

I'd like to know how to get the report to look like:

Group One
Sally $23,000
Sue $22,000
Sam $21,000
Group Two
Joe $44,000
Jay $39,500
Mary $35,780
Group Three
(list the top three of the group and so on for each group)

Thanks for any help,

 

[Marshall Barton]

I'd like to be able to print a report that prints the top 3 of each group.
However, when I try modifying the SQL to begin with "SELECT TOP 3", I get
only 3 records (apparently those that have the largest DesignerID number).
Here's my current SQL that lists all records by group:

SELECT tblSales.SalesAmount, tblDesigner.DesignerID,
tblDesigner.DesignerFirst, tblDesigner.DesignerLast, tblGroup.GroupName,
tblLocation.LocationName, tblSales.NumberSales, tblSales.NetLeads,
tblGroup.GroupID
FROM tblLocation INNER JOIN (tblGroup INNER JOIN (tblDesigner INNER JOIN
tblSales ON tblDesigner.DesignerID = tblSales.DesignerID) ON tblGroup.GroupID
= tblDesigner.GroupID) ON tblLocation.LocationID = tblDesigner.LocationID;

I'd like to know how to get the report to look like:

Group One
Sally $23,000
Sue $22,000
Sam $21,000
Group Two
Joe $44,000
Jay $39,500
Mary $35,780
Group Three
(list the top three of the group and so on for each group)

First, a clarification. The TOP predicate doesn't mean very
much without an ORDER BY clause to tell it what to take the
top of.

Now, to get the TOP of each group, you need to use a
subquery in the WHERE clause. I think you can use something
like this (air code) query (based on the query you posted
above):

SELECT Q.GroupName, Q.DesignerFirst, Q.SalesAmount
FROM yourquery AS Q
WHERE Q.SalesAmount IN (
SELECT TOP 3 X.SalesAmount
FROM yourquery AS X
WHERE X.GroupID = Q.GroupID
AND X.DesignerID = Q.DesignerID
ORDER BY Q.SalesAmount DESC)

Note that TOP will not do anything to break ties so you
might get four or more in a group if there is a tie for
third place.

 

[Duane Hookom]

Another (non-sql) method is to make sure your report is grouped. Then add a
text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount>3

 

[rpw]

Thank you for the detailed response. I must ask for a little more
clarification because there are some things that I don't understand. (Sorry
for being dumb on this...) Oh, and btw, I used Access' Sorting and Grouping
feature to get the report to sort and group so that's why it doesn't show up
in the SQL I guess. Plus, I built the query via the properties box so the
query doesn't have a name.

If I understand your instructions correctly, I think that I need to build
one query that captures all of the records/fields that I want. Then build a
second query similar to the SQL you offered, right?

I don't understand your use of a "Q" or and "X" where the table name
normally resides or the "AS X". I'm guessing that 'Q' is the name of the
first query, but what's 'X'?

I tried looking up subqueries in my Access 2003 InsideOut but the index
references only A31-A34 on the companion CD (which I don't have with me) and
nothing in the printed pages.

 

[rpw]

Hi Duane,

Thanks for your help. This worked exactly the way I wanted (once I included
the equal sign in the control source!). Cool little short cut and it's easy
to understand how it works too. Thanks again!

Another (non-sql) method is to make sure your report is grouped. Then add a
text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount>3

 

[Marshall Barton]

Responses inline below.
--
Marsh
MVP [MS Access]

Thank you for the detailed response. I must ask for a little more
clarification because there are some things that I don't understand. (Sorry
for being dumb on this...) Oh, and btw, I used Access' Sorting and Grouping
feature to get the report to sort and group so that's why it doesn't show up
in the SQL I guess.

Yes, I had assumed that you were using Sorting and Grouping
for that.

Plus, I built the query via the properties box so the
query doesn't have a name.
If I understand your instructions correctly, I think that I need to build
one query that captures all of the records/fields that I want. Then build a
second query similar to the SQL you offered, right?

Right. I believe you can use the properties box to save the
query with a name. If not, then just Copy/Paste the query's
SQL to the SQL view of new query.

I don't understand your use of a "Q" or and "X" where the table name
normally resides or the "AS X". I'm guessing that 'Q' is the name of the
first query, but what's 'X'?

The Q is an alias for your query's name. I used just to
make it easier to write and read the SQL. The name of your
query appears in the main query's FROM clause and the AS Q
is how you assign Q as the alias name for use throughout the
rest of the query.

The X is also an alias for your query's name, but in the
subquery. It is required here to distinguish a field in the
subquery from the same field in the main query.

I tried looking up subqueries in my Access 2003 InsideOut but the index
references only A31-A34 on the companion CD (which I don't have with me) and
nothing in the printed pages.

That is an excellent book, but for more details about SQL,
you should dig into a book specific to SQL. I don't think
there are many for Access/Jet's dialect (maybe(?) SQL for
Dummies), so don't take every detail in any book too
literally unless it says it's for Access. Each SQL dialect
has its own idiosyncracies, but the overall ideas are pretty
much the same for all of dialect. Check your local library,
they should have several books on this subject.

 

[rpw]

Thank you for your continued assistance. I think that I've followed
instructions correctly however, I don't get the results I expected - I
continue to get all records instead of the top 3 for each group. Here's the
SQL from Access with the automatically added extra parenthesis:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID and X.DesignerID = Q.DesignerID ORDER BY
Q.SumofSalesAmount DESC)));

Other than a few field name changes and those extra parenthesis, it appears
the same as your (air code). Do you see anything wrong?

Responses inline below.
--
Marsh
MVP [MS Access]

Thank you for the detailed response. I must ask for a little more
clarification because there are some things that I don't understand. (Sorry
for being dumb on this...) Oh, and btw, I used Access' Sorting and Grouping
feature to get the report to sort and group so that's why it doesn't show up
in the SQL I guess.

Yes, I had assumed that you were using Sorting and Grouping
for that.

Plus, I built the query via the properties box so the
query doesn't have a name.
If I understand your instructions correctly, I think that I need to build
one query that captures all of the records/fields that I want. Then build a
second query similar to the SQL you offered, right?

Right. I believe you can use the properties box to save the
query with a name. If not, then just Copy/Paste the query's
SQL to the SQL view of new query.

I don't understand your use of a "Q" or and "X" where the table name
normally resides or the "AS X". I'm guessing that 'Q' is the name of the
first query, but what's 'X'?

The Q is an alias for your query's name. I used just to
make it easier to write and read the SQL. The name of your
query appears in the main query's FROM clause and the AS Q
is how you assign Q as the alias name for use throughout the
rest of the query.

The X is also an alias for your query's name, but in the
subquery. It is required here to distinguish a field in the
subquery from the same field in the main query.

I tried looking up subqueries in my Access 2003 InsideOut but the index
references only A31-A34 on the companion CD (which I don't have with me) and
nothing in the printed pages.

That is an excellent book, but for more details about SQL,
you should dig into a book specific to SQL. I don't think
there are many for Access/Jet's dialect (maybe(?) SQL for
Dummies), so don't take every detail in any book too
literally unless it says it's for Access. Each SQL dialect
has its own idiosyncracies, but the overall ideas are pretty
much the same for all of dialect. Check your local library,
they should have several books on this subject.

 

[Marshall Barton]

Ahh bleep! I used a Q instead of X in the subquery's ORDER
BY clause.
ORDER BY X.SumofSalesAmount DESC

 

[rpw]

Hi Marsh,

Sorry, that's not changing the results - still not getting top 3 per group.
I'm getting by with Duane's solution, but I'd really like to learn how get
the results this way too.

Ahh bleep! I used a Q instead of X in the subquery's ORDER
BY clause.
ORDER BY X.SumofSalesAmount DESC
--
Marsh
MVP [MS Access]

Thank you for your continued assistance. I think that I've followed
instructions correctly however, I don't get the results I expected - I
continue to get all records instead of the top 3 for each group. Here's the
SQL from Access with the automatically added extra parenthesis:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID and X.DesignerID = Q.DesignerID ORDER BY
Q.SumofSalesAmount DESC)));

Other than a few field name changes and those extra parenthesis, it appears
the same as your (air code). Do you see anything wrong?

 

[Marshall Barton]

That should have changed the results somewhat at least, but
I guess we should not use the GroupId in the subquery's
Where clause:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3
X.SumofSalesAmount FROM qryOne AS X
WHERE X.DesignerID = Q.DesignerID
ORDER BY X.SumofSalesAmount DESC)))

 

[rpw]

This doesn't work either. Maybe the problem is not in this query. Maybe the
problem is the SQL of qryOne or maybe it's how I have the report configured?

That should have changed the results somewhat at least, but
I guess we should not use the GroupId in the subquery's
Where clause:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3
X.SumofSalesAmount FROM qryOne AS X
WHERE X.DesignerID = Q.DesignerID
ORDER BY X.SumofSalesAmount DESC)))
--
Marsh
MVP [MS Access]

Sorry, that's not changing the results - still not getting top 3 per group.
I'm getting by with Duane's solution, but I'd really like to learn how get
the results this way too.

 

[Marshall Barton]

Let's not worry about the report until the query is working.

I may still be misunderstanding your goal here, but I think
my last post should be doing the job as I understand it.
Try adding a WHERE clause to your original query to reduce
the data down to a managable amount. Run the query and
manually calculate the results. Then run my query against
it to see where the results differ. Maybe we can figure
something out from the smaller dataset?
--
Marsh
MVP [MS Access]

This doesn't work either. Maybe the problem is not in this query. Maybe the
problem is the SQL of qryOne or maybe it's how I have the report configured?

That should have changed the results somewhat at least, but
I guess we should not use the GroupId in the subquery's
Where clause:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3
X.SumofSalesAmount FROM qryOne AS X
WHERE X.DesignerID = Q.DesignerID
ORDER BY X.SumofSalesAmount DESC)))
--
Marsh
MVP [MS Access]

Sorry, that's not changing the results - still not getting top 3 per group.
I'm getting by with Duane's solution, but I'd really like to learn how get
the results this way too.

:
Ahh bleep! I used a Q instead of X in the subquery's ORDER
BY clause.
ORDER BY X.SumofSalesAmount DESC


rpw wrote:
Thank you for your continued assistance. I think that I've followed
instructions correctly however, I don't get the results I expected - I
continue to get all records instead of the top 3 for each group. Here's the
SQL from Access with the automatically added extra parenthesis:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID and X.DesignerID = Q.DesignerID ORDER BY
Q.SumofSalesAmount DESC)));

Other than a few field name changes and those extra parenthesis, it appears
the same as your (air code). Do you see anything wrong?

:
Responses inline below.>>

rpw wrote:
Thank you for the detailed response. I must ask for a little more
clarification because there are some things that I don't understand. (Sorry
for being dumb on this...) Oh, and btw, I used Access' Sorting and Grouping
feature to get the report to sort and group so that's why it doesn't show up
in the SQL I guess.

Yes, I had assumed that you were using Sorting and Grouping
for that.


Plus, I built the query via the properties box so the
query doesn't have a name.
If I understand your instructions correctly, I think that I need to build
one query that captures all of the records/fields that I want. Then build a
second query similar to the SQL you offered, right?

Right. I believe you can use the properties box to save the
query with a name. If not, then just Copy/Paste the query's
SQL to the SQL view of new query.


I don't understand your use of a "Q" or and "X" where the table name
normally resides or the "AS X". I'm guessing that 'Q' is the name of the
first query, but what's 'X'?

The Q is an alias for your query's name. I used just to
make it easier to write and read the SQL. The name of your
query appears in the main query's FROM clause and the AS Q
is how you assign Q as the alias name for use throughout the
rest of the query.

The X is also an alias for your query's name, but in the
subquery. It is required here to distinguish a field in the
subquery from the same field in the main query.


I tried looking up subqueries in my Access 2003 InsideOut but the index
references only A31-A34 on the companion CD (which I don't have with me) and
nothing in the printed pages.

That is an excellent book, but for more details about SQL,
you should dig into a book specific to SQL. I don't think
there are many for Access/Jet's dialect (maybe(?) SQL for
Dummies), so don't take every detail in any book too
literally unless it says it's for Access. Each SQL dialect
has its own idiosyncracies, but the overall ideas are pretty
much the same for all of dialect. Check your local library,
they should have several books on this subject.



rpw wrote:
I'd like to be able to print a report that prints the top 3 of each group.
However, when I try modifying the SQL to begin with "SELECT TOP 3", I get
only 3 records (apparently those that have the largest DesignerID number).
Here's my current SQL that lists all records by group:

SELECT tblSales.SalesAmount, tblDesigner.DesignerID,
tblDesigner.DesignerFirst, tblDesigner.DesignerLast, tblGroup.GroupName,
tblLocation.LocationName, tblSales.NumberSales, tblSales.NetLeads,
tblGroup.GroupID
FROM tblLocation INNER JOIN (tblGroup INNER JOIN (tblDesigner INNER JOIN
tblSales ON tblDesigner.DesignerID = tblSales.DesignerID) ON tblGroup.GroupID
= tblDesigner.GroupID) ON tblLocation.LocationID = tblDesigner.LocationID;

I'd like to know how to get the report to look like:

Group One
Sally $23,000
Sue $22,000
Sam $21,000
Group Two
Joe $44,000
Jay $39,500
Mary $35,780
Group Three
(list the top three of the group and so on for each group)


:
First, a clarification. The TOP predicate doesn't mean very
much without an ORDER BY clause to tell it what to take the
top of.

Now, to get the TOP of each group, you need to use a
subquery in the WHERE clause. I think you can use something
like this (air code) query (based on the query you posted
above):

SELECT Q.GroupName, Q.DesignerFirst, Q.SalesAmount
FROM yourquery AS Q
WHERE Q.SalesAmount IN (
SELECT TOP 3 X.SalesAmount
FROM yourquery AS X
WHERE X.GroupID = Q.GroupID
AND X.DesignerID = Q.DesignerID
ORDER BY Q.SalesAmount DESC)

Note that TOP will not do anything to break ties so you
might get four or more in a group if there is a tie for
third place.

 

[rpw]

Thank you for sticking with me on this. I experimented and found a SQL that
works:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID
ORDER BY X.SumofSalesAmount DESC)));

Now that it works, the SQL makes sense to me (or maybe less confusing?). I
guess that now Q is selecting all the information I want WHERE (X is
selecting the top 3 sales amounts of each group that appears in Q).

By including the DesignerID in the X query, I kept getting all of the
designer's sales amount results. If I would have had a larger sample
dataset, the result probably would have given me the top three sales of the
top three designers within each group.

This has certainly been a learning experience for me. I want to thank you
for all of your help and for your patience and for sticking with me until I
got the results I wanted via SQL.

Have a good one!

Let's not worry about the report until the query is working.

I may still be misunderstanding your goal here, but I think
my last post should be doing the job as I understand it.
Try adding a WHERE clause to your original query to reduce
the data down to a managable amount. Run the query and
manually calculate the results. Then run my query against
it to see where the results differ. Maybe we can figure
something out from the smaller dataset?
--
Marsh
MVP [MS Access]

This doesn't work either. Maybe the problem is not in this query. Maybe the
problem is the SQL of qryOne or maybe it's how I have the report configured?

That should have changed the results somewhat at least, but
I guess we should not use the GroupId in the subquery's
Where clause:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3
X.SumofSalesAmount FROM qryOne AS X
WHERE X.DesignerID = Q.DesignerID
ORDER BY X.SumofSalesAmount DESC)))
--
Marsh
MVP [MS Access]


rpw wrote:
Sorry, that's not changing the results - still not getting top 3 per group.
I'm getting by with Duane's solution, but I'd really like to learn how get
the results this way too.

:
Ahh bleep! I used a Q instead of X in the subquery's ORDER
BY clause.
ORDER BY X.SumofSalesAmount DESC


rpw wrote:
Thank you for your continued assistance. I think that I've followed
instructions correctly however, I don't get the results I expected - I
continue to get all records instead of the top 3 for each group. Here's the
SQL from Access with the automatically added extra parenthesis:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID and X.DesignerID = Q.DesignerID ORDER BY
Q.SumofSalesAmount DESC)));

Other than a few field name changes and those extra parenthesis, it appears
the same as your (air code). Do you see anything wrong?

:
Responses inline below.>>

rpw wrote:
Thank you for the detailed response. I must ask for a little more
clarification because there are some things that I don't understand. (Sorry
for being dumb on this...) Oh, and btw, I used Access' Sorting and Grouping
feature to get the report to sort and group so that's why it doesn't show up
in the SQL I guess.

Yes, I had assumed that you were using Sorting and Grouping
for that.


Plus, I built the query via the properties box so the
query doesn't have a name.
If I understand your instructions correctly, I think that I need to build
one query that captures all of the records/fields that I want. Then build a
second query similar to the SQL you offered, right?

Right. I believe you can use the properties box to save the
query with a name. If not, then just Copy/Paste the query's
SQL to the SQL view of new query.


I don't understand your use of a "Q" or and "X" where the table name
normally resides or the "AS X". I'm guessing that 'Q' is the name of the
first query, but what's 'X'?

The Q is an alias for your query's name. I used just to
make it easier to write and read the SQL. The name of your
query appears in the main query's FROM clause and the AS Q
is how you assign Q as the alias name for use throughout the
rest of the query.

The X is also an alias for your query's name, but in the
subquery. It is required here to distinguish a field in the
subquery from the same field in the main query.


I tried looking up subqueries in my Access 2003 InsideOut but the index
references only A31-A34 on the companion CD (which I don't have with me) and
nothing in the printed pages.

That is an excellent book, but for more details about SQL,
you should dig into a book specific to SQL. I don't think
there are many for Access/Jet's dialect (maybe(?) SQL for
Dummies), so don't take every detail in any book too
literally unless it says it's for Access. Each SQL dialect
has its own idiosyncracies, but the overall ideas are pretty
much the same for all of dialect. Check your local library,
they should have several books on this subject.



rpw wrote:
I'd like to be able to print a report that prints the top 3 of each group.
However, when I try modifying the SQL to begin with "SELECT TOP 3", I get
only 3 records (apparently those that have the largest DesignerID number).
Here's my current SQL that lists all records by group:

SELECT tblSales.SalesAmount, tblDesigner.DesignerID,
tblDesigner.DesignerFirst, tblDesigner.DesignerLast, tblGroup.GroupName,
tblLocation.LocationName, tblSales.NumberSales, tblSales.NetLeads,
tblGroup.GroupID
FROM tblLocation INNER JOIN (tblGroup INNER JOIN (tblDesigner INNER JOIN
tblSales ON tblDesigner.DesignerID = tblSales.DesignerID) ON tblGroup.GroupID
= tblDesigner.GroupID) ON tblLocation.LocationID = tblDesigner.LocationID;

I'd like to know how to get the report to look like:

Group One
Sally $23,000
Sue $22,000
Sam $21,000
Group Two
Joe $44,000
Jay $39,500
Mary $35,780
Group Three
(list the top three of the group and so on for each group)


:
First, a clarification. The TOP predicate doesn't mean very
much without an ORDER BY clause to tell it what to take the
top of.

Now, to get the TOP of each group, you need to use a
subquery in the WHERE clause. I think you can use something
like this (air code) query (based on the query you posted
above):

SELECT Q.GroupName, Q.DesignerFirst, Q.SalesAmount
FROM yourquery AS Q
WHERE Q.SalesAmount IN (
SELECT TOP 3 X.SalesAmount
FROM yourquery AS X
WHERE X.GroupID = Q.GroupID
AND X.DesignerID = Q.DesignerID
ORDER BY Q.SalesAmount DESC)

Note that TOP will not do anything to break ties so you
might get four or more in a group if there is a tie for
third place.

 

[Marshall Barton]

Thank you for sticking with me on this. I experimented and found a SQL that
works:

SELECT Q.GroupName, Q.Designer, Q.SumOfSalesAmount
FROM qryOne AS Q
WHERE (((Q.SumOfSalesAmount) In (SELECT TOP 3 X.SumofSalesAmount FROM qryOne
AS X WHERE X.GroupID = Q.GroupID
ORDER BY X.SumofSalesAmount DESC)));

Now that it works, the SQL makes sense to me (or maybe less confusing?). I
guess that now Q is selecting all the information I want WHERE (X is
selecting the top 3 sales amounts of each group that appears in Q).

By including the DesignerID in the X query, I kept getting all of the
designer's sales amount results. If I would have had a larger sample
dataset, the result probably would have given me the top three sales of the
top three designers within each group.

This has certainly been a learning experience for me. I want to thank you
for all of your help and for your patience and for sticking with me until I
got the results I wanted via SQL.

Ahh yes, now I see where I misunderstod the problem. What
you ended up with makes sense and looks good. Sorry about
stumbling along the road to the right answer, but at least
you learned enough to straighten it out on your own. I
think that qualifies as success, even if it took a little
longer than it should have.

 

[António]

hi Duane

Just one quick question please

If i use the non-sql method, do i have to create a macro with the event
Cancel?

thanks
António

Another (non-sql) method is to make sure your report is grouped. Then add a
text box to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No

Then add code to the On Format event of the detail section:
Cancel = Me.txtCount>3