Count by short date and group by agent name

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 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]

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

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

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.

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.

"Marshall Barton" wrote:

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

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.
"John Spencer" wrote:

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

.

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]


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

"Marshall Barton" wrote:
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;


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

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

Whitney wrote:
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.
"John Spencer" wrote:

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

.

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

"Marshall Barton" wrote:

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]


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

"Marshall Barton" wrote:
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;


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


.