CODEWITHSUNDEEP
sqlms-accessrdbms
Michael
Michael

Reputation: 197

Finding multiple instances in a table

I have two tables in my database:

Events:

enter image description here

Student:

enter image description here

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?

Upvotes: 1

Views: 145

Answers (2)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 1

Marcelo
Marcelo

Reputation: 429

SELECT e.RFID, s.FORENAME, s.SURENAME, count(e.*) AS LATE, 
FROM Events e
INNER JOIN students s
on s.RFID = e.RFID
WHERE e.Event_Date_Time BETWEEN '3/24/2015' And '3/17/2015' 
    AND e.In_Or_Out='In' 
    AND time(e.Event_Date_Time)>'8:40:00'
GROUP BY e.RFID, s.FORENAME, s.SURENAME
HAVING COUNT(e.*)>1

Upvotes: 0

Related Questions