[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2279
  • Last Modified:

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
0
hawkeyex
Asked:
hawkeyex
  • 11
  • 8
  • 3
  • +1
1 Solution
 
Steve BinkCommented:
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?
0
 
Alan WarrenCommented:
Looks to me like it should work fine if you just remove the Group BY clause

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

Alan
0
 
Steve BinkCommented:
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.  :)
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
hawkeyexAuthor Commented:
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
0
 
Alan WarrenCommented:
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




0
 
Alan WarrenCommented:
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
0
 
hawkeyexAuthor Commented:
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.
0
 
hawkeyexAuthor Commented:
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 & "];")
0
 
Alan WarrenCommented:

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

0
 
Steve BinkCommented:
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.
0
 
hawkeyexAuthor Commented:
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
0
 
hawkeyexAuthor Commented:
Still waiting..
0
 
Alan WarrenCommented:
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
0
 
hawkeyexAuthor Commented:
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
0
 
hawkeyexAuthor Commented:
Oops. Sorry, the other answers must have disappeared. I hope the above one answers yours.

0
 
Eric FlammSenior ConsultantCommented:
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
0
 
hawkeyexAuthor Commented:
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]));
0
 
hawkeyexAuthor Commented:
Is there a way for me to mail the DB so you can look at it?
0
 
Alan WarrenCommented:
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
0
 
Alan WarrenCommented:
Hi hawkeyex,


You can do this with one query with a sub-select From clause

SELECT
  IIf(SQ.Expr1="","",Median('001A (LTC)','Total Coliform #/100ml')) AS Expr3,
  Format(Date,"yyyy-mm") AS Expr4
FROM
  (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")
  ) SQ,
     [001A (LTC)]

WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"), IIf(SQ.Expr1="","",Median('001A (LTC)','Total Coliform #/100ml'));


Alan
0
 
hawkeyexAuthor Commented:
Thanks Alan! It worked just fine. Now to finish the job.....
0
 
Alan WarrenCommented:
Success with your app hawkeyex

Alan
0
 
hawkeyexAuthor Commented:
Alan, one more thing - CYM. Thanks!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 11
  • 8
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now