Counts number of entries per day in query

  • Thread starter Thread starter StuJol
  • Start date Start date
S

StuJol

using access 2007, i have a query that shows

Event Type State Date/Time* DayCount
ALARM ACT/UNACK 01/01/2010 00:13:38 1
ALARM ACT/UNACK 01/01/2010 00:13:49 1
ALARM ACT/UNACK 01/01/2010 00:17:21 1
ALARM ACT/UNACK 02/01/2010 00:13:38 1
ALARM ACT/UNACK 02/01/2010 00:13:28 1
ALARM ACT/UNACK 03/01/2010 00:13:38 1

my SQL is

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*], Count([Alarm Data
Filtered by Date].[Date/Time*]) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*]
HAVING ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

in trying to count the number of entries per day so for above data it would
return

Date/Time* DayCount
01/01/2010 3
02/01/2010 2
03/01/2010 1

i know its to do with the format of the date as it shows seconds but
everything i trie seems to return the wrong data

can anyone helps please.....
 
On Sat, 24 Apr 2010 06:51:01 -0700, StuJol

If you want to group by date without the time component, you can use
the DateSerial function:
(I renamed your Date/TIme field to DT for brevity)
select DateSerial(Year(DT), Month(DT), Day(DT)), count(*) as myCount
from myQuery
group by DateSerial(Year(DT), Month(DT), Day(DT))

-Tom.
Microsoft Access MVP
 
using access 2007, i have a query that shows

Event Type State Date/Time* DayCount
ALARM ACT/UNACK 01/01/2010 00:13:38 1
ALARM ACT/UNACK 01/01/2010 00:13:49 1
ALARM ACT/UNACK 01/01/2010 00:17:21 1
ALARM ACT/UNACK 02/01/2010 00:13:38 1
ALARM ACT/UNACK 02/01/2010 00:13:28 1
ALARM ACT/UNACK 03/01/2010 00:13:38 1

my SQL is

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*], Count([Alarm Data
Filtered by Date].[Date/Time*]) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*]
HAVING ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

in trying to count the number of entries per day so for above data it would
return
Click to expand...

You can use the DateValue() function to extract the date portion of the
date/time field, discarding the time. You should also move the criteria to a
WHERE clause (applied before all the grouping and totalling) rather than the
HAVING clause (which counts and totals all the data in your entire table and
then throws away most of it). Try

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, DateValue([Alarm Data Filtered by Date].[Date/Time*]) AS
DateOnly, Count(*) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, DateValue([Alarm Data Filtered by Date].[Date/Time*])
WHERE ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

I'd really suggest that you avoid using asterisks, slashes, blanks and other
punctuation in fieldnames.
 
thanks tom, works great

Tom van Stiphout said:
On Sat, 24 Apr 2010 06:51:01 -0700, StuJol

If you want to group by date without the time component, you can use
the DateSerial function:
(I renamed your Date/TIme field to DT for brevity)
select DateSerial(Year(DT), Month(DT), Day(DT)), count(*) as myCount
from myQuery
group by DateSerial(Year(DT), Month(DT), Day(DT))

-Tom.
Microsoft Access MVP


using access 2007, i have a query that shows

Event Type State Date/Time* DayCount
ALARM ACT/UNACK 01/01/2010 00:13:38 1
ALARM ACT/UNACK 01/01/2010 00:13:49 1
ALARM ACT/UNACK 01/01/2010 00:17:21 1
ALARM ACT/UNACK 02/01/2010 00:13:38 1
ALARM ACT/UNACK 02/01/2010 00:13:28 1
ALARM ACT/UNACK 03/01/2010 00:13:38 1

my SQL is

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*], Count([Alarm Data
Filtered by Date].[Date/Time*]) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*]
HAVING ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

in trying to count the number of entries per day so for above data it would
return

Date/Time* DayCount
01/01/2010 3
02/01/2010 2
03/01/2010 1

i know its to do with the format of the date as it shows seconds but
everything i trie seems to return the wrong data

can anyone helps please.....
Click to expand...
.
Click to expand...
 
Slight syntax error in that SQL that John posted.

SELECT [Alarm Data Filtered by Date].[Event Type]
, [Alarm Data Filtered by Date].State
, DateValue([Alarm Data Filtered by Date].[Date/Time*]) AS DateOnly
, Count(*) AS DayCount

FROM [Alarm Data Filtered by Date]

WHERE ((([Alarm Data Filtered by Date].[Event Type])="ALARM")
AND (([Alarm Data Filtered by Date].State)="ACT/UNACK"))

GROUP BY [Alarm Data Filtered by Date].[Event Type]
, [Alarm Data Filtered by Date].State
, DateValue([Alarm Data Filtered by Date].[Date/Time*])

The reason for moving the filter from the HAVING clause into a WHERE clause is
efficiency. It is much more efficient to filter records BEFORE doing any
aggregation than afterward. If you need to filter on the results of the
aggregation Avg,Sum,Count,etc. then you use the HAVING clause to filter after
the aggregation has been done.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
using access 2007, i have a query that shows

Event Type State Date/Time* DayCount
ALARM ACT/UNACK 01/01/2010 00:13:38 1
ALARM ACT/UNACK 01/01/2010 00:13:49 1
ALARM ACT/UNACK 01/01/2010 00:17:21 1
ALARM ACT/UNACK 02/01/2010 00:13:38 1
ALARM ACT/UNACK 02/01/2010 00:13:28 1
ALARM ACT/UNACK 03/01/2010 00:13:38 1

my SQL is

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*], Count([Alarm Data
Filtered by Date].[Date/Time*]) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, [Alarm Data Filtered by Date].[Date/Time*]
HAVING ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

in trying to count the number of entries per day so for above data it would
return
Click to expand...

You can use the DateValue() function to extract the date portion of the
date/time field, discarding the time. You should also move the criteria to a
WHERE clause (applied before all the grouping and totalling) rather than the
HAVING clause (which counts and totals all the data in your entire table and
then throws away most of it). Try

SELECT [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, DateValue([Alarm Data Filtered by Date].[Date/Time*]) AS
DateOnly, Count(*) AS DayCount
FROM [Alarm Data Filtered by Date]
GROUP BY [Alarm Data Filtered by Date].[Event Type], [Alarm Data Filtered by
Date].State, DateValue([Alarm Data Filtered by Date].[Date/Time*])
WHERE ((([Alarm Data Filtered by Date].[Event Type])="ALARM") AND (([Alarm
Data Filtered by Date].State)="ACT/UNACK"));

I'd really suggest that you avoid using asterisks, slashes, blanks and other
punctuation in fieldnames.
Click to expand...
 
Back
Top