Number limiting query

[ariansman]

In table1 students lectures data are stored as:

ID     studentname      lecturesubject      lecturedate
1         steve            politics          3/2/2013 
2         jack             politics          3/2/2013 
3         steve            math              5/2/2013 
4         steve            politics          7/2/2013 
5         jack             politics          8/2/2013 
6         steve            math              8/2/2013 
7         jack             politics          9/2/2013 
8         steve            politics          10/2/2013 
9         steve            math              11/2/2013 
10        steve            physics           13/2/2013 
11        jack             politics          15/2/2013

We need a query in which for each student is allowed to preset a limited number of lectures on a specific subject. For example each student can provide only two lectures on a subject and any more lectures on the same subject presented by him will not be counted. In the above example records 7 8 9 and 11 will not be shown in the query.
Thank you

 

[pr2-eugin]

I think you might need a Query that involves GroupBy clause, with Count <=2 as a criteria..

Check out these links..

http://fontstuff.com/access/acctut04.htm
http://www.techonthenet.com/sql/group_by.php
http://office.microsoft.com/en-gb/access-help/group-by-clause-HA001231482.aspx

 

[ariansman]

thank you, but i was am so naive..
can you let me know about the sql to run this query?
tnx

 

[pr2-eugin]

Air code..

SELECT StudentName, LectureSubject FROM theTableName
GROUP BY StudentName, LectureSubject
HAVING Count(ID) <=2;