Home Ask Login Register

Developers Planet

Your answer is one click away!

Esquireknox February 2016

Removing seconds from the time field in MS Access without updating the table

Good day

I have a table that has users and time-stamps. I need to calculate how many users used a system at any given time. This will help to see peak hours. The problem is that seconds are separating my count(s). For instance, I'll have 4 of the same time-stamps[11:45,11:45, 11:45] because for the different seconds. Is there a way to remove them without updating the table. All the formatting functions don't work! Thanks in advance

The code is as follows;

SELECT distinct mid(Format (u.time), 1,5) as Time,
(select count(u1.student_id) from uselog u1 where u1.time = u.time) as users FROM uselog u


Gustav February 2016

Just round off the seconds:

Select Distinct
    TimeSerial(Hour(u.time), Minute(u.time), 0) As [time],
    (Select Count("*") From uselog As u1 Where TimeSerial(Hour(u1.time), Minute(u1.time), 0) = u.time) As users 
    uselog As u

Post Status

Asked in February 2016
Viewed 2,347 times
Voted 6
Answered 1 times


Leave an answer

Quote of the day: live life