Problem query

G

Guest

The query I am using is to report information by "Year" and "Quarter" but I
have found that it reports by "Quarter" regardles of year. There fore I am
getting totals of quarters for the last several years. How can it be
corrected to do as requested?
Query: SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2, Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 4))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate])
HAVING (((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12") AND ((OvertimeTracking.Quarter)=[Enter Quarter]));
 
K

Ken Snell \(MVP\)

It's not clear how Year fits into this query? The query is "getting" a
quarter (which I assume is a value of 1 to 4, but that may not be right),
but not a year value? You'll need to give us more info about your data
structure before we can give specific suggestions.

I do recommend that the query's structure be changed to this, using your
current query, so that it will run faster (using WHERE clauses to filter out
data instead of HAVING -- WHERE filtering is done before grouping and
summing the data, whereas HAVING filtering is done after the grouping and
summing) -- be sure to double-check the AND/OR groupings and parentheses in
the WHERE clause as I may have not combined them quite the way you wanted:

SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (OvertimeTracking.aUTHORIZED Like 1 Or
OvertimeTracking.aUTHORIZED Like 4) AND (Employees.otdeirelist Like "10" Or
Employees.otdeirelist Like "12") AND
OvertimeTracking.Quarter=[Enter Quarter]
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate]);


I also see that you're using "OvertimeTracking.aUTHORIZED Like 1" and
"OvertimeTracking.aUTHORIZED Like 4" in the query. If [aUTHORIZED] is a text
field, it's better if you put ' characters around the 1 and the 4.

Also, I note that you're using Day as the name of a field in a table. it and
many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




Nick said:
The query I am using is to report information by "Year" and "Quarter" but
I
have found that it reports by "Quarter" regardles of year. There fore I am
getting totals of quarters for the last several years. How can it be
corrected to do as requested?
Query: SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 4))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate])
HAVING (((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12") AND ((OvertimeTracking.Quarter)=[Enter Quarter]));
Click to expand...
 
G

Guest

Thanks! I have made corrections as you suggested. The year request is the
"ActionDate". I have made changes to query that include a year request. This
request returns the correct data with the exception of the year it returns
1905 no matter what year I enter and not the actual year. I need to have the
requested year on the report.
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2, Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Year([Actiondate]) AS oTDLdaTE
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.Quarter)=[Enter Quarter]) AND
((OvertimeTracking.aUTHORIZED) Like 1 Or (OvertimeTracking.aUTHORIZED) Like
4) AND ((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12"))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Year([Actiondate]);


Ken Snell (MVP) said:
It's not clear how Year fits into this query? The query is "getting" a
quarter (which I assume is a value of 1 to 4, but that may not be right),
but not a year value? You'll need to give us more info about your data
structure before we can give specific suggestions.

I do recommend that the query's structure be changed to this, using your
current query, so that it will run faster (using WHERE clauses to filter out
data instead of HAVING -- WHERE filtering is done before grouping and
summing the data, whereas HAVING filtering is done after the grouping and
summing) -- be sure to double-check the AND/OR groupings and parentheses in
the WHERE clause as I may have not combined them quite the way you wanted:

SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (OvertimeTracking.aUTHORIZED Like 1 Or
OvertimeTracking.aUTHORIZED Like 4) AND (Employees.otdeirelist Like "10" Or
Employees.otdeirelist Like "12") AND
OvertimeTracking.Quarter=[Enter Quarter]
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate]);


I also see that you're using "OvertimeTracking.aUTHORIZED Like 1" and
"OvertimeTracking.aUTHORIZED Like 4" in the query. If [aUTHORIZED] is a text
field, it's better if you put ' characters around the 1 and the 4.

Also, I note that you're using Day as the name of a field in a table. it and
many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




Nick said:
The query I am using is to report information by "Year" and "Quarter" but
I
have found that it reports by "Quarter" regardles of year. There fore I am
getting totals of quarters for the last several years. How can it be
corrected to do as requested?
Query: SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 4))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate])
HAVING (((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12") AND ((OvertimeTracking.Quarter)=[Enter Quarter]));
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Your query does not ask for the year from the user; try this:

SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Year([Actiondate]) AS oTDLdaTE
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.Quarter)=[Enter Quarter]) AND
((OvertimeTracking.aUTHORIZED) Like 1 Or (OvertimeTracking.aUTHORIZED) Like
4) AND ((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12") AND Year([Actiondate])=[Enter the year (yyyy):])
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Year([Actiondate]);

--

Ken Snell
<MS ACCESS MVP>



Nick said:
Thanks! I have made corrections as you suggested. The year request is the
"ActionDate". I have made changes to query that include a year request.
This
request returns the correct data with the exception of the year it
returns
1905 no matter what year I enter and not the actual year. I need to have
the
requested year on the report.
SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Year([Actiondate]) AS oTDLdaTE
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.Quarter)=[Enter Quarter]) AND
((OvertimeTracking.aUTHORIZED) Like 1 Or (OvertimeTracking.aUTHORIZED)
Like
4) AND ((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist) Like
"12"))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Year([Actiondate]);


Ken Snell (MVP) said:
It's not clear how Year fits into this query? The query is "getting" a
quarter (which I assume is a value of 1 to 4, but that may not be right),
but not a year value? You'll need to give us more info about your data
structure before we can give specific suggestions.

I do recommend that the query's structure be changed to this, using your
current query, so that it will run faster (using WHERE clauses to filter
out
data instead of HAVING -- WHERE filtering is done before grouping and
summing the data, whereas HAVING filtering is done after the grouping and
summing) -- be sure to double-check the AND/OR groupings and parentheses
in
the WHERE clause as I may have not combined them quite the way you
wanted:

SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (OvertimeTracking.aUTHORIZED Like 1 Or
OvertimeTracking.aUTHORIZED Like 4) AND (Employees.otdeirelist Like "10"
Or
Employees.otdeirelist Like "12") AND
OvertimeTracking.Quarter=[Enter Quarter]
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name], Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate]);


I also see that you're using "OvertimeTracking.aUTHORIZED Like 1" and
"OvertimeTracking.aUTHORIZED Like 4" in the query. If [aUTHORIZED] is a
text
field, it's better if you put ' characters around the 1 and the 4.

Also, I note that you're using Day as the name of a field in a table. it
and
many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not
being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA
Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>




Nick said:
The query I am using is to report information by "Year" and "Quarter"
but
I
have found that it reports by "Quarter" regardles of year. There fore I
am
getting totals of quarters for the last several years. How can it be
corrected to do as requested?
Query: SELECT Employees.SSI, Sum(OvertimeTracking.oTDLhOURUSED) AS
SumOfoTDLhOURUSED, [Last Name] & ", " & [First Name] AS Expr2,
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter,
Sum(OvertimeTracking.oTDLOP)
AS SumOfoTDLOP, Sum(OvertimeTracking.oTBlocked) AS SumOfoTBlocked,
Sum(OvertimeTracking.oVERtIMErEFUSED) AS SumOfoVERtIMErEFUSED,
Format([ActionDate]) AS Expr1
FROM Employees INNER JOIN OvertimeTracking ON Employees.SSI =
OvertimeTracking.EMPiD
WHERE (((OvertimeTracking.aUTHORIZED) Like 1 Or
(OvertimeTracking.aUTHORIZED) Like 4))
GROUP BY Employees.SSI, [Last Name] & ", " & [First Name],
Employees.Day,
Employees.otdeirelist, OvertimeTracking.Quarter, Format([ActionDate])
HAVING (((Employees.otdeirelist) Like "10" Or (Employees.otdeirelist)
Like
"12") AND ((OvertimeTracking.Quarter)=[Enter Quarter]));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Want to find average and color code 1
Calulate total prior to a given date 3
Cross tab to begin fiscal year 11
Query employees have null data or having Data? 1
Problem with query 2
Problem with (complicated) expression in query 3
MS Access Query to sum up hours grouped by staff name 1
Year-To-Date Query with a lime Twist 5

Top