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.....
|
|
0
|
|
|
|
Reply
|
 Utf
|
4/24/2010 1:51:01 PM |
|
On Sat, 24 Apr 2010 06:51:01 -0700, StuJol
<StuJol@discussions.microsoft.com> wrote:
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.....
|
|
|
0
|
|
|
|
Reply
|
Tom
|
4/24/2010 2:02:57 PM
|
|
On Sat, 24 Apr 2010 06:51:01 -0700, StuJol <StuJol@discussions.microsoft.com>
wrote:
>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
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.
--
John W. Vinson [MVP]
|
|
|
0
|
|
|
|
Reply
|
John
|
4/24/2010 5:53:09 PM
|
|
thanks tom, works great
"Tom van Stiphout" wrote:
> On Sat, 24 Apr 2010 06:51:01 -0700, StuJol
> <StuJol@discussions.microsoft.com> wrote:
>
> 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.....
> .
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
4/24/2010 6:23:01 PM
|
|
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
John W. Vinson wrote:
> On Sat, 24 Apr 2010 06:51:01 -0700, StuJol <StuJol@discussions.microsoft.com>
> wrote:
>
>> 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
>
> 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.
|
|
|
0
|
|
|
|
Reply
|
John
|
4/24/2010 6:24:54 PM
|
|
|
4 Replies
427 Views
(page loaded in 0.109 seconds)
Similiar Articles: Counts number of entries per day in query - microsoft.public ...using access 2007, i have a query that shows Event Type State Date/Time* DayCount ALARM ACT/UNACK ... Count number of entries each day - microsoft.public.access.queries ...Answer : Counts number of entries per day in query Counts number of entries per day in query - answer - using access 2007, i have a query that shows Event Type State Date ... Count total records by date - microsoft.public.access ...Count number of entries each day - microsoft.public.access.queries ... Counts number of entries per day in query - microsoft.public ... Count total records by date ... number of emails per day - microsoft.public.outlook.general ...Count number of entries each day - microsoft.public.access.queries ... Answer : Counts number of entries per day in query Counts number of entries per day in query ... last entry per record query.. - microsoft.public.access.queries ...I expect you need a simple totals query like: SELECT Entry_Date ... Counts number of entries per day in query - microsoft.public ... Count total records by date ... using count in a query - microsoft.public.accessCounts number of entries per day in query - microsoft.public ... using access 2007, i have a query that shows Event Type State Date/Time* DayCount ALARM ACT/UNACK ... Unique entries based on condition - count distinct - microsoft ...Counts number of entries per day in query - microsoft.public ... Unique entries based on condition - count distinct - microsoft ... using count in a query - microsoft ... Query returns wrong number - microsoft.public.access.queries ...Counts number of entries per day in query - microsoft.public ..... of the date as it shows seconds but everything i trie seems to return the wrong ... Filter asterisks from a field - microsoft.public.access.queries ...Counts number of entries per day in query - microsoft.public ... Filter asterisks from a field - microsoft.public.access.queries ... Counts number of entries per day in ... Count distinct lines in a report - microsoft.public.access.reports ...Counts number of entries per day in query - microsoft.public ... How do I display 1 line per Invoice Summary w ... Unique entries based on condition - count distinct ... Answer : Counts number of entries per day in queryCounts number of entries per day in query - answer - using access 2007, i have a query that shows Event Type State Date/Time ... Counts number of entries per day in query - microsoft.public ...using access 2007, i have a query that shows Event Type State Date/Time* DayCount ALARM ACT/UNACK ... Count number of entries per day: SQL 2008Keywords: Count, number, of, entries, per, day. count entries in a report; Count entries; Counting entries within a range; SQL Query to Count Entries Per Colum… PowerCram: SQL Server count items per day or per hourHere are a couple of quick SQL statements to return counts based on entries or items or transactions per day or per hour. You can tailor as desired. Google Answers: Access query to count number of activities per daySubject: Access query to count number of activities per day Category: Computers > Software Asked by: lochness-ga List Price: $5.00: Posted: 20 Aug 2002 15 ... 7/30/2012 8:03:37 AM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 