swb1
-
Posts
3 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by swb1
-
-
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 :-(
-
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
Help with running a query using SQL Code in Access 2007
in SQL
Posted
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 :-(