Ad:

MS Access and FileMaker Pro Discussion Thread
Unsolved
Views: 8202

Oct 1st, 2006

Obtaining top 10 performers by category

Expand Post »

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:

Similar Threads

what is the best anti virus? in Viruses, Spyware and other Nasties
Stock Market, anyone? in Geeks' Lounge

muthuivs

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

32 posts
since Jan 2006

Permalink

Oct 4th, 2006

Re: Obtaining top 10 performers by category

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.

Click to Expand / Collapse Quote 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:

jlopez

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Oct 2006

Permalink

Oct 7th, 2006

Re: Obtaining top 10 performers by category

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.

hdhere

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Oct 2006

Permalink

Oct 3rd, 2009

-1

Re: Obtaining top 10 performers by category

Click to Expand / Collapse Quote 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:

Hi,

If your problem is solved then can you post your query.

Thanks

KumarHarsh

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Oct 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS Access and FileMaker Pro Forum Timeline: The student record system

Next Thread in MS Access and FileMaker Pro Forum Timeline: Display fields in calendar like form

DaniWeb Message

Cancel Changes

User Name
Password