Hiding Duplicate Data in a Query

[swb1]

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:

Name Department Sales

Tim A $1M
Ann B $2M
Ann C $3M
Joe D $4M

Some 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 Sales

Tim A $1M
Gary B $1.1M
Ann C $3M
Joe D $4M

Anyone know how to do this?

Thanks,

swb1

[June7]

Try:

SELECT [Name], Last(Department) AS Dept, Max(Sales) AS MaxOfSales
FROM tableORqueryname
GROUP BY [Name]
ORDER BY Last(Department);

Nevermind, that query works with the sample data posted but won't for all data. I will have to do some more testing. This is tricky.

Try:

Query1
SELECT Name, Max(Sales) AS MaxOfSales
FROM tableORqueryname
GROUP BY Name;

Query2
SELECT Department, Sales, tablename.Name
FROM Query1 INNER JOIN Courses ON (Query1.MaxOfSales = tablename.Sales) AND (Query1.Name = tablename.Name)
ORDER BY tablename.Department, tablename.Sales DESC;


BTW, Name is a reserved and should avoid reserved words as field names.

[swb1]

Thanks for Replying June 7! That seems to work, but I have another issue. I see that if I set my Return value = 2, then my results look like:

Tim A $1M
Gary B $1.1M

However, since Ann produced $2M in sales in Department B I would have wanted the results to look like:

Tim A $1M
Ann B $2M

Why didn't the query show that result?

Thanks,

swb1

[June7]

What is 'Return' value?

[swb1]

When you go to Design View -> Query Setup, you will see a box label 'Return'. I believe the default is set to 'All'. You can specify how many records are returned when you 'Run' the Query. I set mine to a value = 2.

Thanks,

swb1

[June7]

Okay, RETURN is the TOP N qualifier in the SQL statement.

It is returning the first two records as established by the query I suggested according to the conditions you originally specified.

Now you want the top performer in each department regardless of how many departments they worked in?

[swb1]

The query I would like is for the top performer in each department to be returned, but an employee can not be represented twice in the query results. For example, Ann can not represent both Dept B & C if the Return value is set to 'All'. However, if only 2 records are returned, I would expect for Ann to be listed over Gary since she was the top earner in Dept B. Is this possible using 1 query? or would this have to be multiple queries?

Thanks,

swb1

[June7]

Will need a different query. Ann will be the top performer for both B and C and if you limit the query to TOP 2 then the C record won't show; however, if you say 4, then she will show for both and we are back to the query I already suggested.

I am thinking will need VBA code to handle this.

[swb1]

Ok, I have never used VBA before. Was hoping that I could use some sort of If-Then-Else clause within Access to accomplish this.

[June7]

IIf() function is usable with query but the complication here is that each record must consider values of other records in same table as part of the criteria to select record. That is not simple. And on top of that, the rules change when the dataset is limited by the TOP N condition or to specified department(s).

[hapm]

Isn't the problem here, that the ordering of the query is ascending, while the precedence of selecting the department to show is descending? IMHO that's a weird requirement in it self, as changing the number of records will change the order of employees. If the query is ascending and limited by top, it will bring up the worst performing department first. Is that intended? Shouldn't you take the worst performance of an employee over each department as his record to show? Or do you want to make the query descending anyway? I don't get the requirement here, would be nice to get some background.

[swb1]

Using the SQL code:

SELECT employee, max(sales) AS maxDeptSales
FROM Sales
Group By employee;

returns the following:

Employee Sales

Ann $3M
Gary $1.1M
Joe $4M
Tim $1M

This is exactly what I want, however how do I also show what departments these sales are coming from in the query?

Thanks,

swb1

[June7]

That's the tricky part. Build another query that joins that GROUP BY query to the original dataset, use compound join on the Employee and Sales fields. However, if Employee has multiple records with the same Sales amount, all will be retrieved.