Hi.
I'm trying to create a function to get the median value of a series
of observations. I've found an excellent reference on a MS website on
how to construct a query to obtain the median value, but I'm having a
bit of difficulty getting the query "visible" to access. Whenever I put
the function into the design grid it fails "undefined function 'Median'
in expression."

I created the function by clicking Insert -> Module. Access then
provided me with a Module1 (how do I rename it?) and I pasted the text
for the function into the module.

I tried running it and initially I had some difficulties with
mismatching parameters (I passed a string instead of a table name).

Once I got the errors ironed out, there are now other objects in my
Visual Basic Object list under a tree of "Utility".

Basically, I have the function Median coded as it is demonstrated in
http://support.microsoft.com/?id=210581.

I then have a query to extract a relevant list of records from the
table:

SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
AS Age
FROM Listing
WHERE (((Listing.Status)="Pending Verification"))
ORDER BY DateValue(Fix([EndDate]));


Then I have the query that fails but is supposed to obtain the median
value:

SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
FROM[Listings Pending Verification Age List];

I'd appreciate any help someone can give me.

Thanks.

Thanks! That helped, although it turned out I had to lose the square
brackets entirely.

Now I get a table with multiple rows (one for each row in the table)
each row having the same value for the median age. Is there a way I
can write the query so that a single row (a single value) is returned?

Mike

Damian S wrote:
> Hi Mike,
>
> The write-up of the Median function in the article you posted wants the
> parameters as strings... you will need to call it like this if you are using
> the same function:
>
> Median("[Listings Pending Verification Age List]","[Age]")
>
> Damian.
>
>
> "MikeB" wrote:
>
> > Hi.
> > I'm trying to create a function to get the median value of a series
> > of observations. I've found an excellent reference on a MS website on
> > how to construct a query to obtain the median value, but I'm having a
> > bit of difficulty getting the query "visible" to access. Whenever I put
> > the function into the design grid it fails "undefined function 'Median'
> > in expression."
> >
> > I created the function by clicking Insert -> Module. Access then
> > provided me with a Module1 (how do I rename it?) and I pasted the text
> > for the function into the module.
> >
> > I tried running it and initially I had some difficulties with
> > mismatching parameters (I passed a string instead of a table name).
> >
> > Once I got the errors ironed out, there are now other objects in my
> > Visual Basic Object list under a tree of "Utility".
> >
> > Basically, I have the function Median coded as it is demonstrated in
> > http://support.microsoft.com/?id=210581.
> >
> > I then have a query to extract a relevant list of records from the
> > table:
> >
> > SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
> > AS Age
> > FROM Listing
> > WHERE (((Listing.Status)="Pending Verification"))
> > ORDER BY DateValue(Fix([EndDate]));
> >
> >
> > Then I have the query that fails but is supposed to obtain the median
> > value:
> >
> > SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
> > FROM[Listings Pending Verification Age List];
> >
> > I'd appreciate any help someone can give me.
> >
> > Thanks.
> >
> >

Hi Mike,

Any time you call a function in a query, you will receive one result for
every row returned by the query. If you want a single result returned, use a
query that returns a single row, or simply remove the from line (since you
aren't returning any other fields from the table in your example):

select Median("[Listings Pending Verification Age List]","[Age]") as mEnd

Hope this helps.

Damian.

"MikeB" wrote:

> Thanks! That helped, although it turned out I had to lose the square
> brackets entirely.
>
> Now I get a table with multiple rows (one for each row in the table)
> each row having the same value for the median age. Is there a way I
> can write the query so that a single row (a single value) is returned?
>
> Mike
>
> Damian S wrote:
> > Hi Mike,
> >
> > The write-up of the Median function in the article you posted wants the
> > parameters as strings... you will need to call it like this if you are using
> > the same function:
> >
> > Median("[Listings Pending Verification Age List]","[Age]")
> >
> > Damian.
> >
> >
> > "MikeB" wrote:
> >
> > > Hi.
> > > I'm trying to create a function to get the median value of a series
> > > of observations. I've found an excellent reference on a MS website on
> > > how to construct a query to obtain the median value, but I'm having a
> > > bit of difficulty getting the query "visible" to access. Whenever I put
> > > the function into the design grid it fails "undefined function 'Median'
> > > in expression."
> > >
> > > I created the function by clicking Insert -> Module. Access then
> > > provided me with a Module1 (how do I rename it?) and I pasted the text
> > > for the function into the module.
> > >
> > > I tried running it and initially I had some difficulties with
> > > mismatching parameters (I passed a string instead of a table name).
> > >
> > > Once I got the errors ironed out, there are now other objects in my
> > > Visual Basic Object list under a tree of "Utility".
> > >
> > > Basically, I have the function Median coded as it is demonstrated in
> > > http://support.microsoft.com/?id=210581.
> > >
> > > I then have a query to extract a relevant list of records from the
> > > table:
> > >
> > > SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
> > > AS Age
> > > FROM Listing
> > > WHERE (((Listing.Status)="Pending Verification"))
> > > ORDER BY DateValue(Fix([EndDate]));
> > >
> > >
> > > Then I have the query that fails but is supposed to obtain the median
> > > value:
> > >
> > > SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
> > > FROM[Listings Pending Verification Age List];
> > >
> > > I'd appreciate any help someone can give me.
> > >
> > > Thanks.
> > >
> > >
>
>

MikeB wrote:
> Hi.
> I'm trying to create a function to get the median value of a series
> of observations. I've found an excellent reference on a MS website on
> how to construct a query to obtain the median value, but I'm having a
> bit of difficulty getting the query "visible" to access. Whenever I put
> the function into the design grid it fails "undefined function 'Median'
> in expression."
>
> I created the function by clicking Insert -> Module. Access then
> provided me with a Module1 (how do I rename it?) and I pasted the text
> for the function into the module.
>
> I tried running it and initially I had some difficulties with
> mismatching parameters (I passed a string instead of a table name).
>
> Once I got the errors ironed out, there are now other objects in my
> Visual Basic Object list under a tree of "Utility".
>
> Basically, I have the function Median coded as it is demonstrated in
> http://support.microsoft.com/?id=210581.
>
> I then have a query to extract a relevant list of records from the
> table:
>
> SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
> AS Age
> FROM Listing
> WHERE (((Listing.Status)="Pending Verification"))
> ORDER BY DateValue(Fix([EndDate]));
>
>
> Then I have the query that fails but is supposed to obtain the median
> value:
>
> SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
> FROM[Listings Pending Verification Age List];
>
> I'd appreciate any help someone can give me.
>
> Thanks.


In:

http://groups.google.com/group/comp....b6eb508f22fa9d

Titled "Median of GROUP BY values," I show a SQL method for obtaining
the medians of query groups that does not involve calling a user-defined
VBA function. Also, the links refer back to the non-grouped case.

James A. Fortune
(E-Mail Removed)

Thanks, I ended up placing the median query in a field in another,
related query that calculated the average and then I got the single
field I wanted.

Mike

Damian S wrote:
> Hi Mike,
>
> Any time you call a function in a query, you will receive one result for
> every row returned by the query. If you want a single result returned, use a
> query that returns a single row, or simply remove the from line (since you
> aren't returning any other fields from the table in your example):
>
> select Median("[Listings Pending Verification Age List]","[Age]") as mEnd
>
> Hope this helps.
>
> Damian.
>
> "MikeB" wrote:
>
> > Thanks! That helped, although it turned out I had to lose the square
> > brackets entirely.
> >
> > Now I get a table with multiple rows (one for each row in the table)
> > each row having the same value for the median age. Is there a way I
> > can write the query so that a single row (a single value) is returned?
> >
> > Mike
> >
> > Damian S wrote:
> > > Hi Mike,
> > >
> > > The write-up of the Median function in the article you posted wants the
> > > parameters as strings... you will need to call it like this if you are using
> > > the same function:
> > >
> > > Median("[Listings Pending Verification Age List]","[Age]")
> > >
> > > Damian.
> > >
> > >
> > > "MikeB" wrote:
> > >
> > > > Hi.
> > > > I'm trying to create a function to get the median value of a series
> > > > of observations. I've found an excellent reference on a MS website on
> > > > how to construct a query to obtain the median value, but I'm having a
> > > > bit of difficulty getting the query "visible" to access. Whenever I put
> > > > the function into the design grid it fails "undefined function 'Median'
> > > > in expression."
> > > >
> > > > I created the function by clicking Insert -> Module. Access then
> > > > provided me with a Module1 (how do I rename it?) and I pasted the text
> > > > for the function into the module.
> > > >
> > > > I tried running it and initially I had some difficulties with
> > > > mismatching parameters (I passed a string instead of a table name).
> > > >
> > > > Once I got the errors ironed out, there are now other objects in my
> > > > Visual Basic Object list under a tree of "Utility".
> > > >
> > > > Basically, I have the function Median coded as it is demonstrated in
> > > > http://support.microsoft.com/?id=210581.
> > > >
> > > > I then have a query to extract a relevant list of records from the
> > > > table:
> > > >
> > > > SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
> > > > AS Age
> > > > FROM Listing
> > > > WHERE (((Listing.Status)="Pending Verification"))
> > > > ORDER BY DateValue(Fix([EndDate]));
> > > >
> > > >
> > > > Then I have the query that fails but is supposed to obtain the median
> > > > value:
> > > >
> > > > SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
> > > > FROM[Listings Pending Verification Age List];
> > > >
> > > > I'd appreciate any help someone can give me.
> > > >
> > > > Thanks.
> > > >
> > > >
> >
> >

Hi James,
I actually found the article you referred to prior to my first post,
but the SQL is a tad too much for me, I'm at a very basic level of SQL.

Thanks anyway.

Mike


James A. Fortune wrote:
> MikeB wrote:
> > Hi.
> > I'm trying to create a function to get the median value of a series
> > of observations. I've found an excellent reference on a MS website on
> > how to construct a query to obtain the median value, but I'm having a
> > bit of difficulty getting the query "visible" to access. Whenever I put
> > the function into the design grid it fails "undefined function 'Median'
> > in expression."
> >
> > I created the function by clicking Insert -> Module. Access then
> > provided me with a Module1 (how do I rename it?) and I pasted the text
> > for the function into the module.
> >
> > I tried running it and initially I had some difficulties with
> > mismatching parameters (I passed a string instead of a table name).
> >
> > Once I got the errors ironed out, there are now other objects in my
> > Visual Basic Object list under a tree of "Utility".
> >
> > Basically, I have the function Median coded as it is demonstrated in
> > http://support.microsoft.com/?id=210581.
> >
> > I then have a query to extract a relevant list of records from the
> > table:
> >
> > SELECT DateValue(Fix([EndDate])) AS [End], Fix(Date())-Fix([EndDate])
> > AS Age
> > FROM Listing
> > WHERE (((Listing.Status)="Pending Verification"))
> > ORDER BY DateValue(Fix([EndDate]));
> >
> >
> > Then I have the query that fails but is supposed to obtain the median
> > value:
> >
> > SELECT Median([Listings Pending Verification Age List],[Age]) AS mEnd
> > FROM[Listings Pending Verification Age List];
> >
> > I'd appreciate any help someone can give me.
> >
> > Thanks.
>
>
> In:
>
> http://groups.google.com/group/comp....b6eb508f22fa9d
>
> Titled "Median of GROUP BY values," I show a SQL method for obtaining
> the medians of query groups that does not involve calling a user-defined
> VBA function. Also, the links refer back to the non-grouped case.
>
> James A. Fortune
> (E-Mail Removed)

MikeB wrote:
> Hi James,
> I actually found the article you referred to prior to my first post,
> but the SQL is a tad too much for me, I'm at a very basic level of SQL.
>
> Thanks anyway.
>
> Mike


I understand. I had to pull a rabbit out of a hat with the SQL in order
to get a solution that didn't require a user-defined function (UDF).
I'm glad you got a UDF to work.

James A. Fortune
(E-Mail Removed)