Count by short date and group by agent name
in [Queries]
|
From: Whitney on 23 Apr 2010 19:40 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. > . >
From: Marshall Barton on 24 Apr 2010 09:59 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. >> . >>
From: John Spencer on 24 Apr 2010 13:31 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] >>>> . >>>> >> . >>
From: Whitney on 28 Apr 2010 12:38 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. > >> . > >> > > . >
From: Marshall Barton on 29 Apr 2010 11:33 Sorry, but I don't see how to relate that error to the query I posted. There is nothing in the query that can get a data type mismatch error, much less in a nonexistent criteria. At this point, I am lost and have to wonder where you pasted the SQL. Into a "blank query" in SQL view or some place in a new query's design view?? Because it is rather difficult to use text messages to explain things in the query design grid, we normally speak queries using SQL view. If you are using design view we are speaking different languages and coherent communications have gone south. -- Marsh MVP [MS Access] Whitney wrote: >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.
First
|
Prev
|
Pages: 1 2 Prev: Help with 3 scenerio query Next: Query to conditionally handle duplicates |