3 Level report

[Guest]

Greetings, thank you for looking. I have a table that holds information on
who business units are spending money with. It consists of 4 fields.

[LOB] - the line of business, there are many lines but each line belongs to
a group
[Group] - there are 16 groups
[Vendor] - there are 10 possible vendors that a LOB could spend with
[AMT] - This is how much the LOB spent in the transaction

I would like a report that will list the top 5 spending LOBs for each vendor
and group...it should look something like this...

Group1
Vendor1
Top LOB
Second LOB
Vendor2
Top LOB
Second LOB
Group2
Vendor1
Top LOB
Second LOB
Vendor2
Top LOB
Second LOB

I hope this gives a clear picture...I can accomplish the first grouping, but
I am unable to get good data for the second grouping. It repeats teh first
vendors data throughout that group. Any help would be greatly appreciated.
If more information is needed or I have not been clear please let me know.

Thank you in advance.

-John

 

[Marshall Barton]

Greetings, thank you for looking. I have a table that holds information on
who business units are spending money with. It consists of 4 fields.

[LOB] - the line of business, there are many lines but each line belongs to
a group
[Group] - there are 16 groups
[Vendor] - there are 10 possible vendors that a LOB could spend with
[AMT] - This is how much the LOB spent in the transaction

I would like a report that will list the top 5 spending LOBs for each vendor
and group...it should look something like this...

Group1
Vendor1
Top LOB
Second LOB
Vendor2
Top LOB
Second LOB
Group2
Vendor1
Top LOB
Second LOB
Vendor2
Top LOB
Second LOB

I hope this gives a clear picture...I can accomplish the first grouping, but
I am unable to get good data for the second grouping. It repeats teh first
vendors data throughout that group. Any help would be greatly appreciated.
If more information is needed or I have not been clear please let me know.

If you have a query that collects the needed data records
from the appropriate tables, then you can use the report's
Sorting and Grouping to group (with header) on the Group
ID/Name first and Vendor second. Then Sort on the total LOB
Amt field.

In other words, most of that report should be simple, once
you create the needed query. Not knowing anything about
your tables, all I can provide is a general idea:

SELECT group, vendor, LOB, Sum(AMT) As SumOfAmt
FROM ???
GROUP BY group, vendor, LOB

If you can get a query to do what's needed, we can fool
around either in another query or in the report to limit the
LOBs per vendor.