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:

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.

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.