It looks like you are "committing spreadsheet" with your Access database. You should really not
have separate columns that describe similar data. That being said, look at KB article 198571 as a
guide:
How to call Excel functions
http://support.microsoft.com/?id=198571
For example, you can use a variation of the example they provided in Example 1. For this code to
work, you must set a reference to the Microsoft Excel Object Library. My variation shows passing
(5) sales values into the function. You'll need to extend this for the number of columns that
you have, keeping in mind that this is not optimum database design. Note: This code does not
include any error handling or data validation.
Option Compare Database
Option Explicit
Function CalcMedian(Sales1 As Currency, Sales2 As Currency, _
Sales3 As Currency, Sales4 As Currency, _
Sales5 As Currency) As Single
Dim objExcel As Excel.Application
Set objExcel = CreateObject("Excel.Application")
CalcMedian = objExcel.Application.Median _
(Sales1, Sales2, Sales3, Sales4, Sales5)
objExcel.Quit
Set objExcel = Nothing
End Function
To call this function from a query, create a new query. Dismiss the Add table dialog without
selecting any tables. Click on View > SQL View in query design. Copy and paste the following SQL
statement into the SQL view:
SELECT tblSales.Date,
CalcMedian([Sales1],[Sales2],[Sales3],[Sales4],[Sales5]) AS Median
FROM tblSales;
Substitute the name of your table where I show "tblSales" above. Then run the query.
Tom
______________________________________________
I posted this question before, but I didn't clarify
exactly what I wanted.
I need to calculate the median of data across rows for a
set of data in Access 2002. The data is imported into
tables and I can read the data into a query.
For example the query is set up as follows:
Date Sales1 Sales2 Sales3 Sales4 .... Sales9
1/1/04 20.00 19.00 15.00 12.00 24.00
1/4/04 15.00 12.00 9.00 25.00 12.00
.....
How do I calculate the median for the sales across the
rows per day?
