Finding multiple instances in a table

Question

I have two tables in my database:

Events:

Student:

I have an SQL query that queries the 'Events' table to show me when a student has coming in (where In_Or_Out = 'In')later than 08:40 in one week period:

SELECT RFID,Event_Date_Time
FROM Events
WHERE Events.Event_Date_Time BETWEEN #3/24/2015# And #3/17/2015# AND Events.In_Or_Out='In' AND Format(Event_Date_Time,'HH:MM')>#8:40:00# 

Now I'm trying to find out if a student has been late twice or more in one week. I've tried the following SQL query but it doesn't work:

SELECT *
FROM Events
WHERE Events.Event_Date_Time BETWEEN #3/24/2015# And #3/17/2015# AND Events.In_Or_Out='In' AND Format(Event_Date_Time,'HH:MM')>#8:40:00#
GROUP BY RFID
HAVING COUNT(RFID)>1

Anyone know the correct query?

Answer 1

Based on your sample queries, it appears Event_Date_Time is Date/Time datatype. So I suggest you use TimeValue() to examine the time components as Date/Time type instead of converting them to String with Format().

SELECT Events.RFID, Count(*)
FROM Events
WHERE
        Events.Event_Date_Time BETWEEN #3/17/2015# And #3/24/2015#
    AND Events.In_Or_Out='In'
    AND TimeValue(Events.Event_Date_Time) > #08:40:00#
GROUP BY Events.RFID
HAVING COUNT(*) > 1

If you want to also see the names or other information from your Student table, join the GROUP BY query back to Student on RFID.