Count by short date and group by agent name

[Whitney]

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

 

[Whitney]

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;

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.

 

[John Spencer]

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

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;

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.

 

[Whitney]

I created a new blank query and pasted your SQL code. I'm getting data type
mismatch in criteria expression. Not sure what the issue is.

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

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;

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.

.

 

[Whitney]

I created a new blank query and pasted your SQL code. I'm getting data type
mismatch in criteria expression. Not sure what the issue is.

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]

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;

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.

.

 

[John Spencer]

I would guess that tbl_Tracker.Date is a date field that is sometimes null.
Or that tblTracker.Date is not a date field, but is a text field of some type
and either contains nulls or contains a value that cannot be interpreted as a
date. DateValue will generate errors if the field is Null or cannot be
interpreted as a date.

SELECT IIF(IsDate(Tbl_Tracker.Date),DateValue(tbl_Tracker.Date),Null) as
JustTheDate
, tbl_Tracker.SLR, Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
GROUP BY IIF(IsDate(Tbl_Tracker.Date),DateValue(tbl_Tracker.Date),Null),
tbl_Tracker.SLR;

OR alternative would be to use a where clause to exclude the nulls if the
field is a field of the type Date.

SELECT DateValue(tbl_Tracker.Date) as JustTheDate
, tbl_Tracker.SLR
, Count(tbl_Tracker.SLR) AS CountOfSLR
FROM tbl_Tracker
WHERE tblTracker.Date is not null
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

I created a new blank query and pasted your SQL code. I'm getting data type
mismatch in criteria expression. Not sure what the issue is.

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

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;



:

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.

.

 

[Whitney]

Like I said, I used your exact SQL code and pasted it into a blank query. I
did not specifiy any criteria.

Criteria? What criteria? You never mentioned any criteria.
The least you could do is post an explanation of whatever
the criteria is supposed to do along with a Copy/Paste of
the query with this criteria.
--
Marsh
MVP [MS Access]

I created a new blank query and pasted your SQL code. I'm getting data type
mismatch in criteria expression. Not sure what the issue is.

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.


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;


:
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.
.

.