Forum Discussion
Count number of records per date
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) ;
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) ;George_Hepworth Thanks George - much appreciated! It works like a charm!