Link to home
Start Free TrialLog in
Avatar of hawkeyex
hawkeyex

asked on

Problem with SQL query for MSAccess - Aggregate Function error - using Median function

I have here a problem. Please forgive an Access newbie. I'm not all that great in it, but it has definetely been a eye opener for me in learning this stuff.

Error shows up: "You tried to execute a query that does not include the specified expression 'IIf(AvgLTCTotalColi!Expr1="","",Median([001A (LTC)],[Total Coliform #/100ml]))' as part of an aggregate function.

I don't understand what that error means. Can someone elaborate for me?

Code is below.

SELECT IIf(AvgLTCTotalColi!Expr1="","",Median([001A (LTC)],[Total Coliform #/100ml])) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

I have a query in a query there, called AvgLTCTotalColi which looks like this:

SELECT Avg([001A (LTC)]![Total Coliform #/100ml]) AS Expr1, Format(Date,"yyyy-mm") AS Expr2 FROM [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

And now, there is a function module called dMedian I found from Microsoft Access Knowledge Base for medians:

Option Explicit

Public Function Median(tName As String, fldName As String) As Single
  Dim MedianDB As DAO.Database
  Dim ssMedian As DAO.Recordset
  Dim RCount As Integer, i As Integer, x As Double, y As Double, _
      OffSet As Integer
  Set MedianDB = CurrentDb()
  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")
  'NOTE: To include nulls when calculating the median value, omit
  'WHERE [" & fldName & "] IS NOT NULL from the example.
  ssMedian.MoveLast
  RCount% = ssMedian.RecordCount
  x = RCount Mod 2
  If x <> 0 Then
     OffSet = ((RCount + 1) / 2) - 2
     For i% = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     Median = ssMedian(fldName)
  Else
     OffSet = (RCount / 2) - 2
     For i = 0 To OffSet
        ssMedian.MovePrevious
     Next i
     x = ssMedian(fldName)
     ssMedian.MovePrevious
     y = ssMedian(fldName)
     Median = (x + y) / 2
  End If
  ssMedian.Close
  MedianDB.Close
End Function
Avatar of Steve Bink
Steve Bink
Flag of United States of America image

The error is informing you of a SQL structure problem.  When you use a GROUP BY clause in SQL, any fields in the field list must also be a) listed in the GROUP BY field list, or b) listed in the field list as part of an aggregate function, such as Sum, Count, etc.  Your SQL:

SELECT IIf(AvgLTCTotalColi!Expr1="","",Median([001A (LTC)],[Total Coliform #/100ml])) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

In this statement, you are group by the results of the Format function.  For each individual row meeting the criteria, your IIF statement is processed, but you are still left with, for example, 100 rows of the same date.  You need to provide a separate aggregate function for the results of your IIF, like this:

SELECT SUM(IIf(AvgLTCTotalColi!Expr1="","",Median([001A (LTC)],[Total Coliform #/100ml]))) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

Do you understand?
Looks to me like it should work fine if you just remove the Group BY clause

GROUP BY Format(Date,"yyyy-mm");

Alan
I feel I explained that poorly...let me try again with an example.  Here's a hypothetical SQL similar to yours:

SELECT MyDate, iif(MyValue=0,-1,MyValue) as Expr1 FROM MyTable GROUP BY MyDate

This statement will generate the same error as your SQL.  If I have data looking like this:

                     ------  8/1    1
   one date    /          8/1    0
        --------|           8/1    3
    one group \          8/1    1
                      ------ 8/1    5

The first step of the SQL is to process the IIF.  My returned recordset will now look something like this:

                     ------  8/1    1
   one date    /          8/1    -1
        --------|           8/1    3
    one group \          8/1    1
                      ------ 8/1    5

Now it takes all the 8/1 dates and combines them.  Access knows the "8/1" is the group 'header', so to speak, but what is it supposed to do with the other column?  It needs to be able to combine all the values into one statistical value by way of an "aggregate function".  Examples of these functions are Sum, Count, Median, StDev, etc.  If I change my SQL to find the median of these values:

SELECT MyDate, Median(iif(MyValue=0,-1,MyValue)) as Expr1 FROM MyTable GROUP BY MyDate

Then my returned recordset will look like this:

8/1     1                    (Median of (-1,1,1,3,5) would be 1)

I hope this helps.  If you have any other questions, feel free to ask.  :)
Avatar of hawkeyex
hawkeyex

ASKER

Thanks, routineint

One more thing:

SELECT SUM(IIf(AvgLTCTotalColi!Expr1="","",Median("[001A (LTC)]","[001A (LTC)]![Total Coliform #/100ml]"))) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM AvgLTCTotalColi
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]));

When I run this, it returns a VBA error that says:

"Runtime Error '3075':

Syntax error in query expression '[[001A (LTC)]![Total Coliform #/100ml]]'.

Can someone elaborate this? I will award points soon after this is resolved. This has been a challenge to resolve this all week.

Thanks!

Hawkeye-X
hi hawkeyex,


Im having a little difficulty with this:

  [001A (LTC)]![Total Coliform #/100ml]

is [001A (LTC)] the name of a query in your db??
is [Total Coliform #/100ml] the name of a field in the query [001A (LTC)] ?

Alan




Try  encapsulating the field called date in square brackets, Date is a reserverd word.

WHERE ((([001A (LTC)].[Date]) Between [Start Date] And [End Date]))

Alan
hi hawkeyex,


Im having a little difficulty with this:

  [001A (LTC)]![Total Coliform #/100ml]

is [001A (LTC)] the name of a query in your db??
is [Total Coliform #/100ml] the name of a field in the query [001A (LTC)] ?

Alan

No. [001A (LTC)] is actually a table in the db.
And [Total Coliform #/100ml] is the name of the field in the table [001A (LTC)]

Hope that helps.
Also when the VBA error occured, the debug showed this as the source of the error:

  Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];")

Your syntax looks good

Dim fldName As String
Dim tName As String
tName = "MyTable"
fldName = "MyField"

Dim sql As String
sql = "SELECT [" & fldName & _
            "] FROM [" & tName & "] WHERE [" & fldName & _
            "] IS NOT NULL ORDER BY [" & fldName & "];"

Debug.Print sql


returns:
SELECT [MyField] FROM [MyTable] WHERE [MyField] IS NOT NULL ORDER BY [MyField];

have tName and fldName variable got values?
Do any of your fieldnames have apostrophes in the name - LOL
Are you replacing the quotes used in the Format(Date,"yyyy-mm") with single apostrophes
Format(Date,'yyyy-mm')
VBA will interperet quotes as the end of the sql string



Alan






Database Object Naming Conventions
http://www.microsoft-accesssolutions.co.uk/naming_conv.htm

ACC: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q109/3/12.asp&NoWebContent=1

What is AvgLTCTotalColi?

The table [001A (LTC)] is not being referenced in the query, so unless AvgLTCTotalColi is a query returning that field, you will not be able to reference it as the SQL is now.  Perhaps if you explain a little about the purpose of the query and the structure of the relevant tables, we can rebuidl it for you.
OK.

What I am trying to do is convert an Excel formula:

=IF('CURRENT DMR'!P35="","",MEDIAN('CURRENT DMR'!P4:P34))

The 'CURRENT DMR'!P35 is actually [AvgLTCTotalColi] in terms of Access

The query for [AvgLTCTotalColi] currently looks like this:

SELECT Avg([001A (LTC)]![Total Coliform #/100ml]) AS Expr1, Format(Date,"yyyy-mm") AS Expr2 FROM [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

And 'Current DMR!P4:P34' is obviously the [001A (LTC)].[Total Coliform #/1000] restricted by date (thus needing the where subset of the query)

I hpoe these info helps a little bit.

Thanks!

Hawkeye-X
Still waiting..
Me too - LOL

is [001A (LTC)] the name of a query in your db??
is [Total Coliform #/100ml] the name of a field in the query [001A (LTC)] ?
have tName and fldName variables got values?
Do any of your fieldnames have apostrophes in the name?

Alan
I believe I answered your question above..

I'll repeat:

Q: is [001A (LTC)] the name of a query in your db??

A: No, it's actually a table.

Q: [Total Coliform #/100ml] the name of a field in the query [001A (LTC)] ?

A: See above - it's actually a field in the table, not the query.

Q: have tName and fldName variables got values?

A: I'm not sure. I believe it's supposed to be [001A (LTC)] for tName and [Total Coliform #/100ml] for fName

Q: Do any of your fieldnames have apostrophes in the name?

A: As far as I know, no. Parathesis, sure. Spaces, sure. No apostrophes though.

Thanks again!

Hawkeye-X
Oops. Sorry, the other answers must have disappeared. I hope the above one answers yours.

You really need to encapsulate Date as a fieldname ([Date]) - otherwise, Access tries to parse it as a function. Also, if you include Format([Date], "yyyy-mm") in your select and Group-By clauses, you will get separate records for each month for which any records exist between start date and end date - is that what you want? so if Start date is 1/1/04, and end date is 6/30/04, you'll get a record for 2004-01, 2004-02, 2004-03 etc., provided at least 1 reading took place in each month.

-ef
This is what the query currently looks like now, with encapsulation suggestions:

SELECT SUM(IIf(AvgLTCTotalColi!Expr1="","",Median("[001A (LTC)]","[001A (LTC)]![Total Coliform #/100ml]"))) AS Expr3, Format(Date,'yyyy-mm') AS Expr4
FROM AvgLTCTotalColi
WHERE ((([001A (LTC)].[Date]) Between [Start Date] And [End Date]));
Is there a way for me to mail the DB so you can look at it?
Hi hawkeyex

I got it working here!

Median function expects string variable and no square bracket as they are added by vba


SELECT IIf(AvgLTCTotalColi!Expr1="","",Median('001A (LTC)','Total Coliform #/100ml')) AS Expr3, Format(Date,"yyyy-mm") AS Expr4
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"),IIf(AvgLTCTotalColi!Expr1="","",Median('001A (LTC)','Total Coliform #/100ml'))


Alan
ASKER CERTIFIED SOLUTION
Avatar of Alan Warren
Alan Warren
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Alan! It worked just fine. Now to finish the job.....
Success with your app hawkeyex

Alan
Alan, one more thing - CYM. Thanks!