query for 2 most recent periods 5

[robertsquestion]

Hi,

I'm using Microsoft Access 2003 and got the following question.
Table T_Sales contains the following fields:
Customernr
Date
Sales

Now I'm looking for a query that gives me the 2 records (per customernr) that contain the largest sales.
So suppose the table contains (custnr, date, sales) the following:
1000, 01-01-2006, 500
1000, 02-01-2006, 900
1000, 03-01-2006, 200
2000, 01-01-2006, 850
2000, 02-01-2006, 600
2000, 03-01-2006, 900

The result of the query should be:
1000, 01-01-2006, 500
1000, 02-01-2006, 900
2000, 01-01-2006, 850
2000, 03-01-2006, 900

Does anyone know how to solve this in a query?
Thanks in advance for your help!

Robert
The Netherlands

 

[CosmoKramer]

Wow, I didn't realize we had implemented voting for preferential solutions here in the threads.

That being the case, I'll say that I prefer mine.

Si hoc legere scis, nimis eruditionis habes

 

[PHV]

Rudy, I've dropped you an email via the contact form on r937.com

 

[milton747]

Hi folks,

I tried out the solution posted.
It runs but I get every order for each sales rep,
not just the TOP 2.
Any thoughts why the TOP 2 filter is not working?
SQL below.

--------------------------------
Schema uses 3 tables:

T_ORDERTOTALS
Order Total
CustID (FK to T_CUSTOMER)

T_CUSTOMERS
CustID
RepID (FK to T_SALESREPS)

T_SALEREPS
RepID
RepName


========================================

SELECT
O.OrderTotal,
S.repID

FROM
OrderTotals O,
customers C,
salesreps S

WHERE
O.customName = C.customName AND
C.repID = S.repID AND
OrderTotal IN
(SELECT TOP 2 OrderTotal
FROM OrderTotals
WHERE
customName = C.customName AND
C.repID = S.repID
ORDER BY OrderTotal DESC
)

ORDER BY
S.repID,
O.OrderTotal DESC


== eom ===