Median

T

ty

I'm using the following function which I found on another site.

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 and 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 exactly 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 Function

The problem I have is that I need to add another set of criteria to group
by. In addition to grouping by NU_USER_ID, I also need to group by Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] = '"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
 
D

Douglas J. Steele

MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")
 
T

ty

When I put it in the query, I get a message that says "You tried to execute a
query that doesn't include the specific expression 'DMedian("Time",.....)' as
part of an aggregate function.

I'm not sure how to resolve this. Your help is greatly appreciated. Thanks.

Douglas J. Steele said:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ty said:
I'm using the following function which I found on another site.

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 and 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 exactly 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 Function

The problem I have is that I need to add another set of criteria to group
by. In addition to grouping by NU_USER_ID, I also need to group by Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Show the SQL for your entire query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ty said:
When I put it in the query, I get a message that says "You tried to
execute a
query that doesn't include the specific expression 'DMedian("Time",.....)'
as
part of an aggregate function.

I'm not sure how to resolve this. Your help is greatly appreciated.
Thanks.

Douglas J. Steele said:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ty said:
I'm using the following function which I found on another site.

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 and 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 exactly 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 Function

The problem I have is that I need to add another set of criteria to
group
by. In addition to grouping by NU_USER_ID, I also need to group by
Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the
Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
Click to expand...
Click to expand...
 
T

ty

Below is the SQL code.

******
SELECT TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week AS Week2007, [Periods_ tbl].WeekNumber AS WeekNumber2007,
BuyerCount_tbl.CountOfQH_ID AS Count2007, Avg(TimeElapsed_byQuote.Time) AS
Avg2007,
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] = '"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' ") AS MedianYTD INTO
TimeElapsedAveByBuyer2007_tbl
FROM BuyerCount_tbl INNER JOIN (TimeElapsed_byQuote INNER JOIN [Periods_
tbl] ON TimeElapsed_byQuote.SubmitDay = [Periods_ tbl].Date2) ON
(BuyerCount_tbl.Week = [Periods_ tbl].Week) AND (BuyerCount_tbl.NU_USER_ID =
TimeElapsed_byQuote.NU_USER_ID) AND (BuyerCount_tbl.NU_UT_ID =
TimeElapsed_byQuote.NU_UT_ID)
WHERE (((TimeElapsed_byQuote.MinOfQD_ASSIGN_TS) Between [beg date] And [end
date]))
GROUP BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week, [Periods_ tbl].WeekNumber, BuyerCount_tbl.CountOfQH_ID
ORDER BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week;

**********
Douglas J. Steele said:
Show the SQL for your entire query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ty said:
When I put it in the query, I get a message that says "You tried to
execute a
query that doesn't include the specific expression 'DMedian("Time",.....)'
as
part of an aggregate function.

I'm not sure how to resolve this. Your help is greatly appreciated.
Thanks.

Douglas J. Steele said:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm using the following function which I found on another site.

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 and 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 exactly 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 Function

The problem I have is that I need to add another set of criteria to
group
by. In addition to grouping by NU_USER_ID, I also need to group by
Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the
Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
Click to expand...
Click to expand...
 
T

ty

Sorry, this is the SQL code

SELECT TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week AS Week2007, [Periods_ tbl].WeekNumber AS WeekNumber2007,
BuyerCount_tbl.CountOfQH_ID AS Count2007, Avg(TimeElapsed_byQuote.Time) AS
Avg2007,
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'" & [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![WeekNumber] = '" & [TimeElapsed_byQuote]![WeekNumber]
& "'") AS MedianYTD INTO TimeElapsedAveByBuyer2007_tbl
FROM BuyerCount_tbl INNER JOIN (TimeElapsed_byQuote INNER JOIN [Periods_
tbl] ON TimeElapsed_byQuote.SubmitDay = [Periods_ tbl].Date2) ON
(BuyerCount_tbl.NU_UT_ID = TimeElapsed_byQuote.NU_UT_ID) AND
(BuyerCount_tbl.NU_USER_ID = TimeElapsed_byQuote.NU_USER_ID) AND
(BuyerCount_tbl.Week = [Periods_ tbl].Week)
WHERE (((TimeElapsed_byQuote.MinOfQD_ASSIGN_TS) Between [beg date] And [end
date]))
GROUP BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week, [Periods_ tbl].WeekNumber, BuyerCount_tbl.CountOfQH_ID
ORDER BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week;


Douglas J. Steele said:
Show the SQL for your entire query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ty said:
When I put it in the query, I get a message that says "You tried to
execute a
query that doesn't include the specific expression 'DMedian("Time",.....)'
as
part of an aggregate function.

I'm not sure how to resolve this. Your help is greatly appreciated.
Thanks.

Douglas J. Steele said:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm using the following function which I found on another site.

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 and 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 exactly 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 Function

The problem I have is that I need to add another set of criteria to
group
by. In addition to grouping by NU_USER_ID, I also need to group by
Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the
Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

When you use aggregate functions such as Avg (or Sum or Count etc.), all the
other fields in the SELECT must appear in the GROUP BY.

You need to repeat the function call in the GROUP BY clause:

SELECT TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week AS Week2007, [Periods_ tbl].WeekNumber AS
WeekNumber2007,
BuyerCount_tbl.CountOfQH_ID AS Count2007, Avg(TimeElapsed_byQuote.Time) AS
Avg2007,
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'" & [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![WeekNumber] = '" & [TimeElapsed_byQuote]![WeekNumber]
& "'") AS MedianYTD INTO TimeElapsedAveByBuyer2007_tbl
FROM BuyerCount_tbl INNER JOIN (TimeElapsed_byQuote INNER JOIN [Periods_
tbl] ON TimeElapsed_byQuote.SubmitDay = [Periods_ tbl].Date2) ON
(BuyerCount_tbl.NU_UT_ID = TimeElapsed_byQuote.NU_UT_ID) AND
(BuyerCount_tbl.NU_USER_ID = TimeElapsed_byQuote.NU_USER_ID) AND
(BuyerCount_tbl.Week = [Periods_ tbl].Week)
WHERE (((TimeElapsed_byQuote.MinOfQD_ASSIGN_TS) Between [beg date] And [end
date]))
GROUP BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week AS Week2007, [Periods_ tbl].WeekNumber,
BuyerCount_tbl.CountOfQH_ID AS Count2007,
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'" & [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![WeekNumber] = '" & [TimeElapsed_byQuote]![WeekNumber]
& "'") AS MedianYTD
ORDER BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week;


BTW, something I didn't notice before. Time is a reserved word, so isn't a
good choice for a field name. If you cannot (or will not) change the field
name, at least enclose it in square brackets. Same holds true for Week. For
a good discussion about what names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ty said:
Sorry, this is the SQL code

SELECT TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week AS Week2007, [Periods_ tbl].WeekNumber AS
WeekNumber2007,
BuyerCount_tbl.CountOfQH_ID AS Count2007, Avg(TimeElapsed_byQuote.Time) AS
Avg2007,
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'" & [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![WeekNumber] = '" &
[TimeElapsed_byQuote]![WeekNumber]
& "'") AS MedianYTD INTO TimeElapsedAveByBuyer2007_tbl
FROM BuyerCount_tbl INNER JOIN (TimeElapsed_byQuote INNER JOIN [Periods_
tbl] ON TimeElapsed_byQuote.SubmitDay = [Periods_ tbl].Date2) ON
(BuyerCount_tbl.NU_UT_ID = TimeElapsed_byQuote.NU_UT_ID) AND
(BuyerCount_tbl.NU_USER_ID = TimeElapsed_byQuote.NU_USER_ID) AND
(BuyerCount_tbl.Week = [Periods_ tbl].Week)
WHERE (((TimeElapsed_byQuote.MinOfQD_ASSIGN_TS) Between [beg date] And
[end
date]))
GROUP BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week, [Periods_ tbl].WeekNumber,
BuyerCount_tbl.CountOfQH_ID
ORDER BY TimeElapsed_byQuote.NU_UT_ID, TimeElapsed_byQuote.NU_USER_ID,
[Periods_ tbl].Week;


Douglas J. Steele said:
Show the SQL for your entire query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


ty said:
When I put it in the query, I get a message that says "You tried to
execute a
query that doesn't include the specific expression
'DMedian("Time",.....)'
as
part of an aggregate function.

I'm not sure how to resolve this. Your help is greatly appreciated.
Thanks.

:

MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' AND
[TimeElapsed_byQuote]![Week_Number] = '" &
[TimeElapsed_byQuote]![Week_Number] & "'")

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm using the following function which I found on another site.

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 and
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 exactly
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 Function

The problem I have is that I need to add another set of criteria to
group
by. In addition to grouping by NU_USER_ID, I also need to group by
Week
(which is on another table called Periods). I'm using this
expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID]
=
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the
Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
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 calculation 2
Problem with Median Calculation / VBA Script 2
median function on parameter query 6
median function problem 7
my VBA code to calculate median values is broken. :( 3
dlookup and dao.recordset 4
median function 8
Median Function Problem w/ select statement 2

Top