CODEWITHSUNDEEP
sqlms-access
Shalev Shimon
Shalev Shimon

Reputation: 1

How do i choose names of students who took a test between 2 dates?

I need to show student id's who took a test both between 01/09/2007 - 31/12/2007 and between 01/01/2008 - 20/06/2008. For some reason, it doesn't work. can I get some help from you? thank you (The text below is what I wrote).

SELECT studentId
FROM gradesTbl
WHERE testDate BETWEEN "01/09/2007" AND "31/12/2007" AND
WHERE testDate BETWEEN "01/01/2008" AND "20/06/2008";

Upvotes: 0

Views: 99

Answers (4)

forpas
forpas

Reputation: 164069

You can do it with EXISTS:

SELECT DISTINCT g1.studentId
FROM gradesTbl AS g1
WHERE 
  EXISTS (SELECT 1 FROM gradesTbl AS g2 WHERE g2.studentId = g1.studentId AND  g2.testDate BETWEEN "01/09/2007" AND "31/12/2007")
  AND 
  EXISTS (SELECT 1 FROM gradesTbl AS g2 WHERE g2.studentId = g1.studentId AND  g2.testDate BETWEEN "01/01/2008" AND "20/06/2008");

But it would be better to select from the table students, which I believe you have and studentId in gradesTbl references its id:

SELECT s.studentId
FROM students AS s
WHERE 
  EXISTS (SELECT 1 FROM gradesTbl AS g WHERE g.studentId = s.studentId AND  g.testDate BETWEEN "01/09/2007" AND "31/12/2007")
  AND 
  EXISTS (SELECT 1 FROM gradesTbl AS g WHERE g.studentId = s.studentId AND  g.testDate BETWEEN "01/01/2008" AND "20/06/2008");

Note: Dates in Access are not Text but DateTime:

BETWEEN #2007/09/01# AND #2007/12/31#
BETWEEN #2008/01/01# AND #2008/06/20#

Upvotes: 3

iDevlop
iDevlop

Reputation: 25252

In Access the date separator is the # sign.
Also the logic operator here should be OR.
So the right query is

SELECT studentId 
FROM gradesTbl
WHERE testDate BETWEEN #01/09/2007# AND #31/12/2007# 
OR testDate BETWEEN #01/01/2008# AND #20/06/2008#;

Since a grade can NEVER answer to both conditions, if you want students who had tests in both period, you will need to group your data.

SELECT studentId, 
       sum(iif(testDate BETWEEN #01/09/2007# AND #31/12/2007#;1;0)) period1
       sum(iif(testDate BETWEEN #01/01/2008# AND #20/06/2008#;1;0)) period2
FROM gradesTbl
GROUP BY studentId
HAVING sum(iif(testDate BETWEEN #01/09/2007# AND #31/12/2007#;1;0)) > 0
   AND sum(iif(testDate BETWEEN #01/01/2008# AND #20/06/2008#;1;0)) > 0

Note: not tested - you might have to replace some ";" by "," depending on your regional settings.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use aggregation:

SELECT studentId
FROM gradesTbl
GROUP BY studentId
HAVING SUM(IIF(testDate BETWEEN "01/09/2007" AND "31/12/2007", 1, 0) > 0 AND
       SUM(IIF(testDate BETWEEN "01/01/2008" AND "20/06/2008", 1, 0) > 0;

Each clause in the aggregation is counting the number of tests that each students takes in the specified period. The > 0 is returning only students who have taken at least one test.

Upvotes: 1

Lee Mac
Lee Mac

Reputation: 16015

You could also accomplish this using inner joins on subqueries, e.g.:

select distinct t.studentid
from 
    (
        gradestbl t inner join
        (select distinct t.studentid from gradestbl t where t.testdate between #2007-09-01# and #2007-12-31#) q1
        on t.studentid = q1.studentid
    ) 
    inner join
    (select distinct t.studentid from gradestbl t where t.testdate between #2008-01-01# and #2008-06-20#) q2
    on t.studentid = q2.studentid

Upvotes: 0

Related Questions