By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,985 Members | 1,694 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,985 IT Pros & Developers. It's quick & easy.

Aggregate Function Error Message

P: 22
Hello,

I've been getting the following error message in multiple queries, over and over again and I don't understand what it doesn't like.

"You tried to execute a query that doesn't include the specific expression '1*fCalculateMedian(tbl.MWHRSday.FLEET_ID,[avg_mwhrs])' as part of an aggregate function."

The '1*fCalculateMedian(tbl.MWHRSday.FLETT_ID,[avg_mwhrs])' part is always something different depending on which field it has issue with.

Anybody run into this before?
Sep 5 '08 #1
Share this Question
Share on Google+
4 Replies


missinglinq
Expert 2.5K+
P: 3,532
This type of query is not my forte, but I'm sure someone else will be along to help you out! It's very good that you posted the error message you're receiving, but I'm pretty sure that you need to also post a sample of the SQL code that causing the problem, as well.

Linq ;0)>
Sep 5 '08 #2

P: 22
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tbl_MWHRSday.FLEET_ID, 1*(fCalculateMedian([FLEET_ID],[avg_mwhrs])) AS Median INTO tbl_fleetmedian
  2. FROM tbl_MWHRSday
  3. GROUP BY tbl_MWHRSday.FLEET_ID;

Also, here is the module fCalculateMedian

Expand|Select|Wrap|Line Numbers
  1. Public Function fCalculateMedian(strfleetname As String, dblmotorMWHRS As Double)
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
  3. Dim intNumOfRecords As Integer, curMotorMWHR As Double
  4.  
  5. MySQL = "SELECT tbl_MWHRSday.FLEET_ID, tbl_MWHRSday.avg_mwhrs FROM tbl_MWHRSday "
  6. MySQL = MySQL & "WHERE tbl_MWHRSday.Fleet_ID='" & strfleetname & "' ORDER BY tbl_MWHRSday.Fleet_ID, tbl_MWHRSday.avg_mwhrs;"
  7.  
  8. Set MyDB = CurrentDb()
  9. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
  10.  
  11. MyRS.MoveLast: MyRS.MoveFirst
  12.  
  13. intNumOfRecords = MyRS.RecordCount
  14. If intNumOfRecords = 0 Then Exit Function
  15.  
  16. If intNumOfRecords Mod 2 = 0 Then     'Even number of Records
  17.   MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
  18.     curMotorMWHR = MyRS![avg_mwhrs]      '1st value to average
  19.   MyRS.MoveNext
  20.     curMotorMWHR = curMotorMWHR + MyRS![avg_mwhrs]               '2nd value to average added to 1st value
  21.     fCalculateMedian = curMotorMWHR / 2  'Average them out
  22. Else   'Odd number of Records
  23.   MyRS.Move (intNumOfRecords \ 2)
  24.   fCalculateMedian = MyRS![avg_mwhrs]
  25. End If
  26.  
  27. MyRS.Close
Sep 5 '08 #3

NeoPa
Expert Mod 15k+
P: 31,471
When dealing with GROUPed queries (where a GROUP BY clause exists) only items which are either themselves GROUPed BY, or are aggregated, can be displayed.

Aggregation is selecting a value which somehow represents the whole group of that item.

Aggregate functions are listed in the "Total" row in the Query Definition grid after selecting the Sigma button. This includes Sum(), Max(), Min(), First(), Last() etc.

It also includes Group. This means that the item is used to determine which records should be treated as together in a group.

A grouped item doesn't change in the group, so logically can be used directly in the SELECT clause.

All other items do, so must include something to indicate what should be used. Even an Expression must consist of only grouped or aggregated items.

Your problem is that you're trying to refer to a field, within a grouped query, which has no logical meaning.

Consider :
Table Name=[tblData]
Expand|Select|Wrap|Line Numbers
  1. Field        Type
  2. Class        String
  3. StudentName  String
  4. ExamScore    Number
Expand|Select|Wrap|Line Numbers
  1. Class  StudentName  ExamScore
  2. Maths  Andrew           55
  3. Maths  Brian            60
  4. Maths  Charles          65
  5. Maths  David            70
  6. Maths  Edward           75
  7. Maths  Francis          80
  8. Maths  Gerald           85
Expand|Select|Wrap|Line Numbers
  1. SELECT [Class]
  2.        Sum([ExamScore]) AS SumExams
  3.  
  4. FROM tblData
Result
Expand|Select|Wrap|Line Numbers
  1. Class  SumExams
  2. Maths     490
This illustrates both types of acceptable forms in a GROUP BY query.

Now consider how much sense there would be within this query to try to show the [StudentName]?

On its own it would have no meaning within the context. It would be possible (though hard to see the use) to use First([StudentName]).
Sep 6 '08 #4

FishVal
Expert 2.5K+
P: 2,653
Hello, denveromlp.

First:
Why would you ever need to use GROUP BY clause in the query?
Second:
What for are you still using dblmotorMWHRS argument in fCalculateMedian() function?

Regards,
Fish
Sep 7 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.