• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

access query counting entries by date

Hi

I have a table which stores the datetime that a survey was completed, along with the surveyid... simple stuff...

my table is storing the date and time in the datetime field.

now I want to create a query that gives output like this:

01/01/2014 - 10
02/01/2014 - 21
03/01/2014 - 39
....


and so on.



SELECT pd_entries.time_stamp, Count(pd_entries.time_stamp) AS num, pd_entries.sessionid
FROM pd_entries
GROUP BY pd_entries.time_stamp, pd_entries.sessionid
HAVING (((pd_entries.sessionid)=5));

Open in new window



I am getting a count of 1 next to each date entry, because the time is different on each entry... doh..


time_stamp                      num      sessionid
29/04/2014 07:00:00      1      5
29/04/2014 07:44:17      1      5
29/04/2014 07:46:16      1      5
29/04/2014 07:56:25      1      5
29/04/2014 11:04:04      1      5
29/04/2014 11:05:03      1      5

is there any way for me to change the query to look just at the date?
0
cycledude
Asked:
cycledude
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try

SELECT pd_entries.time_stamp, Count(datevalue(pd_entries.time_stamp)) AS num, pd_entries.sessionid
FROM pd_entries
GROUP BY datevalue(pd_entries.time_stamp), pd_entries.sessionid
HAVING (((pd_entries.sessionid)=5));
0
 
cycledudeAuthor Commented:
Thanks, had a go, got the following message

"You tried to execute a query that does not include the specified 'time_stamp' as part of an aggregate function"
0
 
Rey Obrero (Capricorn1)Commented:
sorry

SELECT datevalue(pd_entries.time_stamp), Count(datevalue(pd_entries.time_stamp)) AS num, pd_entries.sessionid
FROM pd_entries
GROUP BY datevalue(pd_entries.time_stamp), pd_entries.sessionid
HAVING (((pd_entries.sessionid)=5));
0
 
cycledudeAuthor Commented:
thanks ;o)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now