CODEWITHSUNDEEP
sqlms-access
monkey5672
monkey5672

Reputation: 1

Count records per day using Access SQL

This should be a simple one. I have tables in Access that contain data in 5 minute intervals for multiple years. All I want to do is count how many records per day I have, so I can determine if I'm missing any data for any day. I should have 288 per day. My horrible SQL code below only counts for each 5 minute interval... any help greatly appreciated!

SELECT Huth12_import.DateTime, Count(Huth12_import.DateTime) AS CountOfDateTime
FROM Huth12_import
GROUP BY Huth12_import.DateTime
ORDER BY Huth12_import.DateTime;

Upvotes: 0

Views: 1618

Answers (1)

user2941651
user2941651

Reputation:

UPDATED:

Since your DateTime column contains date and time information stored as text, you could try to use DateValue function in Access (help on the function here), so the query could look even simpler:

SELECT 
    DateValue(Huth12_import.DateTime) AS DayVal, 
    Count(Huth12_import.DateTime) AS CountOfDateTime
FROM 
    Huth12_import
GROUP BY 
    DateValue(Huth12_import.DateTime)
ORDER BY 
    DayVal;

(DateVal functions should only return the date part of the stored information).

Upvotes: 2

Related Questions