Solved

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

Posted on 2004-08-25
23
2,267 Views
Last Modified: 2011-09-20
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
Comment
Question by:hawkeyex
  • 11
  • 8
  • 3
  • +1
23 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11893261
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11893361
Looks to me like it should work fine if you just remove the Group BY clause

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

Alan
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11893381
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
 

Author Comment

by:hawkeyex
ID: 11893504
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11893604
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11893648
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
 

Author Comment

by:hawkeyex
ID: 11893742
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
 

Author Comment

by:hawkeyex
ID: 11893756
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11893923

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
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11893960
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
 

Author Comment

by:hawkeyex
ID: 11894109
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:hawkeyex
ID: 11894720
Still waiting..
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11894781
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
 

Author Comment

by:hawkeyex
ID: 11894888
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
 

Author Comment

by:hawkeyex
ID: 11894898
Oops. Sorry, the other answers must have disappeared. I hope the above one answers yours.

0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11894916
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
 

Author Comment

by:hawkeyex
ID: 11894991
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
 

Author Comment

by:hawkeyex
ID: 11895214
Is there a way for me to mail the DB so you can look at it?
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11895324
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
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 11895630
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
 

Author Comment

by:hawkeyex
ID: 11895723
Thanks Alan! It worked just fine. Now to finish the job.....
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 11895740
Success with your app hawkeyex

Alan
0
 

Author Comment

by:hawkeyex
ID: 11895880
Alan, one more thing - CYM. Thanks!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now