Multi level (Subgrouped) TOP query

[Phil]

OK. I can run a query that will give me say, the top three
salespersons, sorted by sales, for the month of March.

I can run another one for April. One for May, one for June.

I want to run a single report, that will list top 3 for each "groupby."

Sort it by month and sales, and get.

January Fred 5000
January Joan 4000
January John 3000
February Joan 6000
February Fred 5000
February John 3500
....


how to do?

Thanx for any assistance.

 

[Allen Browne]

See:
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/kb/208822/en-us

Or:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039/en-us

 

[Phil]

See:
How to Create a Top Values Per Group Report
at:
http://support.microsoft.com/kb/208822/en-us

Or:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/kb/210039/en-us

Thanks a lot.

 

[Guest]

Hello Allen. I was in need of this same concept and option 1 with the top
values per group worked perfectly. Here is the issue. I am trying to use
this as a subreport for a main report that contains many unrelated items.
The reference to the report in the query does not work when this is added as
a subreport. How could I get this to work properly. Maybe this option isn't
the way to go for what I am attempting to do. Please let me know if you have
any ideas. I already tried changing the reference in the query to the
following: Reports!MainReport!subreport.field

Please let me know if you have the time. Thank you.

 

[Allen Browne]

Subreports are not open in their own right, so you might try a reference
such as:
Reports.[Report1].[Sub1].Report.[Text0]
where:
- Report1 is the name of the main report;
- Sub1 is the name of the subreport control;
- Text0 is the name of the text box in the subreport.

In most cases, you should be able to use a TOP N query as the source for the
subreport without all that, just by using the
LinkMasterFields/LinkChildFields of the subreport control.

Other alternative approaches:
http://support.microsoft.com/kb/210039/en-us

 

[Guest]

That worked great. Thanks for the quick reference lesson. I really
appreciate your quick response.

Subreports are not open in their own right, so you might try a reference
such as:
Reports.[Report1].[Sub1].Report.[Text0]
where:
- Report1 is the name of the main report;
- Sub1 is the name of the subreport control;
- Text0 is the name of the text box in the subreport.

In most cases, you should be able to use a TOP N query as the source for the
subreport without all that, just by using the
LinkMasterFields/LinkChildFields of the subreport control.

Other alternative approaches:
http://support.microsoft.com/kb/210039/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen. I was in need of this same concept and option 1 with the top
values per group worked perfectly. Here is the issue. I am trying to use
this as a subreport for a main report that contains many unrelated items.
The reference to the report in the query does not work when this is added
as
a subreport. How could I get this to work properly. Maybe this option
isn't
the way to go for what I am attempting to do. Please let me know if you
have
any ideas. I already tried changing the reference in the query to the
following: Reports!MainReport!subreport.field

Please let me know if you have the time. Thank you.