How to query time range and day of the week?

[freeskier]

I have table Session (ID, StartTime, EndTime, Date)

How do I query this data to show a count during certain times?

For example:

StartTime Between Count
8:00-8:59 2
9:00-9:59 3
10:00-10:59 0

I want to do the same for days of the week (M, T, W, etc.)

Sorry if this has been asked before but I can't find an answer that gives me exactly what I want.
this seems to be close to what I need, but I am not thrilled by the suggestion of creating dummy tables for Start and End

http://www.thescripts.com/forum/thread769489-date+sql.html

Thanks in advance for the help!

[Denburt]

I have table Session (ID, StartTime, EndTime, Date)

How do I query this data to show a count during certain times?

For example:

StartTime Between Count
8:00-8:59 2
9:00-9:59 3
10:00-10:59 0

I want to do the same for days of the week (M, T, W, etc.)

Sorry if this has been asked before but I can't find an answer that gives me exactly what I want.
this seems to be close to what I need, but I am not thrilled by the suggestion of creating dummy tables for Start and End

http://www.thescripts.com/forum/thread769489-date+sql.html

Thanks in advance for the help!

Just by using certain formats you can easily obtain this. Although I would suggest renaming your "Date" to something like dated simply because date() is a function in MS Access so it is considered a reserved word and shouldn't be used except for it's intended purpose (as a function to call todays date).

Select Format([StartTime],"Short Time"), Format([EndTime],"Short Time") , Dated, count(Dated) from Session GROUP BY Format([StartTime],"Short Time"), Format([EndTime],"Short Time"), Date ORDER BY Count([Dated) DESC;

For days of the week Format([Dated],"d") or Format([Dated],"dddd")

If you type the word "format" in the vba window then highlight the word and press F1 you can see the help file on that item.

[freeskier]

Just by using certain formats you can easily obtain this. Although I would suggest renaming your "Date" to something like dated simply because date() is a function in MS Access so it is considered a reserved word and shouldn't be used except for it's intended purpose (as a function to call todays date).

Select Format([StartTime],"Short Time"), Format([EndTime],"Short Time") , Dated, count(Dated) from Session GROUP BY Format([StartTime],"Short Time"), Format([EndTime],"Short Time"), Date ORDER BY Count([Dated) DESC;

For days of the week Format([Dated],"d") or Format([Dated],"dddd")

If you type the word "format" in the vba window then highlight the word and press F1 you can see the help file on that item.

This gives me "You tried to execute a query that does not include the specified expression "Dated" as part of an aggregate function"

[freeskier]

This gives me "You tried to execute a query that does not include the specified expression "Dated" as part of an aggregate function"

sorry it was a typo on my part

[freeskier]

Just by using certain formats you can easily obtain this. Although I would suggest renaming your "Date" to something like dated simply because date() is a function in MS Access so it is considered a reserved word and shouldn't be used except for it's intended purpose (as a function to call todays date).

Select Format([StartTime],"Short Time"), Format([EndTime],"Short Time") , Dated, count(Dated) from Session GROUP BY Format([StartTime],"Short Time"), Format([EndTime],"Short Time"), Date ORDER BY Count([Dated) DESC;

For days of the week Format([Dated],"d") or Format([Dated],"dddd")

If you type the word "format" in the vba window then highlight the word and press F1 you can see the help file on that item.

Thank you, this works very nicely for days of the week.

However, StartTime does not group so easily. For example, a StartTime may begin at 10:15 or 10:07. I would like to get a count of all StartTimes between the times of 10:00-11:00

[Denburt]

h Display the hour as a number without leading zeros (0 – 23).
Hh Display the hour as a number with leading zeros (00 – 23).

Hope this helps let us know how things go.