If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average Records between date range
Hi all.
I am trying to average the total records by date range. I am getting an error message with my AVG expression. Here is a snap shot of the SQL I have so far. SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; Thanks for your help. |
#2
|
|||
|
|||
Average Records between date range
Should work. Try adding a CDate around the parameters, and [ ] around your
field Date: SELECT a.[Account #], COUNT(b.ReturnReasonCode) AS CountOfReturnReasonCode, AVG(CountOfReturnReasonCode) AS Expr1 FROM [Incoming Return Item Detail] AS b INNER JOIN [RCC Customer List] AS a ON b.DepositingAccount = a.[Account #] WHERE b.[Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY a.[Account #]; Vanderghast, Access MVP "Love Buzz" wrote in message ... Hi all. I am trying to average the total records by date range. I am getting an error message with my AVG expression. Here is a snap shot of the SQL I have so far. SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; Thanks for your help. |
#3
|
|||
|
|||
Average Records between date range
Thank you for responding.
I still get the same 'Subqueries cannot be used in the expression (Avg([CountOfReturnReasonCode])) Here is what my SQL looks like after I made the changes you recommended: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE [Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY [RCC Customer List].[Account #]; "Michel Walsh" wrote: Should work. Try adding a CDate around the parameters, and [ ] around your field Date: SELECT a.[Account #], COUNT(b.ReturnReasonCode) AS CountOfReturnReasonCode, AVG(CountOfReturnReasonCode) AS Expr1 FROM [Incoming Return Item Detail] AS b INNER JOIN [RCC Customer List] AS a ON b.DepositingAccount = a.[Account #] WHERE b.[Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY a.[Account #]; Vanderghast, Access MVP "Love Buzz" wrote in message ... Hi all. I am trying to average the total records by date range. I am getting an error message with my AVG expression. Here is a snap shot of the SQL I have so far. SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; Thanks for your help. |
#4
|
|||
|
|||
Average Records between date range
Ah, I see. In
SELECT ... COUNT([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, AVG([CountOfReturnReasonCode]) AS Expr1 CountOfReturnReasonCode in AVG refers to a field from one of the table/query involved in the FROM clause, or it refers to the alias you just defined for the COUNT? *IF* it is the first case, use a different alias (different than any existing fields) *IF* it is the second case, that does make little sense as it is... you are averaging a single ONE value, at most, so it will be like repeating the single value, and definitively, SQL does not allow you to use: AVG(COUNT( ... ) ) , neither SUM(MAX( ... )) or whatever such expressions. If you think in term of SUM(SUM( ) ) then you see that, to make sense, the outer SUM has to operate on different GROUP than that first SUM did. If that is what you want, then make a saved query with the first aggregate (keep the COUNT, remove the AVG, in your case), then, make a second query that will call use AVG on the COUNT-ed field returned by the first query you just made. Vanderghast, Access MVP "Love Buzz" wrote in message ... Thank you for responding. I still get the same 'Subqueries cannot be used in the expression (Avg([CountOfReturnReasonCode])) Here is what my SQL looks like after I made the changes you recommended: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE [Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY [RCC Customer List].[Account #]; "Michel Walsh" wrote: Should work. Try adding a CDate around the parameters, and [ ] around your field Date: SELECT a.[Account #], COUNT(b.ReturnReasonCode) AS CountOfReturnReasonCode, AVG(CountOfReturnReasonCode) AS Expr1 FROM [Incoming Return Item Detail] AS b INNER JOIN [RCC Customer List] AS a ON b.DepositingAccount = a.[Account #] WHERE b.[Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY a.[Account #]; Vanderghast, Access MVP "Love Buzz" wrote in message ... Hi all. I am trying to average the total records by date range. I am getting an error message with my AVG expression. Here is a snap shot of the SQL I have so far. SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; Thanks for your help. |
#5
|
|||
|
|||
Average Records between date range
Note that you may want:
SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg(Incoming Return Item Detail].ReturnReasonCode]) AS Expr1 FROM... so, for a given group, you get and the COUNT, and the AVERAGE. Vanderghast, Access MVP "Love Buzz" wrote in message ... Thank you for responding. I still get the same 'Subqueries cannot be used in the expression (Avg([CountOfReturnReasonCode])) Here is what my SQL looks like after I made the changes you recommended: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE [Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY [RCC Customer List].[Account #]; "Michel Walsh" wrote: Should work. Try adding a CDate around the parameters, and [ ] around your field Date: SELECT a.[Account #], COUNT(b.ReturnReasonCode) AS CountOfReturnReasonCode, AVG(CountOfReturnReasonCode) AS Expr1 FROM [Incoming Return Item Detail] AS b INNER JOIN [RCC Customer List] AS a ON b.DepositingAccount = a.[Account #] WHERE b.[Date] Between CDate([Start Date]) And CDate([End Date]) GROUP BY a.[Account #]; Vanderghast, Access MVP "Love Buzz" wrote in message ... Hi all. I am trying to average the total records by date range. I am getting an error message with my AVG expression. Here is a snap shot of the SQL I have so far. SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg([CountOfReturnReasonCode]) AS Expr1 FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; Thanks for your help. |
#6
|
|||
|
|||
Average Records between date range
Suspect the error message you're getting is:
"Subqueries cannot be used in the expression Avg([CountOfReturnReasonCode])" Removing the Avg() statement and running the query, you'd get a record for each each [Account #], [ReturnReasonCode] with a count for that particular combination. The question is now what you want to see in the way of an average? Little more detail would be helpful. Bob Michel Walsh wrote: Note that you may want: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg(Incoming Return Item Detail].ReturnReasonCode]) AS Expr1 FROM... so, for a given group, you get and the COUNT, and the AVERAGE. Vanderghast, Access MVP Thank you for responding. [quoted text clipped - 47 lines] Thanks for your help. -- Message posted via http://www.accessmonster.com |
#7
|
|||
|
|||
Average Records between date range
Yes, the following SQL provides me with a count of records, for a date range
selected for the ReturnReasonCode: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; My goal is to compare the count of ReturnReasonCode to a daily average of the count. For example, I want to compare 05/01/2008 volume to the average daily count of 04/20 - 04/30/2008. Hope that made more sense. Thanks for your help. "raskew via AccessMonster.com" wrote: Suspect the error message you're getting is: "Subqueries cannot be used in the expression Avg([CountOfReturnReasonCode])" Removing the Avg() statement and running the query, you'd get a record for each each [Account #], [ReturnReasonCode] with a count for that particular combination. The question is now what you want to see in the way of an average? Little more detail would be helpful. Bob Michel Walsh wrote: Note that you may want: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg(Incoming Return Item Detail].ReturnReasonCode]) AS Expr1 FROM... so, for a given group, you get and the COUNT, and the AVERAGE. Vanderghast, Access MVP Thank you for responding. [quoted text clipped - 47 lines] Thanks for your help. -- Message posted via http://www.accessmonster.com |
#8
|
|||
|
|||
Average Records between date range
Then you have to first count by day, next, average these counts for 10 days:
SELECT COUNT(*) AS theseCounts, DateValue(DateTimeValue) AS theseDays FROM somewhere GROUP BY DateValue(DateTimeFieldValueHere) ) As a saved query q1, then SELECT AVG(theseCounts) FROM q1 WHERE theseDays BETWEEN date1 AND date2 will give the average of the counts. The first aggregation, COUNT, occurs BY DAY, while the second aggregation, AVG, occurs (implicitly) over one range. Vanderghast, Access MVP "Love Buzz" wrote in message ... Yes, the following SQL provides me with a count of records, for a date range selected for the ReturnReasonCode: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; My goal is to compare the count of ReturnReasonCode to a daily average of the count. For example, I want to compare 05/01/2008 volume to the average daily count of 04/20 - 04/30/2008. Hope that made more sense. Thanks for your help. "raskew via AccessMonster.com" wrote: Suspect the error message you're getting is: "Subqueries cannot be used in the expression Avg([CountOfReturnReasonCode])" Removing the Avg() statement and running the query, you'd get a record for each each [Account #], [ReturnReasonCode] with a count for that particular combination. The question is now what you want to see in the way of an average? Little more detail would be helpful. Bob Michel Walsh wrote: Note that you may want: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, Avg(Incoming Return Item Detail].ReturnReasonCode]) AS Expr1 FROM... so, for a given group, you get and the COUNT, and the AVERAGE. Vanderghast, Access MVP Thank you for responding. [quoted text clipped - 47 lines] Thanks for your help. -- Message posted via http://www.accessmonster.com |
#9
|
|||
|
|||
Average Records between date range
Try this:
************************************************** *********************** PARAMETERS StartD DateTime , EndD DateTime; SELECT [Incoming Return Item Detail].ReturnReasonCode , Count(([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode , [CountOfReturnReasonCode]/DateDiff("d",[StartD],[EndD]) AS DailyAverage FROM [Incoming Return Item Detail] WHERE (([Incoming Return Item Detail].Date)) Between [startd] AND [endD])) GROUP BY [Incoming Return Item Detail].ReturnReasonCode; This could be off by a bracket or parenthesis, since I couldn't test it. Here's what it's based on--tested and working--based on Northwind's Orders table, renamed Orders3. ************************************************** *********************** PARAMETERS StartD DateTime , EndD DateTime; SELECT Orders3.ShipVia , Count(Orders3.ShipVia) AS CountOfShipViab , [CountOfShipVia]/DateDiff("d",[StartD],[EndD]) AS DailyAverage FROM Orders3 WHERE (((Orders3.ShippedDate) Between [startd] AND [endD])) GROUP BY Orders3.ShipVia; ************************************************** *********************** I saw no need for your other table. You should rename your Date field, since Date is an Access reserved word, and could at a later date cause problems. HTH - Bo Love Buzz wrote: Yes, the following SQL provides me with a count of records, for a date range selected for the ReturnReasonCode: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; My goal is to compare the count of ReturnReasonCode to a daily average of the count. For example, I want to compare 05/01/2008 volume to the average daily count of 04/20 - 04/30/2008. Hope that made more sense. Thanks for your help. Suspect the error message you're getting is: "Subqueries cannot be used in the expression Avg([CountOfReturnReasonCode])" [quoted text clipped - 27 lines] Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200806/1 |
#10
|
|||
|
|||
Average Records between date range
First of all, thank you both for your help. I modified your SQL below a bit,
but didn't change the expression. PARAMETERS StartD DateTime, EndD DateTime; SELECT [Incoming Return Item Detail].DepositingAccount, Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode, [CountOfReturnReasonCode]/DateDiff("d",[StartD],[EndD]) AS DailyAverage FROM [Incoming Return Item Detail] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [Incoming Return Item Detail].DepositingAccount; What I am getting though is the count of the return items, and a % of count of returns, rather than the average for the date range specified. I have Jan 1st through May 31st in the table. I want to be able to count one days worth (based on the start and end date) and compare it to an average of previous activity (based on the start and end date). Any ideas? Thanks again for your help. "raskew via AccessMonster.com" wrote: Try this: ************************************************** *********************** PARAMETERS StartD DateTime , EndD DateTime; SELECT [Incoming Return Item Detail].ReturnReasonCode , Count(([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode , [CountOfReturnReasonCode]/DateDiff("d",[StartD],[EndD]) AS DailyAverage FROM [Incoming Return Item Detail] WHERE (([Incoming Return Item Detail].Date)) Between [startd] AND [endD])) GROUP BY [Incoming Return Item Detail].ReturnReasonCode; This could be off by a bracket or parenthesis, since I couldn't test it. Here's what it's based on--tested and working--based on Northwind's Orders table, renamed Orders3. ************************************************** *********************** PARAMETERS StartD DateTime , EndD DateTime; SELECT Orders3.ShipVia , Count(Orders3.ShipVia) AS CountOfShipViab , [CountOfShipVia]/DateDiff("d",[StartD],[EndD]) AS DailyAverage FROM Orders3 WHERE (((Orders3.ShippedDate) Between [startd] AND [endD])) GROUP BY Orders3.ShipVia; ************************************************** *********************** I saw no need for your other table. You should rename your Date field, since Date is an Access reserved word, and could at a later date cause problems. HTH - Bo Love Buzz wrote: Yes, the following SQL provides me with a count of records, for a date range selected for the ReturnReasonCode: SELECT [RCC Customer List].[Account #], Count([Incoming Return Item Detail].ReturnReasonCode) AS CountOfReturnReasonCode FROM [Incoming Return Item Detail] INNER JOIN [RCC Customer List] ON [Incoming Return Item Detail].DepositingAccount = [RCC Customer List].[Account #] WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End Date])) GROUP BY [RCC Customer List].[Account #]; My goal is to compare the count of ReturnReasonCode to a daily average of the count. For example, I want to compare 05/01/2008 volume to the average daily count of 04/20 - 04/30/2008. Hope that made more sense. Thanks for your help. Suspect the error message you're getting is: "Subqueries cannot be used in the expression Avg([CountOfReturnReasonCode])" [quoted text clipped - 27 lines] Thanks for your help. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200806/1 |
|
Thread Tools | |
Display Modes | |
|
|