return only lowest 5 results by account manager

[james_halliwell]

[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]

Select Top 5 after sorting into descending order.

Brian

 

[Brianwarnock]

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]

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

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]

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]

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]

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]

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]

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