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 ="","",Med ian([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= "","",Medi an([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("SE LECT [" & 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
Error shows up: "You tried to execute a query that does not include the specified expression 'IIf(AvgLTCTotalColi!Expr1
I don't understand what that error means. Can someone elaborate for me?
Code is below.
SELECT IIf(AvgLTCTotalColi!Expr1=
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("SE
"] 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
Looks to me like it should work fine if you just remove the Group BY clause
GROUP BY Format(Date,"yyyy-mm");
Alan
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,My Value)) 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. :)
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,My
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. :)
ASKER
Thanks, routineint
One more thing:
SELECT SUM(IIf(AvgLTCTotalColi!Ex pr1="","", Median("[0 01A (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
One more thing:
SELECT SUM(IIf(AvgLTCTotalColi!Ex
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
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
WHERE ((([001A (LTC)].[Date]) Between [Start Date] And [End Date]))
Alan
ASKER
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.
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.
ASKER
Also when the VBA error occured, the debug showed this as the source of the error:
Set ssMedian = MedianDB.OpenRecordset("SE LECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
Set ssMedian = MedianDB.OpenRecordset("SE
"] 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.
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.
ASKER
OK.
What I am trying to do is convert an Excel formula:
=IF('CURRENT DMR'!P35="","",MEDIAN('CUR RENT 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
What I am trying to do is convert an Excel formula:
=IF('CURRENT DMR'!P35="","",MEDIAN('CUR
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
ASKER
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
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
ASKER
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
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
ASKER
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
-ef
ASKER
This is what the query currently looks like now, with encapsulation suggestions:
SELECT SUM(IIf(AvgLTCTotalColi!Ex pr1="","", Median("[0 01A (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]));
SELECT SUM(IIf(AvgLTCTotalColi!Ex
FROM AvgLTCTotalColi
WHERE ((([001A (LTC)].[Date]) Between [Start Date] And [End Date]));
ASKER
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= "","",Medi an('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 (AvgLTCTot alColi!Exp r1="","",M edian('001 A (LTC)','Total Coliform #/100ml'))
Alan
I got it working here!
Median function expects string variable and no square bracket as they are added by vba
SELECT IIf(AvgLTCTotalColi!Expr1=
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm"),IIf
Alan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Alan! It worked just fine. Now to finish the job.....
Success with your app hawkeyex
Alan
Alan
ASKER
Alan, one more thing - CYM. Thanks!
SELECT IIf(AvgLTCTotalColi!Expr1=
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!Ex
FROM AvgLTCTotalColi, [001A (LTC)]
WHERE ((([001A (LTC)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");
Do you understand?