Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  How to get last 10 marks per name with access SQL?

Author  Topic  devbox Starting Member 1 Post Posted - 2007-09-24 : 19:52:50 Hello, I have the following query that provides the data I need from a table with id, name, date, and mark. I need the query to get up to the last 10 marks per name based on all marks from last month and only those >=50 further than last month (currently 2 months past last month) Can anyone point me in the right direction? Here is what I have so far... 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 :)