Median in group

[Scott]

I would like to find the medians in each group rather than in total.

Category

Leadtime
Overallleadtime

Record1
Record2
..
..
..
..
Record20

Group footer Median("qryName","Leadtime")
Median('qryName","Overallleadtime")


It just returns the medians of all records, not the records of each group.
Your advice is appreciated.

Thanks,

Scott

 

[Roger Carlson]

To my knowledge, Access does not have a built-in Median function. Where did
you get this? What version of Access?

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Median.mdb" which illustrates how to create your own Domain
Aggregate function called DMedian, which will allow you to specify a Where
condition so you can aggregate on groups.

--
--Roger Carlson
MS Access MVP
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

 

[Scott]

Roger,

I got the code from Microsoft on article ID 210581. It works correctly and
I tried it without grouping. I have no idea how to apply it in grouping on
a report. I believe my issue is similar to how to sum a field of records in
a group instead of whole recordset.

Scott

 

[Roger Carlson]

The median function in the article does not make any provision for grouping.
It finds the median of the entire dataset.

My function has a Where clause argument. If you put the field you are
grouping by in that argument, then you will get a Median for that group.

--
--Roger Carlson
MS Access MVP
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

 

[Scott]

Roger,

Thanks for your advice. I tried your program and have two issues in my
application.

1. If any record is null, the median calculation fails to work. Is there
any way to get round it under this situation.

2. I put it =DMedian97("qryLeadtime","Leadtime","Category") in the
control source of a text box under the group footer of a report and returned
incorrect result plus error message saying "Item not found in this
collection.". If I change the where clause to [Category]=ABC, it works
correctly. I cannot use it like that since I group Category in the report
and would like to get the median in each group.

Your further advice is appreciated.

Thanks,

Scott

 

[Roger Carlson]

1. How do you mean it fails to work? Do you get an error?

2. You need to supply the category as in the query examples:
=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "'")

This MAY solve both of your problems, depending on what the problem with the
Null is:

=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "' and
[Leadtime] Is Not Null")

--
--Roger Carlson
MS Access MVP
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

Roger,

Thanks for your advice. I tried your program and have two issues in my
application.

1. If any record is null, the median calculation fails to work. Is there
any way to get round it under this situation.

2. I put it =DMedian97("qryLeadtime","Leadtime","Category") in the
control source of a text box under the group footer of a report and returned
incorrect result plus error message saying "Item not found in this
collection.". If I change the where clause to [Category]=ABC, it works
correctly. I cannot use it like that since I group Category in the report
and would like to get the median in each group.

Your further advice is appreciated.

Thanks,

Scott

The median function in the article does not make any provision for
grouping.
It finds the median of the entire dataset.

My function has a Where clause argument. If you put the field you are
grouping by in that argument, then you will get a Median for that group.

--
--Roger Carlson
MS Access MVP
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


grouping
on records
in

 

[Scott]

Roger,

Many thanks for your further advice.

Access did not accept your suggestion statements and returned an error
message saying "The expression you entered contains invalid syntax. You may
have entered an operand without an operator.".

I looked at your 2k Median example database again. There are two modules:
one is ADO and function name = DMedian(FieldName,TableName,Criteria) and
another one is DAO and function name
=DMedian97(TableName,FieldName,Criteria). I imported both into my Access
2003 application. Is there any error I have made? Which function should I
use?

My report is based on a query that linked with a table. The report is
grouped by category and the Median function is applied to the footer of
category group. You further advice is highly appreciated.

Thanks,

Scott

1. How do you mean it fails to work? Do you get an error?

2. You need to supply the category as in the query examples:
=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] &
"'")

This MAY solve both of your problems, depending on what the problem with
the
Null is:

=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "' and
[Leadtime] Is Not Null")

--
--Roger Carlson
MS Access MVP
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

Roger,

Thanks for your advice. I tried your program and have two issues in my
application.

1. If any record is null, the median calculation fails to work. Is there
any way to get round it under this situation.

2. I put it =DMedian97("qryLeadtime","Leadtime","Category") in the
control source of a text box under the group footer of a report and returned
incorrect result plus error message saying "Item not found in this
collection.". If I change the where clause to [Category]=ABC, it works
correctly. I cannot use it like that since I group Category in the
report
and would like to get the median in each group.

Your further advice is appreciated.

Thanks,

Scott

The median function in the article does not make any provision for
grouping.
It finds the median of the entire dataset.

My function has a Where clause argument. If you put the field you are
grouping by in that argument, then you will get a Median for that
group.

--
--Roger Carlson
MS Access MVP
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


Roger,

I got the code from Microsoft on article ID 210581. It works
correctly
and
I tried it without grouping. I have no idea how to apply it in grouping
on
a report. I believe my issue is similar to how to sum a field of records
in
a group instead of whole recordset.

Scott

To my knowledge, Access does not have a built-in Median function.
Where
did
you get this? What version of Access?

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Median.mdb" which illustrates how to create your own
Domain
Aggregate function called DMedian, which will allow you to specify a
Where
condition so you can aggregate on groups.

--
--Roger Carlson
MS Access MVP
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


I would like to find the medians in each group rather than in
total.

Category

Leadtime
Overallleadtime

Record1
Record2
.
.
.
.
Record20

Group footer Median("qryName","Leadtime")
Median('qryName","Overallleadtime")


It just returns the medians of all records, not the records of each
group.
Your advice is appreciated.

Thanks,

Scott

 

[Roger Carlson]

OK, I wasn't reading your post closely enough. You have the order of
arguments wrong. It should be:

=DMedian("Leadtime","qryLeadtime","[Category] = '" & Category & "'")

This assumes that 1) qryLeadtime is the query being used as the Record
Source for the Report. 2) Leadtime is the field that you want to find the
median for, and 3) Category is the field you are grouping on and it is a
text field. If Category is a numeric field, it would be this:

=DMedian("Leadtime","qryLeadtime","[Category] = " & Category )

The only difference between DMedian and DMedian97 is that DMedian97 used
DAO. DMedian uses ADO. There is no need to import both of them.

If this doesn't help, I'm stuck too.

--
--Roger Carlson
MS Access MVP
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

Roger,

Many thanks for your further advice.

Access did not accept your suggestion statements and returned an error
message saying "The expression you entered contains invalid syntax. You may
have entered an operand without an operator.".

I looked at your 2k Median example database again. There are two modules:
one is ADO and function name = DMedian(FieldName,TableName,Criteria) and
another one is DAO and function name
=DMedian97(TableName,FieldName,Criteria). I imported both into my Access
2003 application. Is there any error I have made? Which function should I
use?

My report is based on a query that linked with a table. The report is
grouped by category and the Median function is applied to the footer of
category group. You further advice is highly appreciated.

Thanks,

Scott

1. How do you mean it fails to work? Do you get an error?

2. You need to supply the category as in the query examples:
=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] &
"'")

This MAY solve both of your problems, depending on what the problem with
the
Null is:

=DMedian97("qryLeadtime","Leadtime","[Category] = '" & [Category] & "' and
[Leadtime] Is Not Null")

--
--Roger Carlson
MS Access MVP
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

Roger,

Thanks for your advice. I tried your program and have two issues in my
application.

1. If any record is null, the median calculation fails to work. Is there
any way to get round it under this situation.

2. I put it =DMedian97("qryLeadtime","Leadtime","Category") in the
control source of a text box under the group footer of a report and returned
incorrect result plus error message saying "Item not found in this
collection.". If I change the where clause to [Category]=ABC, it works
correctly. I cannot use it like that since I group Category in the
report
and would like to get the median in each group.

Your further advice is appreciated.

Thanks,

Scott

The median function in the article does not make any provision for
grouping.
It finds the median of the entire dataset.

My function has a Where clause argument. If you put the field you are
grouping by in that argument, then you will get a Median for that
group.

--
--Roger Carlson
MS Access MVP
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


Roger,

I got the code from Microsoft on article ID 210581. It works
correctly
and
I tried it without grouping. I have no idea how to apply it in grouping
on
a report. I believe my issue is similar to how to sum a field of records
in
a group instead of whole recordset.

Scott

To my knowledge, Access does not have a built-in Median function.
Where
did
you get this? What version of Access?

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "Median.mdb" which illustrates how to create your own
Domain
Aggregate function called DMedian, which will allow you to specify a
Where
condition so you can aggregate on groups.

--
--Roger Carlson
MS Access MVP
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


I would like to find the medians in each group rather than in
total.

Category

Leadtime
Overallleadtime

Record1
Record2
.
.
.
.
Record20

Group footer Median("qryName","Leadtime")
Median('qryName","Overallleadtime")


It just returns the medians of all records, not the records of each
group.
Your advice is appreciated.

Thanks,

Scott