Jump to content

Help with running a query using SQL Code in Access 2007


swb1

Recommended Posts

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...