Aggregate Function Error Message

[denveromlp]

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?

[missinglinq]

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)>

[denveromlp]

SELECT DISTINCT tbl_MWHRSday.FLEET_ID, 1*(fCalculateMedian([FLEET_ID],[avg_mwhrs])) AS Median INTO tbl_fleetmedian
FROM tbl_MWHRSday
GROUP BY tbl_MWHRSday.FLEET_ID;

Also, here is the module fCalculateMedian

Public Function fCalculateMedian(strfleetname As String, dblmotorMWHRS As Double)
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
Dim intNumOfRecords As Integer, curMotorMWHR As Double
 
MySQL = "SELECT tbl_MWHRSday.FLEET_ID, tbl_MWHRSday.avg_mwhrs FROM tbl_MWHRSday "
MySQL = MySQL & "WHERE tbl_MWHRSday.Fleet_ID='" & strfleetname & "' ORDER BY tbl_MWHRSday.Fleet_ID, tbl_MWHRSday.avg_mwhrs;"
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
 
MyRS.MoveLast: MyRS.MoveFirst
 
intNumOfRecords = MyRS.RecordCount
If intNumOfRecords = 0 Then Exit Function
 
If intNumOfRecords Mod 2 = 0 Then     'Even number of Records
  MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
    curMotorMWHR = MyRS![avg_mwhrs]      '1st value to average
  MyRS.MoveNext
    curMotorMWHR = curMotorMWHR + MyRS![avg_mwhrs]               '2nd value to average added to 1st value
    fCalculateMedian = curMotorMWHR / 2  'Average them out
Else   'Odd number of Records
  MyRS.Move (intNumOfRecords \ 2)
  fCalculateMedian = MyRS![avg_mwhrs]
End If
 
MyRS.Close

[NeoPa]

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]

Field        Type
Class        String
StudentName  String
ExamScore    Number

Class  StudentName  ExamScore
Maths  Andrew           55
Maths  Brian            60
Maths  Charles          65
Maths  David            70
Maths  Edward           75
Maths  Francis          80
Maths  Gerald           85

SELECT [Class]
       Sum([ExamScore]) AS SumExams
 
FROM tblData

Result

Class  SumExams
 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]).

[FishVal]

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