Median Calculation in Access Reports

[Guest]

Hi everyone,

On our aging accounts reports for sales reps, I would like to calculate the
median aging field values on each rep's list. I tried placing Median([Aging])
in the Rep footer, alongside Sum([Open Balance]).

Opening the report, Access asks for the Median parameter but calculates Sum().

Looking into Access help, I could find no reference to Median(). On the
other hand, Microsoft provides a complete discussion of Sum().

Have I missed something, or does Access not provides for median calculation?

 

[Guest]

I don't believe there's a median function available in Access. Try copying
the function located at
http://www.fabalou.com/Access/Modules/recordset_median.asp into your report.

Implement it by using this as the controlsource property for the textbox
used to display the median for your aging field.

=MedianOfRst([RecordSource],"Aging")

 

[Guest]

Thanks, I'll try it later today.

Meanwhile, given all the bells and whistles MSFT marketing has added over
the years to sell Office updates, I wonder why they leave out median and
mode, two statisticals giving quick analytical angles into stacks of data?

I don't believe there's a median function available in Access. Try copying
the function located at
http://www.fabalou.com/Access/Modules/recordset_median.asp into your report.

Implement it by using this as the controlsource property for the textbox
used to display the median for your aging field.

=MedianOfRst([RecordSource],"Aging")

Hi everyone,

On our aging accounts reports for sales reps, I would like to calculate the
median aging field values on each rep's list. I tried placing Median([Aging])
in the Rep footer, alongside Sum([Open Balance]).

Opening the report, Access asks for the Median parameter but calculates Sum().

Looking into Access help, I could find no reference to Median(). On the
other hand, Microsoft provides a complete discussion of Sum().

Have I missed something, or does Access not provides for median calculation?

 

[George Nicholson]

Access has never provided much in the way of statistical analysis, its focus
has always been data storage. Anything beyond simple reports or analysis has
always been left up to Excel or 3rd party tools.

Thanks, I'll try it later today.

Meanwhile, given all the bells and whistles MSFT marketing has added over
the years to sell Office updates, I wonder why they leave out median and
mode, two statisticals giving quick analytical angles into stacks of data?

I don't believe there's a median function available in Access. Try
copying
the function located at
http://www.fabalou.com/Access/Modules/recordset_median.asp into your
report.

Implement it by using this as the controlsource property for the textbox
used to display the median for your aging field.

=MedianOfRst([RecordSource],"Aging")

Hi everyone,

On our aging accounts reports for sales reps, I would like to calculate
the
median aging field values on each rep's list. I tried placing
Median([Aging])
in the Rep footer, alongside Sum([Open Balance]).

Opening the report, Access asks for the Median parameter but calculates
Sum().

Looking into Access help, I could find no reference to Median(). On the
other hand, Microsoft provides a complete discussion of Sum().

Have I missed something, or does Access not provides for median
calculation?

 

[Guest]

Any recommendation on 3rd-party add-ins for stats?

Access has never provided much in the way of statistical analysis, its focus
has always been data storage. Anything beyond simple reports or analysis has
always been left up to Excel or 3rd party tools.

Thanks, I'll try it later today.

Meanwhile, given all the bells and whistles MSFT marketing has added over
the years to sell Office updates, I wonder why they leave out median and
mode, two statisticals giving quick analytical angles into stacks of data?

I don't believe there's a median function available in Access. Try
copying
the function located at
http://www.fabalou.com/Access/Modules/recordset_median.asp into your
report.

Implement it by using this as the controlsource property for the textbox
used to display the median for your aging field.

=MedianOfRst([RecordSource],"Aging")

:

Hi everyone,

On our aging accounts reports for sales reps, I would like to calculate
the
median aging field values on each rep's list. I tried placing
Median([Aging])
in the Rep footer, alongside Sum([Open Balance]).

Opening the report, Access asks for the Median parameter but calculates
Sum().

Looking into Access help, I could find no reference to Median(). On the
other hand, Microsoft provides a complete discussion of Sum().

Have I missed something, or does Access not provides for median
calculation?

 

[George Nicholson]

I have used Access Statistics from FMS. (http://fmsinc.com/) but I really
haven't used it enough to recommend, just point out its existence. (I do use
& recommend their Access Analyzer, however.)

One big thing in its favor: it is an Access add-in, so it does it's thing
within Access, allowing you to query result tables directly, rather than
having to export data to an external stat package, process & then import
back in. Ugh.

One (little?) thing against: the Trial version allowed you to apply Median
to a Date field, but the current "full" package will not ("...must be
numeric field..."!!). You can convert date to double and it will allow it
(..but I thought a Date *were* Doubles...). They know about this, but not
sure where it stands on their to-do list. More of an annoyance than anything
else.

Since I'm pretty sure their people read these groups at least occasionally,
maybe this will give them a nudge

HTH,

Any recommendation on 3rd-party add-ins for stats?

Access has never provided much in the way of statistical analysis, its
focus
has always been data storage. Anything beyond simple reports or analysis
has
always been left up to Excel or 3rd party tools.

Thanks, I'll try it later today.

Meanwhile, given all the bells and whistles MSFT marketing has added
over
the years to sell Office updates, I wonder why they leave out median
and
mode, two statisticals giving quick analytical angles into stacks of
data?

:

I don't believe there's a median function available in Access. Try
copying
the function located at
http://www.fabalou.com/Access/Modules/recordset_median.asp into your
report.

Implement it by using this as the controlsource property for the
textbox
used to display the median for your aging field.

=MedianOfRst([RecordSource],"Aging")

:

Hi everyone,

On our aging accounts reports for sales reps, I would like to
calculate
the
median aging field values on each rep's list. I tried placing
Median([Aging])
in the Rep footer, alongside Sum([Open Balance]).

Opening the report, Access asks for the Median parameter but
calculates
Sum().

Looking into Access help, I could find no reference to Median(). On
the
other hand, Microsoft provides a complete discussion of Sum().

Have I missed something, or does Access not provides for median
calculation?