J. Doe J. Doe - 5 months ago 12
SQL Question

ms access sql code check

I'm working on some sql code, but not use to ms access so wanted to make sure it would behave like i think it should.

What I'm trying to do is get a count and the hour in military time for a set of records between june 1, 2015 to may 31, 2016. I want to know how many records I have for each hour. It doesn't matter what date the record took place on just the hour.

SELECT DatePart("h",[CentralTime]) AS Expr1, Count(DatePart("h",[CentralTime])) AS Expr2
FROM Master
WHERE (((Master.CentralTime) Between #6/1/2015# And #5/31/2016#))
GROUP BY DatePart("h",[CentralTime]);

Answer

Yes your query is fine and it will do what you want it to do. I tend to prefer count('x'), and you can lose some of the parenthesis but your query will work as is.

Or use this:

SELECT DatePart("h",[CentralTime]) AS Expr1, Count('x') AS Expr2
FROM ShipandGetCalls_Master
WHERE ShipandGetCalls_Master.CentralTime Between #6/1/2015# And #5/31/2016#
GROUP BY DatePart("h",[CentralTime]);

And you may want to take a look at the filter about whether you want to include the data on 5/31/16. You can change this field to a date/time to further specify exactly what you want to query to return.