Average Records between date range

[Love Buzz]

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.

 

[Michel Walsh]

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]

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

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

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.

 

[Michel Walsh]

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

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

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

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.

 

[Michel Walsh]

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.

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

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

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.

 

[raskew via AccessMonster.com]

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

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]

 

[Love Buzz]

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

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

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.

 

[Michel Walsh]

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

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

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

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.

 

[raskew via 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

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]

 

[Love Buzz]

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.

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

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.

 

[Michel Walsh]

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

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.

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

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.

 

[Love Buzz]

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?

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

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.

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.

 

[Michel Walsh]

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