return only lowest 5 results by account manager

james_halliwell · May 15, 2010

[solved]return only lowest 5 results by account manager

Hi all,

Im looking for advise on how to achive a query that i want to have in my database, i want to find the worst (lowest 5) results by my account managers

The query shows account managers, accounts, brand, sales, quantity

These are summed using totals on the sales and quantity

What i would like access to do is find the lowest 5 brands by account managers is this possible if so could some one advise me on how i can achive this

Many Thanks in advance

Brianwarnock · May 15, 2010

Select Top 5 after sorting into descending order.

Brian

Brianwarnock · May 15, 2010

Just reread your post and realised that I had been too quick as the Top function works over the whole result not by group.

brian

Brianwarnock · May 16, 2010

Ok I've no data to test on but I believe that you need to do a ranking query on your query with the worst sales per manager as 1 and then next as 2 etc, select those ranking <6 and then join this back to your original query on acctmgr and sales to pull the rest of the data.

Try this changing object names as necessary

Code:

SELECT x.acctmgr, x.sales, (select count(*) + 1
           from yourquery
          where acctmgr = x.acctmgr
            and sales < x.sales ) AS rank
FROM yourquery AS x
WHERE ((((select count(*) + 1
           from yourquery
          where acctmgr = x.acctmgr
            and sales < x.sales ))<6))
ORDER BY x.acctmgr, x.sales;

Brian

Brianwarnock · May 21, 2010

Going through some old posts to see if there was any follow up and I see from his public profile that this guy has not been back since posting, presumeably another many forums poster who doesn't mind wasting peoples time.

Brian

james_halliwell · May 23, 2010

Brianwarnock said:
Going through some old posts to see if there was any follow up and I see from his public profile that this guy has not been back since posting, presumeably another many forums poster who doesn't mind wasting peoples time.

Brian

I have changed my ISP and had connection problems, so its not just another person wasting peoples time, always visted this site as many people have helped me out,?

Try find this post on any other site?

Brianwarnock · May 24, 2010

Ok so I owe you an apology, but it seems to be happening so often now that I get a bit fed up with no response so that I, and more importantly others who may end up on a thread via a search , have no idea if the problem is solved or the approach is a dead loss.

Brian

james_halliwell · May 25, 2010

No problems, i can understand your fustration, the query worked a treat i just managed to get it done many thanks for your help and advise and thanks for taking the time to look at the code

i would rep you but it looks like i rep'd you the last time as it says to "Share the love before i can rep you again"

cheers

Brianwarnock · May 25, 2010

Thanks for posting back, glad it worked, don't worry about the reps a comment on the thread is all I need to know that the job is done.

Brian

return only lowest 5 results by account manager

james_halliwell

Registered User.

Brianwarnock

Retired

Brianwarnock

Retired

Brianwarnock

Retired

Brianwarnock

Retired

james_halliwell

Registered User.

Brianwarnock

Retired

james_halliwell

Registered User.

Brianwarnock

Retired

Similar threads

Users who are viewing this thread