Excel Function in Access

[Guest]

I'm trying to use an Excel Function (Median and Mode) in Access. I was able
to add the Excel reference in VBA mode and see the worksheet function, but
I'm not sure how to use this. Specifically, I want to run the Median and
Mode function to a set of records that is in an Access table. How do I do
this?

 

[lgalumbres]

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs in
Field1 and the main query grabs the highest count from the subquery..
giving you the MODE.

Cheers!
-Lem

 

[Guest]

Thanks for the link and the tip. I wanted to keep it relatively simple by
using the Excel functions as the file needs to be audited. Any chance of
using the Excel functions?

 

[Douglas J. Steele]

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.

 

[Guest]

That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard time
implementing it. I keep getting a ?#Name? error.

thanks.

What

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel 10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in. If
you're *really* intent on using it, you can access it from automation, see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the link and the tip. I wanted to keep it relatively simple by
using the Excel functions as the file needs to be audited. Any chance of
using the Excel functions?

 

[Douglas J. Steele]

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard time
implementing it. I keep getting a ?#Name? error.

thanks.

What

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for the link and the tip. I wanted to keep it relatively simple
by
using the Excel functions as the file needs to be audited. Any chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs in
Field1 and the main query grabs the highest count from the subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access. I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table. How
do I
do
this?

 

[Guest]

Great! that gave me a few more ideas to work with. Thanks a bunch

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard time
implementing it. I keep getting a ?#Name? error.

thanks.

What

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively simple
by
using the Excel functions as the file needs to be audited. Any chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs in
Field1 and the main query grabs the highest count from the subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access. I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table. How
do I
do
this?

 

[dbahooker]

usei subqueries; it is 100 times more powerful than anything that Excel
can do.

-Aaron

 

[Guest]

HI Doug,

The function works great. However, I have one more question. When I try to
use the Criteria argument, I haven't had much luck. I tried using the SQL
query without the Where. This is what I had and added a field called
Criteria to your database ((DiceRolls.[Criteria])="Code 1"). But got an
error #name?. So I changed it to "Criteria" = "Code 1". But now the median
is blank. How should the Criteria argument look like? This is what I have
=DMedian("Total","DiceRolls","Criteria"="Code 1"). Also would the =DAvg also
allow for Criteria selection?

Thanks,
Fred

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard time
implementing it. I keep getting a ?#Name? error.

thanks.

What

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins (which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively simple
by
using the Excel functions as the file needs to be audited. Any chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs in
Field1 and the main query grabs the highest count from the subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access. I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table. How
do I
do
this?

 

[Douglas J. Steele]

=DMedian("Total","DiceRolls","Criteria='Code 1'").

If you're trying to pass a variable, you'd use

=DMedian("Total","DiceRolls","Criteria='" & variable & "'").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI Doug,

The function works great. However, I have one more question. When I try
to
use the Criteria argument, I haven't had much luck. I tried using the SQL
query without the Where. This is what I had and added a field called
Criteria to your database ((DiceRolls.[Criteria])="Code 1"). But got an
error #name?. So I changed it to "Criteria" = "Code 1". But now the
median
is blank. How should the Criteria argument look like? This is what I have
=DMedian("Total","DiceRolls","Criteria"="Code 1"). Also would the =DAvg
also
allow for Criteria selection?

Thanks,
Fred

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to
do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard
time
implementing it. I keep getting a ?#Name? error.

thanks.

What
:

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins
(which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA
editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you
have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for
example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by
passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel
Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are
VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively
simple
by
using the Excel functions as the file needs to be audited. Any
chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As
T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs
in
Field1 and the main query grabs the highest count from the
subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access.
I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table.
How
do I
do
this?

 

[Guest]

Worked great in the form. How would you use this in a query? I used the
standard select query: grouping Products, and doing median on those in
another column. The Median column is showing the median of the entire
population as oppose for just that product. The Field was set up as
MedianPrice: DMedian("AvgOfUnitASP","tbl_SA_Data_Summary"). How do I use the
criteria argument to reference the Product (there are hundreds)? Following
the example from below:
Product Median
abc $200
ghi $800

=DMedian("Total","DiceRolls","Criteria='Code 1'").

If you're trying to pass a variable, you'd use

=DMedian("Total","DiceRolls","Criteria='" & variable & "'").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI Doug,

The function works great. However, I have one more question. When I try
to
use the Criteria argument, I haven't had much luck. I tried using the SQL
query without the Where. This is what I had and added a field called
Criteria to your database ((DiceRolls.[Criteria])="Code 1"). But got an
error #name?. So I changed it to "Criteria" = "Code 1". But now the
median
is blank. How should the Criteria argument look like? This is what I have
=DMedian("Total","DiceRolls","Criteria"="Code 1"). Also would the =DAvg
also
allow for Criteria selection?

Thanks,
Fred

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to
do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard
time
implementing it. I keep getting a ?#Name? error.

thanks.

What
:

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins
(which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA
editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you
have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for
example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by
passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel
Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are
VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively
simple
by
using the Excel functions as the file needs to be audited. Any
chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As
T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs
in
Field1 and the main query grabs the highest count from the
subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access.
I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table.
How
do I
do
this?

 

[dbahooker]

you can use domain aggregate functions; or even subqueries-- anywhere
that you want.

if you want it in the middle of a query; then copy and paste it into a
query

-Aaron

Worked great in the form. How would you use this in a query? I used the
standard select query: grouping Products, and doing median on those in
another column. The Median column is showing the median of the entire
population as oppose for just that product. The Field was set up as
MedianPrice: DMedian("AvgOfUnitASP","tbl_SA_Data_Summary"). How do I use the
criteria argument to reference the Product (there are hundreds)? Following
the example from below:
Product Median
abc $200
ghi $800

=DMedian("Total","DiceRolls","Criteria='Code 1'").

If you're trying to pass a variable, you'd use

=DMedian("Total","DiceRolls","Criteria='" & variable & "'").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI Doug,

The function works great. However, I have one more question. When I try
to
use the Criteria argument, I haven't had much luck. I tried using the SQL
query without the Where. This is what I had and added a field called
Criteria to your database ((DiceRolls.[Criteria])="Code 1"). But got an
error #name?. So I changed it to "Criteria" = "Code 1". But now the
median
is blank. How should the Criteria argument look like? This is what I have
=DMedian("Total","DiceRolls","Criteria"="Code 1"). Also would the =DAvg
also
allow for Criteria selection?

Thanks,
Fred

:

While I realize you said you wanted to use the functions from Excel, that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way to
do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard
time
implementing it. I keep getting a ?#Name? error.

thanks.

What
:

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins
(which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA
editor.
2. Choose Tools\References, and scroll down and check Microsoft Excel
10.0
Object Library (or whatever version of the Excel Object Library you
have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for
example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by
passing
appropriate data to the Excel function and returning the results, for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel
Add-in.
If
you're *really* intent on using it, you can access it from automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are
VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively
simple
by
using the Excel functions as the file needs to be audited. Any
chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber) AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable As
T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value occurs
in
Field1 and the main query grabs the highest count from the
subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in Access.
I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table.
How
do I
do
this?

 

[Douglas J. Steele]

Assuming you're got a table Products that contains (at least) Product and
Price, create an intermediary query that returns only the distinct Product
values:

SELECT DISTINCT Product
FROM Products

Call that qryDistinctProducts.

Create a second query based on that query.

Select the Product field, and add a computed field by typing the following
into the Field cell of an empty column in the grid:

MedianPrice: DMedian("Price","Products","Product = '" & [Product] & "'")

The SQL for this query will look like:

SELECT qryDistinctProducts.Product,
DMedian("Price","Products","Product = '" & [Product] & "'") AS MedianPrice
FROM qryDistinctProducts;

In Access 2000 and newer, it's possible to do this in a single query:

SELECT P.Product,
DMedian("Price","Products","Product = '" & P.[Product] & "'") AS MedianPrice
FROM
(SELECT DISTINCT Product
FROM Products) AS P


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Worked great in the form. How would you use this in a query? I used the
standard select query: grouping Products, and doing median on those in
another column. The Median column is showing the median of the entire
population as oppose for just that product. The Field was set up as
MedianPrice: DMedian("AvgOfUnitASP","tbl_SA_Data_Summary"). How do I use
the
criteria argument to reference the Product (there are hundreds)? Following
the example from below:
Product Median
abc $200
ghi $800

=DMedian("Total","DiceRolls","Criteria='Code 1'").

If you're trying to pass a variable, you'd use

=DMedian("Total","DiceRolls","Criteria='" & variable & "'").

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

HI Doug,

The function works great. However, I have one more question. When I
try
to
use the Criteria argument, I haven't had much luck. I tried using the
SQL
query without the Where. This is what I had and added a field called
Criteria to your database ((DiceRolls.[Criteria])="Code 1"). But got
an
error #name?. So I changed it to "Criteria" = "Code 1". But now the
median
is blank. How should the Criteria argument look like? This is what I
have
=DMedian("Total","DiceRolls","Criteria"="Code 1"). Also would the
=DAvg
also
allow for Criteria selection?

Thanks,
Fred

:

While I realize you said you wanted to use the functions from Excel,
that
actually introduces a lot of overhead.

Take a look at my October, 2005 "Access Answers" column in Pinnacle
Publication's "Smart Access" for an all-Access approach. You can
download
the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


That's a bit more complicated than I thought (Access challenged)
Basically, I have a table like this (with over 65000 rows)
Product Value1 Value2
abc $123 345
abc $235 654
ghi $789 951
ghi $987 954
abc $231 321
etc.
so I need a median on abc, ghi, etc. What would be the easiest way
to
do
this? I tried creating a function
(http://support.microsoft.com/kb/95918/en-us), but I'm having a hard
time
implementing it. I keep getting a ?#Name? error.

thanks.

What
:

Here's something retrieved from my file of common answers:

How to Use Excel Functions in Access:

It would be very nice if I could call an excel function like yield
directly
from a query.
Can this be done? If so, how?

Well, there are Excel functions and then there are Excel Add-ins
(which
unfortunately includes Yield). The core worksheet functions can be
included
as follows:

1. Open your database, type Ctrl+G to make sure you're in the VBA
editor.
2. Choose Tools\References, and scroll down and check Microsoft
Excel
10.0
Object Library (or whatever version of the Excel Object Library you
have)
3. Use the functions as Excel.WorkSheetFunction.FunctionName, for
example
if
you type:
?Excel.WorksheetFunction.Ceiling(5,3)
in the immediate pane and hit enter, it should return 6.
4. To use such functions in queries, make a wrapper function by
passing
appropriate data to the Excel function and returning the results,
for
example:
Function MyCeiling(ByVal iOne As Double, ByVal iTwo As Double)
MyCeiling = Excel.WorksheetFunction.Ceiling(iOne, iTwo)
End Function
5. Call that wrapper function from queries, as in:
Result: MyCeiling([field1],[field2])

The Yield function, however, is a special case, as it's an Excel
Add-in.
If
you're *really* intent on using it, you can access it from
automation,
see:
http://support.microsoft.com/?id=198571

Probably best to develop your own library of functions: there are
VB/VBA
versions out there for almost anything that you might need.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for the link and the tip. I wanted to keep it relatively
simple
by
using the Excel functions as the file needs to be audited. Any
chance
of
using the Excel functions?

:

Hi,

Check this out....

Shows how to calculate the median in a table in SQL and Access:
http://www.aspfaq.com/show.asp?id=2506

For MODE() Function on the other hand you can try something
like...

SELECT TOP 1 T2.Field1 AS MostFrequentValue, Max(T2.CountNumber)
AS
NumberOfTimes
FROM
(
Select T1.Field1, Count(Field1) As CountNumber FROM tblYourTable
As
T1
Group By T1.Field1
) AS T2
GROUP BY T1.Field1

Basically the sub query counts the number of times a value
occurs
in
Field1 and the main query grabs the highest count from the
subquery..
giving you the MODE.

Cheers!
-Lem

FredL wrote:
I'm trying to use an Excel Function (Median and Mode) in
Access.
I
was
able
to add the Excel reference in VBA mode and see the worksheet
function,
but
I'm not sure how to use this. Specifically, I want to run the
Median
and
Mode function to a set of records that is in an Access table.
How
do I
do
this?