Prompting for date range twice

[Love Buzz]

Hi all.

I have a query that I am creating to compare volumes to previous day
averages. What I am trying to do is have the query prompt me twice for a
date range. The first prompt will be a specific date and the second will be
a range of days. From those results I want to compare the daily volume to
the average of the range of days I have selected.

Here is what I have now and obviously I am not getting the result I am
looking for. Any ideas? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, Count([Incoming
Return Item Detail].ReturnReason) AS CountOfReturnReason,
[CountOfReturnReason] AS Expr1
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]) AND (([Incoming Return Item Detail].Date) Between [Start Date] And
[End Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount;

 

[KARL DEWEY]

Try this --
SELECT [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].DepositingAccount, [Incoming Return Item Detail].[ReturnReason],
Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE [Incoming Return Item Detail].[Date] Between [Start Date] And [End
Date]
GROUP BY [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].[DepositingAccount], [Incoming Return Item Detail].[ReturnReason];

 

[Love Buzz]

Thank you Karl for responding. I receive a Syntax Error in JOIN Operation
when I run that SQL.

Try this --
SELECT [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].DepositingAccount, [Incoming Return Item Detail].[ReturnReason],
Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE [Incoming Return Item Detail].[Date] Between [Start Date] And [End
Date]
GROUP BY [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].[DepositingAccount], [Incoming Return Item Detail].[ReturnReason];

--
KARL DEWEY
Build a little - Test a little

Hi all.

I have a query that I am creating to compare volumes to previous day
averages. What I am trying to do is have the query prompt me twice for a
date range. The first prompt will be a specific date and the second will be
a range of days. From those results I want to compare the daily volume to
the average of the range of days I have selected.

Here is what I have now and obviously I am not getting the result I am
looking for. Any ideas? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, Count([Incoming
Return Item Detail].ReturnReason) AS CountOfReturnReason,
[CountOfReturnReason] AS Expr1
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]) AND (([Incoming Return Item Detail].Date) Between [Start Date] And
[End Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount;

 

[KARL DEWEY]

I just tried it without an error.

Check for returns inserted in the copy/paste/posting process. There should
only be a return before the FROM, WHERE, and BROUP BY.
--
KARL DEWEY
Build a little - Test a little

Thank you Karl for responding. I receive a Syntax Error in JOIN Operation
when I run that SQL.

Try this --
SELECT [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].DepositingAccount, [Incoming Return Item Detail].[ReturnReason],
Count([Incoming Return Item Detail].ReturnReason) AS CountOfReturnReason
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE [Incoming Return Item Detail].[Date] Between [Start Date] And [End
Date]
GROUP BY [Incoming Return Item Detail].[Date], [Incoming Return Item
Detail].[DepositingAccount], [Incoming Return Item Detail].[ReturnReason];

--
KARL DEWEY
Build a little - Test a little

Hi all.

I have a query that I am creating to compare volumes to previous day
averages. What I am trying to do is have the query prompt me twice for a
date range. The first prompt will be a specific date and the second will be
a range of days. From those results I want to compare the daily volume to
the average of the range of days I have selected.

Here is what I have now and obviously I am not getting the result I am
looking for. Any ideas? Thanks for your help.

SELECT [Incoming Return Item Detail].DepositingAccount, Count([Incoming
Return Item Detail].ReturnReason) AS CountOfReturnReason,
[CountOfReturnReason] AS Expr1
FROM [RCC Customer List] INNER JOIN [Incoming Return Item Detail] ON [RCC
Customer List].[Account #] = [Incoming Return Item Detail].DepositingAccount
WHERE ((([Incoming Return Item Detail].Date) Between [Start Date] And [End
Date]) AND (([Incoming Return Item Detail].Date) Between [Start Date] And
[End Date]))
GROUP BY [Incoming Return Item Detail].DepositingAccount;