swb1 Posted June 26, 2014 Share Posted June 26, 2014 Hi,I am using Access 2007 and am unsure how to get the results that I want. I have joined two tables and am running a query. I get the desired results that I want except for 1 issue. My results come out as such:Employee Department SalesTim A $1MAnn B $2MAnn C $3MJoe D $4MSome of the employees work in more than one department. I want to know who has the highest sales in each department. However, if someone such as "Ann" had the most sales in 2 different departments, I do not want to list her twice in the results. I would like to keep her listed in Department C since she had more sales in Department C than in Department B. Then for Department B, I would like the employee with the next highest sales # listed. I would like the results to look like:Name Department SalesTim A $1MGary B $1.1MAnn C $3MJoe D $4MAnyone know how to do this?Using the SQL code:SELECT employee, max(sales) AS maxDeptSalesFROM SalesGroup By employee;returns the following:Employee SalesAnn $3MGary $1.1MJoe $4MTim $1MThis is exactly what I want, however how do I also show what departments these sales are coming from in the query?Thanks,swb1 Link to comment Share on other sites More sharing options...
justsomeguy Posted June 30, 2014 Share Posted June 30, 2014 If the sales table has a column for department, then include that in the query and group by it also. Link to comment Share on other sites More sharing options...
swb1 Posted July 2, 2014 Author Share Posted July 2, 2014 Running this query: SELECT employee, max(sales) AS maxDeptSalesFROM SalesGROUP BY employee, Department; Returns the following: Employee SalesAnn $2MAnn $3M Gary $1.1MJoe $4MTim $1M Still not what I am looking for :-( Link to comment Share on other sites More sharing options...
swb1 Posted July 2, 2014 Author Share Posted July 2, 2014 Then running this query: SELECT employee, Department, max(sales) AS maxDeptSalesFROM SalesGROUP BY employee, Department; Returns the following: Employee Department SalesAnn B $2MAnn C $3M Gary B $1.1MJoe D $4MTim A $1M Still not what I am looking for :-( Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now