logins by time of day

[paky]

Hi,

I have a table LOGINS that has field LOGIN_DATE, data format in this field is DD.MM.YYYY HH.MM.SS.
I would need a query that counts logins by time of day, result shoud be something like this:

Week number 42
week day 00-01 GMT 01-02 GMT 02-03 GMT 03-04 GMT 04-05 GMT 05-06 GMT 06-07 GMT etc..
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
I am little lost with this and I would be really grateful if someone could help me with this.

 

[ColinEssex]

If you're trying to count the Logins, will you also need the logout time as well? For example, if 3 people log in between 09:00 and 10:00 but one is still logged in between 10:00 and 11:00 - will you need to show this user as still being logged in between 10:00 and 11:00?

Col

 

[Mile-O]

Just store the date and time (as one field) that the person logs in. The rest can be calculated from there.

 

[paky]

logins

If you're trying to count the Logins, will you also need the logout time as well? For example, if 3 people log in between 09:00 and 10:00 but one is still logged in between 10:00 and 11:00 - will you need to show this user as still being logged in between 10:00 and 11:00?

Col

Hi,
No, in my system, when someone logs in, username, login time, etc. is written to log.
I Just need to count e.g. how many logins is made today between 10:00-11:00, 11:00-12:00....and somhow get the result in to readable format

 

[KKilfoil]

In your query, use the Format command to create new fields like:

LoginDayHour: Format([YourDateTimeField],"YYYY-MM-DD HH")
This will generate a unique value for each hour of each day

LoginHour: Format([YourDateTimeField],"HH")
This will generate a unique value for each hour of the day, but not differentiate between days.

You should now be able to use 'group by' in a summary query to get what you want, or create a suitable crosstab.