How to get last 10 marks per name with access SQL?

[devbox]

Hello,

I have the following query that provides the data I need from a table with name, date, and mark; however I can't seem to figure out how to limit the query output to only the last 10 marks by date per name. (The query simply gets all marks from last month and only those >=50 further than last month - this works fine) Can anyone point me in the right direction? Here is what I have so far...

SELECT DISTINCT A.name, A.mdate, A.mark
FROM [SELECT data.name, data.mdate, data.mark FROM DATA
WHERE ((DATA.mdate Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)))
UNION
SELECT data.name, data.mdate, data.mark FROM DATA
WHERE ((DATA.mdate<DateSerial(Year(Date()),Month(Date())-1,1)) AND ((DATA.mark)>=50))]. AS A;

Thanks!

 

[maxhugen]

Save your union query, then use it as the basis for another query. In this second query, sort by date in descending order, and set the query property 'Top Values' to 10.

HTH

Max Hugen
Australia

 

[devbox]

Thanks for the relpy. With setting top values to 10 it will return the top 10 of all names, and my problem is that is need 10 for each name.

 

[PHV]

You may try something like this:

SELECT A.name, A.mdate, A.mark
FROM DATA AS A INNER JOIN DATA AS B ON A.name=B.Name AND A.mdate<=B.mdate
WHERE (A.mdate Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0))
OR (A.mdate<DateSerial(Year(Date()),Month(Date())-1,1) AND A.mark>=50)
GROUP BY A.name, A.mdate, A.mark
HAVING Count(*)<=10
ORDER BY 1, 2 DESC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[devbox]

Yes! PHV you are a genius! This works perfectly!

Thank you very much!

DB

 

[devbox]

Ran into a few issues upon further testing. Had to include the id in case of multiple records with same name, date, and mark. Also having problem as it does not display all 10 that meet the criteria within the date ranges.

This is the code I have now...

SELECT A.ID, A.NAME, A.EDATE, A.EMARK
FROM DATA AS A INNER JOIN DATA AS B ON (A.NAME = B.NAME) and (A.edate<=b.edate)
WHERE (((A.EDATE)>=DateSerial(Year(Date()),Month(Date())-1,1) And (A.EDATE)<=DateSerial(Year(Date()),Month(Date()),0))) OR (((A.EDATE)<=DateSerial(Year(Date()),Month(Date())-1,0) And (A.EDATE)>=DateSerial(Year(Date()),Month(Date())-3,1)) AND ((A.EMARK)>=50))
GROUP BY A.ID, A.NAME, A.EDATE, A.EMARK
HAVING (((Count(*))<=10))
ORDER BY 2,3 asc;

Here is the test data...

19826 Bill 06/01/07 100
19829 Bill 06/30/07 90
19830 Bill 07/01/07 100
19832 Bill 07/29/07 80
19833 Bill 07/31/07 90
19835 Bill 08/01/07 100
19836 Bill 08/02/07 49
19828 Bill 08/29/07 50
19837 Bill 08/29/07 80
19838 Bill 08/31/07 90


Here is the current query result...

19829 Bill 06/30/07 90
19830 Bill 07/01/07 100
19832 Bill 07/29/07 80
19833 Bill 07/31/07 90
19835 Bill 08/01/07 100
19836 Bill 08/02/07 49
19828 Bill 08/29/07 50
19837 Bill 08/29/07 80
19838 Bill 08/31/07 90

For some reason it does not show the 10th record on 06/01/2007.

Help