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.

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.

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.

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.

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.

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

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

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

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

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