Median Function Problem w/ select statement

R

Robert_DubYa

Please forgive my ignorance as I am new to SQL and Modules. I have a module
that I got off this site that is used to return the median out of a list. My
problem is I can not get my select statement correct. I know the module
works (see below for the module) as I can run it and get the median for the
table the module is based off of. Please help me with the select portion in
the second query. My desired results (from data provded below the module)
would be a query that has results of:

Expr1 test

8 a
7.5 b

MODULE:
Option Compare Database

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As String

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function


Table ("tblTestData"):

Field1 test
5 b
5 b
5 b
6 b
7 b
8 b
9 b
10 b
11 b
11 b
2 a
3 a
5 a
5 a
8 a

Query 1 ("qryTestQuery1"):

SELECT DISTINCT test
FROM tblTestData;

Query2 ("qryTestQuery2"):

SELECT [qryTestQuery1].test, DMedian("Field1","tblTestData","'test' = " &
[test]) AS Expr1
FROM qryTestQuery1;

Your help is greatly apperciated,
Robert Wainwright
 
R

Roger Carlson

I believe you problem lies with the Where clause in the DMedian call. First
of all, you have quotes around the field name ('test') and secondly, you're
Where clause needs quotes delimiting the variable.

Try this:

SELECT [qryTestQuery1].test, DMedian("Field1","tblTestData","test = '" &
[test] & "'") AS Expr1
FROM qryTestQuery1;

BTW, this function is very similar to a sample on my website
(www.rogersaccesslibrary.com) called: "Median.mdb". I thought at first it
was mine, but it wasn't. But it's interesting to compare the two.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Robert_DubYa said:
Please forgive my ignorance as I am new to SQL and Modules. I have a module
that I got off this site that is used to return the median out of a list. My
problem is I can not get my select statement correct. I know the module
works (see below for the module) as I can run it and get the median for the
table the module is based off of. Please help me with the select portion in
the second query. My desired results (from data provded below the module)
would be a query that has results of:

Expr1 test

8 a
7.5 b

MODULE:
Option Compare Database

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As String

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function


Table ("tblTestData"):

Field1 test
5 b
5 b
5 b
6 b
7 b
8 b
9 b
10 b
11 b
11 b
2 a
3 a
5 a
5 a
8 a

Query 1 ("qryTestQuery1"):

SELECT DISTINCT test
FROM tblTestData;

Query2 ("qryTestQuery2"):

SELECT [qryTestQuery1].test, DMedian("Field1","tblTestData","'test' = " &
[test]) AS Expr1
FROM qryTestQuery1;

Your help is greatly apperciated,
Robert Wainwright
Click to expand...
 
R

Robert_DubYa

THANK YOU Roger!!!!!!!!!!!

Roger Carlson said:
I believe you problem lies with the Where clause in the DMedian call. First
of all, you have quotes around the field name ('test') and secondly, you're
Where clause needs quotes delimiting the variable.

Try this:

SELECT [qryTestQuery1].test, DMedian("Field1","tblTestData","test = '" &
[test] & "'") AS Expr1
FROM qryTestQuery1;

BTW, this function is very similar to a sample on my website
(www.rogersaccesslibrary.com) called: "Median.mdb". I thought at first it
was mine, but it wasn't. But it's interesting to compare the two.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Robert_DubYa said:
Please forgive my ignorance as I am new to SQL and Modules. I have a module
that I got off this site that is used to return the median out of a list. My
problem is I can not get my select statement correct. I know the module
works (see below for the module) as I can run it and get the median for the
table the module is based off of. Please help me with the select portion in
the second query. My desired results (from data provded below the module)
would be a query that has results of:

Expr1 test

8 a
7.5 b

MODULE:
Option Compare Database

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As String

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function


Table ("tblTestData"):

Field1 test
5 b
5 b
5 b
6 b
7 b
8 b
9 b
10 b
11 b
11 b
2 a
3 a
5 a
5 a
8 a

Query 1 ("qryTestQuery1"):

SELECT DISTINCT test
FROM tblTestData;

Query2 ("qryTestQuery2"):

SELECT [qryTestQuery1].test, DMedian("Field1","tblTestData","'test' = " &
[test]) AS Expr1
FROM qryTestQuery1;

Your help is greatly apperciated,
Robert Wainwright
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

median function 8
Median Calculation, Dates, & Group By 1
Problem with Median Calculation / VBA Script 2
Appending problem after splitting field 2
Median 6
Median calculation 2
median function problem 7
median function on parameter query 6

Top