Crosstab Sum

[Secret Squirrel]

I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

 

[Tom van Stiphout]

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP

 

[John Spencer (MVP)]

While ' should work when calling the format function I would use "

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(WorkHours) AS SumOfWorkHours
SELECT EmpID
, Format([PunchDate],"mm") AS MonthSelect
, Count(RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE Format([PunchDate],"mm")=[Forms]![frmTest]![cboMonth]
OR [Forms]![frmTest]![cboMonth]) Is Null
GROUP BY EmpID
, Format([PunchDate],"mm")
, PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
[PunchDate]-Weekday([PunchDate])+7;

As noted elsewhere. You can trouble shoot by simplifying the query until it
does work and then add back parts until it fails. That at least will allow
you to pinpoint the problem.

Removing the parameter declaration is NOT the solution as you MUST declare
parameters when using them in a crosstab query. I would probably start by
replacing the apostrophes with quote marks, then simplifying the Pivot to just
"Wk " & Format([PunchDate],"ww",2) & " "


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Secret Squirrel]

Hi Tom,
I tried everything you mentioned and it still gave me the same response
saying it's too complex to evaluate. When I changed the qryTimeAttendance
into a Make Table Query and then removed the where clause and parameter I
then got a Data Type Mismatch error. I think it has something to do with the
sum of the WorkHours that I'm using. The WorkHours is a calculated value in
my qryTimeAttendance. Here is what that query looks like.

SELECT qryBusinessHoursUnion.RecordID, qryBusinessHoursUnion.Shift,
qryBusinessHoursUnion.ShiftID, qryBusinessHoursUnion.EmpID, [LastName] & ", "
& [FirstName] AS EmpName, qryBusinessHoursUnion.PunchDate,
qryBusinessHoursUnion.PunchCount, qryBusinessHoursUnion.DateIn,
qryBusinessHoursUnion.TimeIn, qryBusinessHoursUnion.DateOut,
qryBusinessHoursUnion.TimeOut, qryBusinessHoursUnion.EmployeeID,
qryBusinessHoursUnion.Starttime,
IIf([TimeIn]>[StartTime],TimeDuration([StartTime],[TimeIn]),"") AS Tardy,
tblEmployees.Status, tblEmployees.Dept, Format([PunchDate],"mmmm") AS Mo,
tblDepartment.Department, qryBusinessHoursUnion.Endtime, [DateIn] & " " &
[TimeIn] AS Start, [DateOut] & " " & [TimeOut] AS [End],
(DateDiff("n",[Start],[End])) AS WorkMinutes, [WorkMinutes]\60 &
Format([WorkMinutes] Mod 60,"\:00") AS WorkHours
FROM tblDepartment INNER JOIN (tblEmployees INNER JOIN qryBusinessHoursUnion
ON tblEmployees.ID = qryBusinessHoursUnion.EmployeeID) ON
tblDepartment.DeptID = tblEmployees.Dept
ORDER BY [LastName] & ", " & [FirstName], qryBusinessHoursUnion.PunchDate;

What do you think?

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP

I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

 

[Secret Squirrel]

One other note....The reason I think it's the WorkHours is because I changed
that to RecordID and just tested summing up the RecordID instead of WorkHours
and it worked fine. Why will it not work using the WorkHours?

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP

I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

 

[John Spencer (MVP)]

Because WORKHOURS is a string value and you are trying to SUM a string.

Try using WorkMinutes in the crosstab and then format the sum of WorkMinutes
in the crosstab.

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(WorkMinutes)\60 & Format(Sum(WorkMinutes) mod 60,":00") AS
SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],"mm") AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],"mm"))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],"mm"),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

One other note....The reason I think it's the WorkHours is because I changed
that to RecordID and just tested summing up the RecordID instead of WorkHours
and it worked fine. Why will it not work using the WorkHours?

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP

I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

 

[Secret Squirrel]

That worked but only when I made the qryTimeAttendance into a Make Table
Query and used that table in my crosstab instead of using the actual
qryTimeAttendance. What's the difference there and why can't I just use the
query?

Because WORKHOURS is a string value and you are trying to SUM a string.

Try using WorkMinutes in the crosstab and then format the sum of WorkMinutes
in the crosstab.

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(WorkMinutes)\60 & Format(Sum(WorkMinutes) mod 60,":00") AS
SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],"mm") AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],"mm"))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],"mm"),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

One other note....The reason I think it's the WorkHours is because I changed
that to RecordID and just tested summing up the RecordID instead of WorkHours
and it worked fine. Why will it not work using the WorkHours?

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP


I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

 

[Secret Squirrel]

Figured it out. The reason why it wouldn't work using the qryTimeAttendance
is because the WorkMinutes calc had an "#Error" in some records because those
employees didn't punch out so it couldn't calculate the minutes. Fixed that
problem. Everything is working fine now.
Thanks for your help John!

Because WORKHOURS is a string value and you are trying to SUM a string.

Try using WorkMinutes in the crosstab and then format the sum of WorkMinutes
in the crosstab.

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(WorkMinutes)\60 & Format(Sum(WorkMinutes) mod 60,":00") AS
SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],"mm") AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],"mm"))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],"mm"),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

One other note....The reason I think it's the WorkHours is because I changed
that to RecordID and just tested summing up the RecordID instead of WorkHours
and it worked fine. Why will it not work using the WorkHours?

On Sun, 1 Feb 2009 18:44:00 -0800, Secret Squirrel

In situations like this the best approach I know of is to make a copy
of the query and simplify it. For example take out the Parameter
declaration. Does it make a difference? Take out the entire Where
clause. Run again. Also turn qryTimeAttendance in a MakeTable query
and run your query against that new table.
Let us know what you find out.

-Tom.
Microsoft Access MVP


I'm having trouble with my crosstab query. It is telling me it's too complex
to evaluate, etc. Can anyone see what the problem might be?

PARAMETERS [Forms]![frmTest]![cboMonth] Text ( 255 );
TRANSFORM Sum(qryTimeAttendance.WorkHours) AS SumOfWorkHours
SELECT qryTimeAttendance.EmpID, Format([PunchDate],'mm') AS MonthSelect,
Count(qryTimeAttendance.RecordID) AS [Total Of RecordID]
FROM qryTimeAttendance
WHERE (((Format([PunchDate],'mm'))=[Forms]![frmTest]![cboMonth])) OR
((([Forms]![frmTest]![cboMonth]) Is Null))
GROUP BY qryTimeAttendance.EmpID, Format([PunchDate],'mm'),
qryTimeAttendance.PunchCount
PIVOT "Wk " & Format([PunchDate],"ww",2) & " " &
([PunchDate]-Weekday([PunchDate]))+7;