Forum Discussion
Count number of records per date
Hi
I have a query in my MS Access DB, approximately 15.000 rows, with a date (date and time per field) for each record.
I want to create a query, where I can show how many entries there are for each date. Basically, my DB is linked to my Outlook inbox, and I want to be able to see how many emails I've received for each date.
Is that possible?
Thanks
Jens
My error in throwing that out there untested. The Group By needs to match the field selected as does the Order By. That is to say DateValue(Received)
Try this:
SELECT DateValue(Received) AS DateWithOutTime, Count(*) AS ItemsPerDay
FROM Mails
GROUP BY DateValue(Received)
ORDER BY DateValue(Received) ;
5 Replies
Yes, an aggregate query will do that. I would need the table and field names to create the exact query, but it should look like this pattern.
SELECT DateValue(YourDateFieldGoesHere) AS DateWithOutTime, Count(*) AS ItemsPerDay
FROM YourTableNameGoesHere
GROUP BY DateValue(YourDateFieldGoesHere)
ORDER BY YourDateFieldGoesHere;
George_Hepworth Thanks for your feedback.
I've chosen External Data > New datasource > From other sources > Outlook folder. I called that table Inbox.
Then I created a query I called MAILS From that, I've chosen the following columns:
Received, From, Sender name, Subject.
I then created a query from your suggestion - and it looks like this:
SELECT DateValue(Received) AS DateWithOutTime, Count(*) AS ItemsPerDay
FROM Mails
GROUP BY DateValue(Received)
ORDER BY Received;When I created the view, I went straight into DQL view, created the view, then saved the view (I called that vwPerDay), I switched to Datasheet view, and an error - a prompt appeared, saying:
"Your query does not include the specified expression 'Received' as part of an aggregate expression"
Clearly I'm missing the part where I create an "aggregate view", but I thought if you went straight into SQL view that would solve all problems.
Any help is appreciated.
Thanks.
My error in throwing that out there untested. The Group By needs to match the field selected as does the Order By. That is to say DateValue(Received)
Try this:
SELECT DateValue(Received) AS DateWithOutTime, Count(*) AS ItemsPerDay
FROM Mails
GROUP BY DateValue(Received)
ORDER BY DateValue(Received) ;
