Crosstab Queries

[brian]

Sorry, but I have read many postings on this subject and just don't get it.
I am getting the following error:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

I have a Crosstab Query which is based on another Query. I want to prompt
for two dates and send the results back which fall in the range. Below are
the SQL statements for both

Initial Query:
SELECT "X" AS Exp1, Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Attendees.AttendeeID, Registration.AttendeeID, Courses.CATEGORY,
IIf(Registration.Completed=-1,Registration.StartDate,"") AS StartDate,
Attendees.PrimaryFacility, Attendees.FacilityID, Registration.Completed,
Attendees.Designation, Groups.Group, Roles.Role, Attendees.CernerID
FROM Courses INNER JOIN (((Registration INNER JOIN Attendees ON
Registration.AttendeeID = Attendees.AttendeeID) LEFT JOIN Groups ON
Attendees.GroupID = Groups.GroupID) LEFT JOIN Roles ON Attendees.RoleID =
Roles.RoleID) ON Courses.EventID = Registration.EventID
ORDER BY Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Courses.CATEGORY, IIf(Registration.Completed=-1,Registration.StartDate,"")
DESC;

Crosstab Query:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Min(AllAttendeeMatrixQuery.StartDate) AS FirstOfStartDate1
SELECT AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
FROM AllAttendeeMatrixQuery
WHERE (((AllAttendeeMatrixQuery.StartDate) Between [Start Date] And [End
Date]))
GROUP BY AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
PIVOT AllAttendeeMatrixQuery.CATEGORY;

Any help would be much appreciated.

 

[Jerry Whittle]

Hi,

Put the parameters in the first query. Crosstabs can have problems with
parameters.

Also your StartDate IIf statement is passing a zero-length string instead of
a date while the parameters all looking for a DateTime. Maybe changing the
IIf to a Null might work. Or passing a bogus date like #1/1/1950# which will
be out of the normal range of dates that you're looking for in the WHERE
clause.

Lastly if you aren't using the initial query for anything else, take out the
Order By or sort. The crosstab is going to resort things anyway so it might
speed things up.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, but I have read many postings on this subject and just don't get it.
I am getting the following error:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

I have a Crosstab Query which is based on another Query. I want to prompt
for two dates and send the results back which fall in the range. Below are
the SQL statements for both

Initial Query:
SELECT "X" AS Exp1, Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Attendees.AttendeeID, Registration.AttendeeID, Courses.CATEGORY,
IIf(Registration.Completed=-1,Registration.StartDate,"") AS StartDate,
Attendees.PrimaryFacility, Attendees.FacilityID, Registration.Completed,
Attendees.Designation, Groups.Group, Roles.Role, Attendees.CernerID
FROM Courses INNER JOIN (((Registration INNER JOIN Attendees ON
Registration.AttendeeID = Attendees.AttendeeID) LEFT JOIN Groups ON
Attendees.GroupID = Groups.GroupID) LEFT JOIN Roles ON Attendees.RoleID =
Roles.RoleID) ON Courses.EventID = Registration.EventID
ORDER BY Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Courses.CATEGORY, IIf(Registration.Completed=-1,Registration.StartDate,"")
DESC;

Crosstab Query:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Min(AllAttendeeMatrixQuery.StartDate) AS FirstOfStartDate1
SELECT AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
FROM AllAttendeeMatrixQuery
WHERE (((AllAttendeeMatrixQuery.StartDate) Between [Start Date] And [End
Date]))
GROUP BY AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
PIVOT AllAttendeeMatrixQuery.CATEGORY;

Any help would be much appreciated.

 

[brian]

Thank you, Jerry. It works now.

Hi,

Put the parameters in the first query. Crosstabs can have problems with
parameters.

Also your StartDate IIf statement is passing a zero-length string instead of
a date while the parameters all looking for a DateTime. Maybe changing the
IIf to a Null might work. Or passing a bogus date like #1/1/1950# which will
be out of the normal range of dates that you're looking for in the WHERE
clause.

Lastly if you aren't using the initial query for anything else, take out the
Order By or sort. The crosstab is going to resort things anyway so it might
speed things up.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry, but I have read many postings on this subject and just don't get it.
I am getting the following error:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

I have a Crosstab Query which is based on another Query. I want to prompt
for two dates and send the results back which fall in the range. Below are
the SQL statements for both

Initial Query:
SELECT "X" AS Exp1, Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Attendees.AttendeeID, Registration.AttendeeID, Courses.CATEGORY,
IIf(Registration.Completed=-1,Registration.StartDate,"") AS StartDate,
Attendees.PrimaryFacility, Attendees.FacilityID, Registration.Completed,
Attendees.Designation, Groups.Group, Roles.Role, Attendees.CernerID
FROM Courses INNER JOIN (((Registration INNER JOIN Attendees ON
Registration.AttendeeID = Attendees.AttendeeID) LEFT JOIN Groups ON
Attendees.GroupID = Groups.GroupID) LEFT JOIN Roles ON Attendees.RoleID =
Roles.RoleID) ON Courses.EventID = Registration.EventID
ORDER BY Attendees.AttendeeLastName, Attendees.AttendeeFirstName,
Courses.CATEGORY, IIf(Registration.Completed=-1,Registration.StartDate,"")
DESC;

Crosstab Query:
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
TRANSFORM Min(AllAttendeeMatrixQuery.StartDate) AS FirstOfStartDate1
SELECT AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
FROM AllAttendeeMatrixQuery
WHERE (((AllAttendeeMatrixQuery.StartDate) Between [Start Date] And [End
Date]))
GROUP BY AllAttendeeMatrixQuery.AttendeeLastName,
AllAttendeeMatrixQuery.AttendeeFirstName, AllAttendeeMatrixQuery.CernerID,
AllAttendeeMatrixQuery.Designation, AllAttendeeMatrixQuery.Group,
AllAttendeeMatrixQuery.Role
PIVOT AllAttendeeMatrixQuery.CATEGORY;

Any help would be much appreciated.