Formula Problems

[Love Buzz]

Hi all.

I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

Any ideas? Thanks for your help.

 

[KARL DEWEY]

You can not have your cake and it it too. You can not sum a range of dates
and display the dates as it will sum for EACH date.
Try this ---
SELECT [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3, [StartDate], [EndDate]
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

 

[Love Buzz]

Thank you for responding. By taking the date out of the query of course it
works, but I want to be able to sum for a series of days. I have months
worth of data and sometimes the operator just wants to see a week or a month.

I want my cake and be able to eat it too!! lol

I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

Remove the Date field from the Select and Group By clauses

 

[KARL DEWEY]

but I want to be able to sum for a series of days.
You series of dates is selected by --
HAVING ((([December 4].Date) Between [StartDate] And [EndDate])

I added in the [StartDate] & [EndDate] in the SELECT statement so they
would be visible and available for a report.
The SQL I posted will not give you individual dates - so you eat the cake
still have the frosting.
--
KARL DEWEY
Build a little - Test a little

Thank you for responding. By taking the date out of the query of course it
works, but I want to be able to sum for a series of days. I have months
worth of data and sometimes the operator just wants to see a week or a month.

I want my cake and be able to eat it too!! lol

I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

Remove the Date field from the Select and Group By clauses

 

[Love Buzz]

Thank you Marshall and Karl.

Changing it to 'Where' did the trick. However, I have the query tied to a
report and would like to display the range I selected. How can that be done?

Thanks so much.

That's why you leave the date field in the Where clause.
You can not include it in the Select and Group By clauses or
you will get a total for each date in the range.

If you are working in the query designer, uncheck the Show
box and use Where in the Totals row.
--
Marsh
MVP [MS Access]

Thank you for responding. By taking the date out of the query of course it
works, but I want to be able to sum for a series of days. I have months
worth of data and sometimes the operator just wants to see a week or a month.

Remove the Date field from the Select and Group By clauses

Love Buzz wrote:
I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

 

[KARL DEWEY]

My previous post ---
I added in the [StartDate] & [EndDate] in the SELECT statement so they
would be visible and available for a report.

--
KARL DEWEY
Build a little - Test a little

Thank you Marshall and Karl.

Changing it to 'Where' did the trick. However, I have the query tied to a
report and would like to display the range I selected. How can that be done?

Thanks so much.

That's why you leave the date field in the Where clause.
You can not include it in the Select and Group By clauses or
you will get a total for each date in the range.

If you are working in the query designer, uncheck the Show
box and use Where in the Totals row.
--
Marsh
MVP [MS Access]

Thank you for responding. By taking the date out of the query of course it
works, but I want to be able to sum for a series of days. I have months
worth of data and sometimes the operator just wants to see a week or a month.


:
Remove the Date field from the Select and Group By clauses

Love Buzz wrote:
I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));

 

[Love Buzz]

Sorry Karl. I updated my query and saved it fine, but when I run it, I get
this message.

'You tried to execute a query that does not include the specified expression
'[December 4].UserID Like [Enter Employee User ID]&"*" AND[December].Date
Between[StartDate] And [EndDate]' as part of an aggregate function'

Any ideas?

Here is my SQL view again:

SELECT [December 4].UserID, [December 4].Queu, [December 4].Function,
Sum([December 4].Items) AS SumOfItems, Sum([December 4].timeinqueu) AS
SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS Expr1, [Queus and
SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS Expr3,
[StartDate], [EndDate]
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Function = [Queus and SLAs].Function) AND ([December 4].Queu = [Queus and
SLAs].[Queu Number])
WHERE ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].Date) Between [StartDate] And [EndDate]))
GROUP BY [December 4].UserID, [December 4].Queu, [December 4].Function,
[Queus and SLAs].SLA
HAVING ((([December 4].UserID) Like [Enter Employee User ID] & "*") AND
(([December 4].Date) Between [StartDate] And [EndDate]));

My previous post ---
I added in the [StartDate] & [EndDate] in the SELECT statement so they
would be visible and available for a report.

--
KARL DEWEY
Build a little - Test a little

Thank you Marshall and Karl.

Changing it to 'Where' did the trick. However, I have the query tied to a
report and would like to display the range I selected. How can that be done?

Thanks so much.

That's why you leave the date field in the Where clause.
You can not include it in the Select and Group By clauses or
you will get a total for each date in the range.

If you are working in the query designer, uncheck the Show
box and use Where in the Totals row.
--
Marsh
MVP [MS Access]


Love Buzz wrote:
Thank you for responding. By taking the date out of the query of course it
works, but I want to be able to sum for a series of days. I have months
worth of data and sometimes the operator just wants to see a week or a month.


:
Remove the Date field from the Select and Group By clauses

Love Buzz wrote:
I have a query that I am trying to prompt the user to key the date range and
the individuals user ID on. The prompting works great and if the user is
just inquiring about one day, everything adds up fine. However, when a date
range is selected, the response is not the desired. Instead of summing up
the date ranges, I am just getting each day within the range with the totals
for that day.

Hope that made sense. Here is the SQL view:

SELECT [December 4].Date, [December 4].UserID, [December 4].Queu, [December
4].Function, Sum([December 4].Items) AS SumOfItems, Sum([December
4].timeinqueu) AS SumOftimeinqueu, Sum([December 4]![timeinqueu]/"60"/60) AS
Expr1, [Queus and SLAs].SLA, [SumOfItems]/[Expr1] AS Expr2, [Expr2]/[SLA] AS
Expr3
FROM ([December 4] INNER JOIN [Returns Associates] ON [December 4].UserID =
[Returns Associates].[User ID]) INNER JOIN [Queus and SLAs] ON ([December
4].Queu = [Queus and SLAs].[Queu Number]) AND ([December 4].Function = [Queus
and SLAs].Function)
GROUP BY [December 4].Date, [December 4].UserID, [December 4].Queu,
[December 4].Function, [Queus and SLAs].SLA
HAVING ((([December 4].Date) Between [StartDate] And [EndDate]) AND
(([December 4].UserID) Like [Enter Employee User ID] & "*"));