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
323 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 44

Accepted Solution

by:
GRayL earned 500 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

724 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