Median Calculation in Access Reports

J

JLC

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?
 
J

JLC

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?

Analyst72 said:
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")

JLC said:
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?
 
G

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.



JLC said:
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?

Analyst72 said:
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")

JLC said:
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?
 
J

JLC

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

George Nicholson said:
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.



JLC said:
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?

Analyst72 said:
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?
 
G

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,


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

George Nicholson said:
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.



JLC said:
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?
 

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

Top