Restricting a field to a certain value using query

[iyappa]

Hi Friends,

My dataset has four fields Student_id, type of sport good ,date, class level.I wish to pop a message out when the particular student exceeds the request of the same kind of sport good twice in the last 3 weeks.

scenario:

If student X gets baseball twice in the last 3 weeks then it should pop out his student id and store the info in a seperate table. Also it should flash a message saying exceeded the number of times.

Data set looks like this

1)1001 xxx baseball 07/05/2007 2)1321 yyy raquet 07/05/2007 3)1001 xxx baseball 06/28/2007

Here xxx should be written in another table.

Is it possible to write a query or a macro in access like this ??

Please let me know. Thanks a lot

[geekgirlau]

INSERT INTO OtherTable ( StudentID )
SELECT StudentID
FROM Request_SportsGoods
WHERE (RequestDate Between DateAdd("ww",-3,Date()) And Date())
GROUP BY StudentID, SportsGood
HAVING Count(SportsGood)>1