Median in ms-access

[JPCogAdmin]

Hi,

I'd like to get the median out of a record field
ie age. How could I do that?

Thanks for your help

-JP

 

[AlexCuse]

JPCogAdmin -

Have a look at this thread. I haven't implemented this at all, but it looks promising.

thread181-54516

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Thanks AlexCuse,

I'll take a look at it and find let you know.
;-)
Going to see it now....

-J

 

[JPCogAdmin]

AlexCuse,

Just looked at it. Could not use it bacause they're using ADO. They're programming it and its not just SQL. I was even thinking of trying to write sql that would number each row and basically create an increasing counter. Then I would find out if the counter is odd or even then select the middle and Wola but I don't know how I could do that.

-J

 

[AlexCuse]

J -

I will take a closer look at it some time by monday. I think you'd be able to implement them pretty easily (just by pasting the code from this post:

timgill (IS/IT--Management)
14 Feb 01 10:22

Into a VBA code module. For calculations like this, you are usually much better off doing them in a lower level language than you would be doing them purely in SQL (especially if you are doing them lots of times in each query).

What version of access are you using?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Thanks Alex,

I'm using Access 2003. Looking forward to what can come out by Monday.
;-)

-JP

 

[AlexCuse]

Hm, I looked at this this morning, and it seems the median function takes a comma separated list of values. In order to make this work for you, I think we will need to come up with a VBA routine to get your column into an array. It will be trickier than I thought but it can be done. Can you provide more info on your table design, some sample data, and what you would like the results to look like?

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Alex,

I can't come up with a VB integration solution because this will have to reside in Oracle. In 10g this is simply called as Median(field) but I'm trying a quick proof of concept. The bad thing is that Access 2003 doesn't support case when stuff. I don't know if I should be persuing this mean() in access. But, I was thinking that if there was a way to write the mean() in all SQL then it could be portable to other DBs. One thing I don't know if it can be done is:

To create an ordered list of -- lets say 3 fields and count each one in incrementing format. ie.

field1, field2, counter
tha thata 1
thataa thatatha 2
thattaa thatathaa 3
............
.......
....

Then I could get the middel value and send it out as the median.
;-)
hehe.
But, I don't know how to get that done here.

-J

 

[AlexCuse]

The equivalent in access to CASE is called iif, iif that helps you ;-)

Honestly, if this is to be an Oracle database, I would just download oracle's freebie version and put it together in there (if you don't want it on your server). When moving from access to a more robust DB platform you (well, I anyway) basically end up needing to rewrite everything I have in terms of queries.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Thanks Alex,

I appreciate every attempt you've made when I have posted here. It means alot.

-J

 

[AlexCuse]

JP - I have gotten a ton of help from this site, and I like to give back where I can. I am sure you will do the same thing

Are you still trying to do this in access, or are you going to try and get it done in Oracle? I think we can probably replicate oracle's median function in access with a bit of work. Let me know.

Ignorance of certain subjects is a great part of wisdom

 

[JPCogAdmin]

Hi Alex,

;-)

I will try to do the next amount of work in Oracle.
;-)
Lets keep in touch.
;-)
-JP

 

[AlexCuse]

You know where to find me ;-)

I don't know much about oracle, but I do know where the oracle 10g forum is (I like to read a lot). So if you run into issues, here is the place to go: forum1177

Good Luck!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Here's a SQL Server method to get the median. No cursors, no dynamic sql. It may not help (since you are not using SQL Server). faq183-6220


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[JPCogAdmin]

;-)
Thank you Alex.
Thank you to you too -George.
;-)
I'll keep in touch.

-Jp