Ok, so I figured out how to get my Top 50 Report going. It works great. However, because my file has nearly 100k records, whenever I want to open the report, it takes roughly 10-15 minutes. Secondly, if I need to print any portion (the report is approximately 10000 pages total). Each page takes about 3-4 minutes to print.
I used the information found in the following URL to create my top 50 list.
http://support.microsoft.com/default.aspx?scid=kb;en-us;109319
Basically, the report runs off a query that lists account numbers, value of account, and revenue generated by that account and shows the top 50 revenue generating accounts per rep. I need to extend that usage. Using the same query, I need to sort by value, but I need to retain the revenue ranking next to each account. If I simply use the same code, it will show the value ranking instead of the revenue ranking.
Long story short, I was wondering if there's a way to have my query sort by revenue, add a autonumber generated ranking, then keep that number static if I resort by another criteria. That will bypass my need for a ranking that only gets put on in the report (hence speeding up my process) and secondly making that ranking information reusable.
If anyone understands what I'm getting at, know your assistance is greatly appreciated.
Oh I thought about making a new table and having it sorted by revenue and then putting in an autonumber field. But that would merely list all accounts ranked by revenue. I need it by revenue per rep. So if there's a way to restart the numbering when the rep changes, that'd be my solution I'm sure.
Again, sorry for the long post, and thanks in advance for any assistance.
Alex Lee
NatCity Investments
I used the information found in the following URL to create my top 50 list.
http://support.microsoft.com/default.aspx?scid=kb;en-us;109319
Basically, the report runs off a query that lists account numbers, value of account, and revenue generated by that account and shows the top 50 revenue generating accounts per rep. I need to extend that usage. Using the same query, I need to sort by value, but I need to retain the revenue ranking next to each account. If I simply use the same code, it will show the value ranking instead of the revenue ranking.
Long story short, I was wondering if there's a way to have my query sort by revenue, add a autonumber generated ranking, then keep that number static if I resort by another criteria. That will bypass my need for a ranking that only gets put on in the report (hence speeding up my process) and secondly making that ranking information reusable.
If anyone understands what I'm getting at, know your assistance is greatly appreciated.
Oh I thought about making a new table and having it sorted by revenue and then putting in an autonumber field. But that would merely list all accounts ranked by revenue. I need it by revenue per rep. So if there's a way to restart the numbering when the rep changes, that'd be my solution I'm sure.
Again, sorry for the long post, and thanks in advance for any assistance.
Alex Lee
NatCity Investments
