Average Records between date range

That is why you need 2 queries (it is easier with 2 queries than with a
query and a subquery);


First query:


SELECT [Incoming Return Item Detail].DepositingAccount,
Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode,
[Incoming Return Item Detail].Date
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [Incoming Return
Item Detail].Date



say, you call it q1. Then, a second query:



SELECT DepositingAccount,
AVG(CountOfReturnReasonCode) AS average
FROM [Incoming Return Item Detail]
GROUP BY [Incoming Return Item Detail].DepositingAccount



return the desired average. You can use:

SELECT DepositingAccount,
AVG(CountOfReturnReasonCode) AS average,
MAX(date) AS latestDate
FROM [Incoming Return Item Detail]
GROUP BY [Incoming Return Item Detail].DepositingAccount


if you want to be able to associate it to the latest date of the interval.



Vanderghast, Access MVP


"Love Buzz" wrote in message
...
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

Thank you for responding and thank you for your patience.

When I try to run the average queuery I get to the sixth status square
(Running Query) and I have to go into Task Manager after about an hour to get
out of Access.

What is that an indication of? Is the query wrong, to complicated, or it
just needs a lot of time to go through all of the data?



"Michel Walsh" wrote:

That is why you need 2 queries (it is easier with 2 queries than with a
query and a subquery);


First query:


SELECT [Incoming Return Item Detail].DepositingAccount,
Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode,
[Incoming Return Item Detail].Date
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount, [Incoming Return
Item Detail].Date



say, you call it q1. Then, a second query:



SELECT DepositingAccount,
AVG(CountOfReturnReasonCode) AS average
FROM [Incoming Return Item Detail]
GROUP BY [Incoming Return Item Detail].DepositingAccount



return the desired average. You can use:

SELECT DepositingAccount,
AVG(CountOfReturnReasonCode) AS average,
MAX(date) AS latestDate
FROM [Incoming Return Item Detail]
GROUP BY [Incoming Return Item Detail].DepositingAccount


if you want to be able to associate it to the latest date of the interval.



Vanderghast, Access MVP


"Love Buzz" wrote in message
...
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

If

SELECT [Incoming Return Item Detail].DepositingAccount,
Count([Incoming Return Item Detail].ReturnReasonCode) AS
CountOfReturnReasonCode,
[Incoming Return Item Detail].Date
FROM [Incoming Return Item Detail]
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount,
[Incoming Return Item Detail].Date


takes too long to run, be sure you have an index on field DepositingAccount
and on field Date.

Sure, if [Incoming Return Item Detail] is a query, you can try to dump it
into a temporary table on which you will be able to define index onto its
fields (we cannot define index over select-queries).



Vanderghast, Access MVP