[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

You tried to execute a query that does not include the specific expression as part of an aggregate function?????????

Posted on 2006-07-15
9
Medium Priority
?
331 Views
Last Modified: 2008-02-01
I am using the following cross-tab query but an error message comes out "You tried to execute a query that does not include the specific expression'TimeCode' as part of an aggregate function" any suggestions ? thanks !

TRANSFORM tbPlacement.TimeCode
SELECT tbStudentInfo.StudentName
FROM tbStudentInfo INNER JOIN (tbDetailsOfDates INNER JOIN (tbDetailsOfWeekday INNER JOIN (tbPlacement INNER JOIN tbStudentGroupList ON tbPlacement.GroupName = tbStudentGroupList.GroupName) ON tbDetailsOfWeekday.DayNo = tbPlacement.DayNo) ON (tbDetailsOfWeekday.DayNo = tbDetailsOfDates.DayNo) AND (tbDetailsOfDates.DayNo = tbPlacement.DayNo) AND (tbDetailsOfDates.WeekNo = tbPlacement.WeekNo) AND (tbDetailsOfDates.TermNo = tbPlacement.TermNo)) ON tbStudentInfo.StudentID = tbStudentGroupList.StudentID
WHERE (((tbPlacement.Venue)="4d") AND ((tbPlacement.VenueCode)="sh"))
GROUP BY tbStudentInfo.StudentName
ORDER BY tbStudentInfo.StudentName, tbDetailsOfDates.Dates
PIVOT tbDetailsOfDates.Dates;
0
Comment
Question by:mmccy
  • 5
  • 3
9 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17115524
Does the TRANSFORM statement not require an aggregate function?

e,g,

TRANSFORM Sum(tbPlacement.TimeCode

or

TRANSFORM Var(tbPlacement.TimeCode
0
 

Author Comment

by:mmccy
ID: 17116552
there is no aggregate function for the transform statement
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17116652
What Im saying is, dont u need one
if u add one in, do u still get the error
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Accepted Solution

by:
GRayL earned 1500 total points
ID: 17117674
By way of explanation, in a crosstab query, if the field in the TRANSFORM statement is not part of an Aggregate function, it must be named in the GROUP BY clause.  Normally, that field is wrapped in an aggregate function such as Sum, First, Last, Min, Max, etc. so the crosstab can serve as a collector of data, with rows as per the SELECT statement and additional columns as per the PIVOT clause.  It is hard to imagine a meaningful crosstab query without an aggregate function in the TRANSFORM statement.  When the TRANSFORM statement is an aggregate function, it is not part of the GROUP BY clause.  
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17117805
Perhaps thats how I needed to phrase it, as that was the point I was trying to make

mmccy, in your question after this, u used FIRST, just wondering whether what I wrote actually helped or not - feedback purposes
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17122865
Thanks, glad I could help.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 17122866
Thanks, glad I could help.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17126869
GRayL appreciated it so much, it stressed it again :)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17126870
urm, typo
should be

he stressed

not

it stressed

ooops
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question