Prev: Joined table: Returns duplicated record
Next: Syntax for If then Conditional Statement
From: geebee on 27 Jun 2008 10:01
hi,

i have a table with lots of rows. I'm trying to pull out the top 10 records
by dollar amount by employee. i tried the following:

SELECT [employee], [amount]
FROM YourTable
WHERE amount=
(SELECT Top 10 amount
FROM YourTable as Temp
WHERE Temp.[employee] = YourTable.[employee]
ORDER BY amount DESC)

am i on the right track? what i want to retrieve is every employee, and the
top 10 amounts per employee.

thanks in advance,
geebee

From: raskew via AccessMonster.com on 27 Jun 2008 10:54
Try this -
**********************************************
SELECT DISTINCT
t.Employee
, YourTable.Amount
FROM
YourTable AS t
LEFT JOIN
YourTable
ON
t.Employee = YourTable.Employee
WHERE
(((YourTable.Amount) In (
SELECT
Top 10 [Amount]
FROM
YourTable
WHERE
[Employee]= t.[Employee]
ORDER BY
[Amount])))
ORDER BY
t.Employee
, YourTable.Amount DESC;
**********************************************
HTH - Bob

geebee wrote:
>hi,
>
>i have a table with lots of rows. I'm trying to pull out the top 10 records
>by dollar amount by employee. i tried the following:
>
>SELECT [employee], [amount]
>FROM YourTable
>WHERE amount=
> (SELECT Top 10 amount
> FROM YourTable as Temp
> WHERE Temp.[employee] = YourTable.[employee]
> ORDER BY amount DESC)
>
>am i on the right track? what i want to retrieve is every employee, and the
>top 10 amounts per employee.
>
>thanks in advance,
>geebee

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200806/1

From: Klatuu on 27 Jun 2008 11:01
Try:
SELECT [employee], [amount]
FROM YourTable
WHERE amount IN
((SELECT Top 10 amount
FROM YourTable as Temp
WHERE Temp.[employee] = YourTable.[employee]
ORDER BY amount DESC));


--
Dave Hargis, Microsoft Access MVP


"geebee" wrote:

> hi,
>
> i have a table with lots of rows. I'm trying to pull out the top 10 records
> by dollar amount by employee. i tried the following:
>
> SELECT [employee], [amount]
> FROM YourTable
> WHERE amount=
> (SELECT Top 10 amount
> FROM YourTable as Temp
> WHERE Temp.[employee] = YourTable.[employee]
> ORDER BY amount DESC)
>
> am i on the right track? what i want to retrieve is every employee, and the
> top 10 amounts per employee.
>
> thanks in advance,
> geebee
>
 | 
Pages: 1
Prev: Joined table: Returns duplicated record
Next: Syntax for If then Conditional Statement