?
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
?
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…
Suggested Courses

762 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