 |  |  |  |  |  |  |  |
Obtaining top 10 performers by category
Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
Hello,
Have a Access problem. I have 1 master table with records by Sales Rep ID (80001, 80002, and so on). For each of these ID's, i have multiple lines of records that show their sales (Revenue) by customer like follows:
80001 5,000 IBM
80001 7,000 Staples
80005 6,000 Motorola
And so on.
I am trying to get the top 10 deals for each of the Sales Rep ID, In other words
Top 10 for 80001-->Data
Top 10 for 80002-->Data
Can anyoneshed some light on this ?
Thanks.
Regards,
Muthu:eek:
Have a Access problem. I have 1 master table with records by Sales Rep ID (80001, 80002, and so on). For each of these ID's, i have multiple lines of records that show their sales (Revenue) by customer like follows:
80001 5,000 IBM
80001 7,000 Staples
80005 6,000 Motorola
And so on.
I am trying to get the top 10 deals for each of the Sales Rep ID, In other words
Top 10 for 80001-->Data
Top 10 for 80002-->Data
Can anyoneshed some light on this ?
Thanks.
Regards,
Muthu:eek:
Hi,
You need to create a query in QBE, then after selecting the table you need try to right click on the table area but not in the table you selected then you will see the "PROPERTIES" there is a "TOP VALUES" type 10 then save your query and run.
By the way, you need to sort the field in decending order also to get faster result.
You need to create a query in QBE, then after selecting the table you need try to right click on the table area but not in the table you selected then you will see the "PROPERTIES" there is a "TOP VALUES" type 10 then save your query and run.
By the way, you need to sort the field in decending order also to get faster result.
•
•
•
•
Originally Posted by muthuivs 
Hello,
Have a Access problem. I have 1 master table with records by Sales Rep ID (80001, 80002, and so on). For each of these ID's, i have multiple lines of records that show their sales (Revenue) by customer like follows:
80001 5,000 IBM
80001 7,000 Staples
80005 6,000 Motorola
And so on.
I am trying to get the top 10 deals for each of the Sales Rep ID, In other words
Top 10 for 80001-->Data
Top 10 for 80002-->Data
Can anyoneshed some light on this ?
Thanks.
Regards,
Muthu:eek:
definitions:
sales is the table.
ID is the sales person ID field in sales table.
Revenue is the revenue field in sales table.
create query - view as SQL - use:
SELECT Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID;
to produce a result with the sum of each sales ID.
SELECT Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
will produce the same where the top row of result is the highest sales.
SELECT top 1 Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
will result in the top 1 sales Id with revenue.
So assuming you have more than 10 sales IDs use:
SELECT top 10 Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
This will only produce a view of top 10 - to further use that data is more complicated. Recommend you get a good book on MS Access.
sales is the table.
ID is the sales person ID field in sales table.
Revenue is the revenue field in sales table.
create query - view as SQL - use:
SELECT Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID;
to produce a result with the sum of each sales ID.
SELECT Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
will produce the same where the top row of result is the highest sales.
SELECT top 1 Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
will result in the top 1 sales Id with revenue.
So assuming you have more than 10 sales IDs use:
SELECT top 10 Sales.ID, Sum(Sales.Revenue) AS SumOfRevenue
FROM Sales
GROUP BY Sales.ID order by sum(sales.revenue) desc ;
This will only produce a view of top 10 - to further use that data is more complicated. Recommend you get a good book on MS Access.
•
•
•
•
Originally Posted by muthuivs
Hello,
Have a Access problem. I have 1 master table with records by Sales Rep ID (80001, 80002, and so on). For each of these ID's, i have multiple lines of records that show their sales (Revenue) by customer like follows:
80001 5,000 IBM
80001 7,000 Staples
80005 6,000 Motorola
And so on.
I am trying to get the top 10 deals for each of the Sales Rep ID, In other words
Top 10 for 80001-->Data
Top 10 for 80002-->Data
Can anyoneshed some light on this ?
Thanks.
Regards,
Muthu:eek:
If your problem is solved then can you post your query.
Thanks
|
Similar Threads
- what is the best anti virus? (Viruses, Spyware and other Nasties)
- Stock Market, anyone? (Geeks' Lounge)
- Price Reduced!!!! TOP COUPON WEBSITE (BRAND AND TRAFFIC) (Websites for Sale)
- FOR SALE Fully Developed Coupon Website TOP WEBSITE (Websites for Sale)
- Permanent PR4 Directory Links - Just $9.95 (Ad Space for Sale)
- Want info on Sequential files (Community Introductions)
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: The student record system
- Next Thread: Display fields in calendar like form
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest MS Access and FileMaker Pro Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
