Prev: Help with 3 scenerio query
Next: Query to conditionally handle duplicates
From: Whitney on 21 Apr 2010 16:28
I have a tracking table (tbl_Tracker) that now contains date and time in one
field [Date] and agent name in another field [SLR]. I would like run a query
to do a count of number of records by agent by day. How do I set up the query
to recognize only the date and not the time.

I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
record for each date and specific time instead of grouping the whole day.

Then I need to build another query that looks at another table
(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
date range and compare that to the above query for the number of tracks
[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].

Ex.
Date Name Handled Tracked
4/19/2010 Mary 20 15
4/19/2010 Bob 35 34
4/20/2010 Mary 29 29
4/20/2010 Bob 45 40
From: Marshall Barton on 21 Apr 2010 17:04
Whitney wrote:

>I have a tracking table (tbl_Tracker) that now contains date and time in one
>field [Date] and agent name in another field [SLR]. I would like run a query
>to do a count of number of records by agent by day. How do I set up the query
>to recognize only the date and not the time.
>
>I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
>record for each date and specific time instead of grouping the whole day.
>
>Then I need to build another query that looks at another table
>(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
>date range and compare that to the above query for the number of tracks
>[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
>
>Ex.
>Date Name Handled Tracked
>4/19/2010 Mary 20 15
>4/19/2010 Bob 35 34
>4/20/2010 Mary 29 29
>4/20/2010 Bob 45 40


SELECT DateValue(datefield) As TheDate,
person, Count(*) as Tracked
FROM tbl_Tracker
GROUP BY DateValue(datefield), person

Not sure I follow what the other query is supposed to be
doing.

--
Marsh
MVP [MS Access]
From: Whitney on 23 Apr 2010 14:32
I don't understand your SQL code..
SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
FROM tbl_Tracker
GROUP BY DateValue(datefield), person

This is what I have now:
SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;



"Marshall Barton" wrote:

> Whitney wrote:
>
> >I have a tracking table (tbl_Tracker) that now contains date and time in one
> >field [Date] and agent name in another field [SLR]. I would like run a query
> >to do a count of number of records by agent by day. How do I set up the query
> >to recognize only the date and not the time.
> >
> >I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
> >record for each date and specific time instead of grouping the whole day.
> >
> >Then I need to build another query that looks at another table
> >(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
> >date range and compare that to the above query for the number of tracks
> >[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
> >
> >Ex.
> >Date Name Handled Tracked
> >4/19/2010 Mary 20 15
> >4/19/2010 Bob 35 34
> >4/20/2010 Mary 29 29
> >4/20/2010 Bob 45 40
>
>
> SELECT DateValue(datefield) As TheDate,
> person, Count(*) as Tracked
> FROM tbl_Tracker
> GROUP BY DateValue(datefield), person
>
> Not sure I follow what the other query is supposed to be
> doing.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: John Spencer on 23 Apr 2010 15:03
SELECT Datevalue(tbl_Tracker.Date) as JustTheDate, tbl_Tracker.SLR,
Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR;

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Whitney wrote:
> I don't understand your SQL code..
> SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
> FROM tbl_Tracker
> GROUP BY DateValue(datefield), person
>
> This is what I have now:
> SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
> FROM tbl_Tracker
> GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
>
>
>
> "Marshall Barton" wrote:
>
>> Whitney wrote:
>>
>>> I have a tracking table (tbl_Tracker) that now contains date and time in one
>>> field [Date] and agent name in another field [SLR]. I would like run a query
>>> to do a count of number of records by agent by day. How do I set up the query
>>> to recognize only the date and not the time.
>>>
>>> I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
>>> record for each date and specific time instead of grouping the whole day.
>>>
>>> Then I need to build another query that looks at another table
>>> (tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
>>> date range and compare that to the above query for the number of tracks
>>> [Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
>>>
>>> Ex.
>>> Date Name Handled Tracked
>>> 4/19/2010 Mary 20 15
>>> 4/19/2010 Bob 35 34
>>> 4/20/2010 Mary 29 29
>>> 4/20/2010 Bob 45 40
>>
>> SELECT DateValue(datefield) As TheDate,
>> person, Count(*) as Tracked
>> FROM tbl_Tracker
>> GROUP BY DateValue(datefield), person
>>
>> Not sure I follow what the other query is supposed to be
>> doing.
>>
>> --
>> Marsh
>> MVP [MS Access]
>> .
>>
From: Marshall Barton on 23 Apr 2010 15:25
I wasn't sure what fields you were really using so I just
used place holder names. Here's my attempt to translate
your query to waht I was saying:

SELECT DateValue(tbl_Tracker.Date),
tbl_Tracker.SLR,
Count(*) AS CountOfSLR
FROM tbl_Tracker
GROUP BY DateValue(tbl_Tracker.Date), tbl_Tracker.SLR

I used Count(*) instead of Count(tbl_Tracker.SLR) because *
counts all the records even if the SLR field contains Null
in some records and because it is a lot faster than counting
the non null values in a field.
--
Marsh
MVP [MS Access]


Whitney wrote:
>I don't understand your SQL code..
>SELECT DateValue(datefield) As TheDate, person, Count(*) as Tracked
>FROM tbl_Tracker
>GROUP BY DateValue(datefield), person
>
>This is what I have now:
>SELECT tbl_Tracker.Date, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
>FROM tbl_Tracker
>GROUP BY tbl_Tracker.Date, tbl_Tracker.SLR;
>
>
>"Marshall Barton" wrote:
>> Whitney wrote:
>> >I have a tracking table (tbl_Tracker) that now contains date and time in one
>> >field [Date] and agent name in another field [SLR]. I would like run a query
>> >to do a count of number of records by agent by day. How do I set up the query
>> >to recognize only the date and not the time.
>> >
>> >I have Group By [SLR], Count [SLR] and Group By [Date], however it shows a
>> >record for each date and specific time instead of grouping the whole day.
>> >
>> >Then I need to build another query that looks at another table
>> >(tbl_SL_Call_Stats) for number of calls handled [Handled] by day [Date] for a
>> >date range and compare that to the above query for the number of tracks
>> >[Tracked:Nz ([CountOfSLR]),0) by day for each agent [SLR].
>> >
>> >Ex.
>> >Date Name Handled Tracked
>> >4/19/2010 Mary 20 15
>> >4/19/2010 Bob 35 34
>> >4/20/2010 Mary 29 29
>> >4/20/2010 Bob 45 40
>>
>>
>> SELECT DateValue(datefield) As TheDate,
>> person, Count(*) as Tracked
>> FROM tbl_Tracker
>> GROUP BY DateValue(datefield), person
>>
>> Not sure I follow what the other query is supposed to be
>> doing.
 |  Next  |  Last
Pages: 1 2
Prev: Help with 3 scenerio query
Next: Query to conditionally handle duplicates