Hello

I did not work with MS access for a while now. Sorry, if my question
is trivial. I have a very simple crosstab query:

PARAMETERS Expr Text ( 255 );
TRANSFORM qryCOC.Expr
SELECT qryCOC.Course, qryCOC.Class, qryCOC.Group
FROM qryCOC
GROUP BY qryCOC.Course, qryCOC.Class, qryCOC.Group
PIVOT qryCOC.Start;

Expr is a complicate expression created by a previous query: Expr:
Format([Time],"HH:mm") & "-" & Format([Finish],"HH:mm") & " " &
Format([Length],"HH:mm") & " " & [Num Weeks] & " wks " & [Type] & " "
& [Module] & " " & [Lecturer] & " " & [Room] & [Req Room] & [Category]

Result of this looks like: 17:00-18:30 01:30 34 wks TUT Tutorial
Bindon, Peter Classroom

When I try to run my crosstab, I get a message: "You tried to execute
a query that does not include the specified expression 'Expr' as a
part of an aggregate function'

What would you recommend I should do? Thank you.

Galina

Remove the Parameter line. Your query is saying there is a field named Expr
and a parameter named Expr.

Also, you must apply one of the aggregate functions to the field in the
TRANSFORM clause. You can use FIRST, LAST, MAX, or Min on a text string.

I would try the following.

TRANSFORM First(qryCOC.Expr) as FirstExpr
SELECT qryCOC.Course, qryCOC.Class, qryCOC.Group
FROM qryCOC
GROUP BY qryCOC.Course, qryCOC.Class, qryCOC.Group
PIVOT qryCOC.Start;



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

On 5/12/2011 7:34 AM, Galka wrote: