Median & Mode Calculation

  • Thread starter Thread starter mfoley.foley
  • Start date Start date
M

mfoley.foley

I've been unsuccessful finding the median & mode
functions in Access. They are available in Excel, but
it's not possible to use Excel when the number of records
exceeds 60,000. Does anyone know how to calculate these
basic statitical functions in Access?
 
If you've already determined that you have more values than Excel can
handle, you won't be able to use a reference in Access to Excel's function.
Another alternative would be to write your own functions. As I recall, a
"median" is that value which lies exactly in the middle, after sorting
ascending or descending (if there's an odd number of input values --
otherwise, I believe it's the average of the two closest to the middle).

The mode is that value which occurs most frequently ... but there can be
many "most frequently-occurring values".
 
How would I do either of these options (reference an
Excel function in Access or write my own function)?
Would I do this as an Expression? I don't know SQL?
 
I've been unsuccessful finding the median & mode
functions in Access. They are available in Excel, but
it's not possible to use Excel when the number of records
exceeds 60,000. Does anyone know how to calculate these
basic statitical functions in Access?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can use OLE Automation with Excel.

Scenario:

1. Open Excel as an Automation server.
2. Run query that uses Excel functions.
3. Close Excel Automation server.

1. Read the Access help article on CreateObject Function and
GetObject Function. These tell you how to set up an Automation
server. Set the object variable as a global or module-level variable.

2. Create the query with a call to an Access user-defined function
(UDF). That function will use the Automation object you created in 1.
to pass the required parameters to the Excel functions & return the
results to the query. You can use the UDF call like this:

SELECT col_1, col_2, functionName(param1, param2)
FROM tableName
.... etc. ...

3. After the query runs, set the Automation object to Nothing.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFY5FoechKqOuFEgEQJ1ZQCeJdGm1DCA6YQplVyk+dMmM5ursHsAoJ67
JMNb1CmlYt3GQbwBu2XUxoIp
=q6Bn
-----END PGP SIGNATURE-----
 
Marilyn

Did I miss something? I thought your original post said you could NOT use
Excel's function because you had too many rows.

Jeff Boyce
<Access MVP>
 
[email protected] said:
I've been unsuccessful finding the median & mode
functions in Access. They are available in Excel, but
it's not possible to use Excel when the number of records
exceeds 60,000. Does anyone know how to calculate these
basic statitical functions in Access?
Click to expand...

Public Sub ExcelFunctionExample()

'In Access, type "cntl-g", to bring up the Visual Basic Editor
'Then click Tools>References (while in the Visual Basic Editor)
'Scroll down until you find the "Microsoft Excel v.n Object Library,
' where v.n = your particular version number for MS Excel.
'Check the box next to that Object Library, and click OK.
'Paste the this entire VBA Sub into a Module, and excute it.

Debug.Print Excel.WorksheetFunction.Median(32, 42, 56)


'Note: The Median function works, however trying the Mode function
' throws an error.
' Run-Time Error '1004' Unable to set Mode Property of the
' WorksheetFunction Class.
' I Googled around for an explanation, but could not find one
' for certain, though I believe it's because Mode requires
' a Range Object. If you know how to make the Range Object
' Provide a list of specified values, in VBA, it might work.

End Sub
 
Oops, that's right . . . Calling on Excel's Functions via VBA, etc., will
only run into the Row Limitation.

My apologies.
 
Back
Top