Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Query for MEDIAN In Access 2003

    I need a query to accomplish the results as see in this link:

    http://www.rsmrlty.com/Average_Price_Cities_SFR.htm

    I'm looking for a "simple" to understand query that will quary all of the cities and areas (City Field) in a given database by specific date ranges to provide the MEDIAN sale price. The number of transactions are simply the total record count in the query per city.

    I can do this manually but it takes for ever!

    I've looked through the examples here but most are drastically complex.

    Any assistance would be greatly appreciated.

    Thanks . . . Rick

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is all this data in one table?

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There is not a simple way to calculate the median in SQL - at least not one that is obvious if you are not really quite comfortable with set based logic and SQL. There are also a couple of variants - I forget the names of them but if there are an even number of qualifying rows then you need to decide which value is the median.

    Why do you need a simple version rather than one that simply "works"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Median

    Yes - the data is all in one table.

    Access' SQL seems to be difficult for me to grasp.

    Many times I see very complex answers to questions in the forums and then a very simple solution that I can grasp almost immediately. (Simple mind I suppose).

    With Access it appears there is more than one way to accomplish the same goal.

    In answer, "Simply" works - yes!

    Where there are an even number of records I need to calculate the average of the two records (SalePrice field) in the middle of the table which becomes the MEDIAN.

    AAAAA
    BBBBB average
    CCCCC average
    DDDDD

    the average of the two becomes the median

    An odd number of records is easier (SalePrice = ?) or some kind of variable assignemt I think.

    AAAAA
    BBBBB
    CCCCC = median/the same number of records above and below the median record
    DDDDD
    EEEEE

    Here is the manual query I use.

    SELECT TblSoCalMLS_Download.SALESPRICE, TblSoCalMLS_Download.CITY
    FROM TblSoCalMLS_Download
    WHERE (((TblSoCalMLS_Download.CITY)=[Enter City Code]) AND ((TblSoCalMLS_Download.PROPSUBTYPE)="PROPCONDO") AND ((TblSoCalMLS_Download.LISTSTATUS)="closd") AND ((TblSoCalMLS_Download.OFFMARKETDATE)>=#3/1/2008# And (TblSoCalMLS_Download.OFFMARKETDATE)<=#3/31/2008#))
    ORDER BY TblSoCalMLS_Download.SALESPRICE;

    I have to enter every city code one at a time and then do the averaging by hand if there are an even number of records.

    Oh, I need to count the records in each query result as this is the number of transactions shown in the matrix at this link: http://www.rsmrlty.com/Average_Price_Cities_SFR.htm.

    Hope I haven't overwhelmed anyone here.

    Thanks much .. . Rick

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    The code here should do what you need.
    http://www.fabalou.com/Access/Module...set_median.asp

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    MEDIAN all cities?

    Rogue - thanks for your info.

    I looked at that link and copied it to try and understand.

    Here is my manual procedure ;

    Query each city/area separately. This takes a very long time.

    Will this procedure perform the MEDIAN clculations for each city at one time? I think not.

    That is my goal.

    Please let me know.

    Thanks much.

    Rick

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    At a quick glance, I don't see why it wouldn't. Try using the function in groupby query.

  8. #8
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Put a string variable in your query instead of [Enter City Code] to let you put the cities in your query by code, then loop through your cities and run the Rogue's function giving the cities to it.
    you can put the resaults of function in an aray and then print it out.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by rogue
    The code here should do what you need.
    http://www.fabalou.com/Access/Module...set_median.asp
    You can modify that to accept a WHERE clause and loop through it without too much difficulty.

    Another method (second question of the day that can be solved this way... odd) would be to assign an arbitrary rank to every listing in your table, then take a look at the min and max rank values and see if the current rank is in the middle.

    Also, you're not trying to do this over RETS are you?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •