median function on parameter query

[stef.pillaert]

Hello,
I'm looking for a median function in Access for using it in querie
with "group by".
I did find a few examples of good VB-code, where I even can suppl
optional criteria. This way it is possible to use it on "grou
by"-queries.
They work fine, as long a I use the provided functions on tables o
queries without parameters.
However, I want to be able to use those median functions also on
query with parameters, as you can with the "Avg"-function provided b
Access. Anyone an idea how to do this?
Here is an example of such a median function I found (sorry, don'
remember the author...). I would like to use it with "TableName
referring to a query with parameters.

Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Double
On Error GoTo Err_DMedian
'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY "
FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
' Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low an
high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactl
in
'the middle.
rs.Move Int(RowCount / 2)
DMedian = rs(FieldName)
End If

Exit_DMedian:
Exit Function

Err_DMedian:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_DMedian
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_DMedian
Else
MsgBox Err.Description
Resume Exit_DMedian
End If
End Functio

 

[Tom Ellison]

Dear Stef:

Have you tried using this function with the Criteria set to be the GROUP BY
column(s). That should get it for you.

I don't see where having parameters in the query that uses this function
would necessarily prevent it from working.

I suggest you write the query you want to end up having, but using AVG()
instead. Perhaps then I could see just what difficulty you might have.

Tom Ellison

 

[stef.pillaert]

No, I did mean that the query I'm calling has parameters. Let me reduc
it to a simple form: I have a "query1" like this (with parameters) tha
I created in ACCESS. The query1 just selects a group of records from
huge table named "scores", asking me for which group I want th
scores.:

PARAMETERS [Geef groep:] Long;
SELECT scores.scoreID, scores.groep, scores.score
FROM scores
WHERE (((scores.groep)=[Geef groep:]));

When I want to calculate the median of scores based on this query, i
doesn't work:

SELECT query1.groep, Avg(query1.score) AS GemVanscore
DMedian("score","query1","[groep] = " & [groep]) AS Mediaan
FROM query1
GROUP BY query1.groep;

It asks me "Geef groep:" as expected, but then I get a message tha
query1 expects 1 parameter (which is logical). So the behaviour o
DMedian is different then Avg: whit Avg it is possible to calcultat
the average on a parameter-query, but how do I do this with DMedian? I
would really be helpfull to have a Median function that works as simpl
as Avg does...

Thanks for any help,
Ste

 

[Tom Ellison]

Dear Stef:

Your use of the DMedian function references Query1. The function invokes
that query, and that query requires a parameter. This is the source of your
problem.

Now, your invocation of DMedian provides a filter (the 3rd parameter).
Therefore, I recommend you create a new query, one much like Query1, but
without the parameter:

SELECT scores.scoreID, scores.groep, scores.score
FROM scores

Then change your Query2 so the above query is referenced as the second
parameter in the call to DMedian.

Can you see that your DMedian call already contains the filter (the 3rd
parameter) that is duplicated by the parameter in Query1? Can you see this
is a potential source of conflict, and is at best unnecessary?

Tom Ellison

 

[stef.pillaert]

I understand the conflict, and I understand now that I can solve it b
putting the parameters in the calling query instead of in query1. But
hoped there would be a way to make a median function that acts the sam
way as Avg: Avg works on query1 (even with the parameters in it), an
DMedian doesn't...

Thanks for the help,

Ste

 

[Tom Ellison]

Dear Stef:

DMedian is not one of the built in aggregate functions. When you invoke
Avg() it works within the context of the query's grouping and filtering.
When you invoke the custom function DMedian it cannot do so. You must pass
the desired filtering to this query.

To expect to be able to use a parameter query for the source of the DMedian
seems a bit severe. It isn't so much that DMedian doesn't work like Avg
(none of the domain functions are close replacements for aggregate
functions) but that you must specify to it the data source and filtering it
is to use. This filtering is not compatible with the parameter in your
source query.

So, did you try my suggestion? Did it help?

Tom Ellison

 

[stef.pillaert]

Yes, thanks for all the help. I have to make some changes to my databas
to make things work, but in principle, it is OK.

Ste