Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post

    Any way to sort so that "m" comes before "f"?

    I'm retrieving demographic data from an Access database using ASP. There is a row for each result for male, a row for female, and a row for total (male + female). The column is called "gender", by the way. I can sort ascending (ORDER BY gender ASC), which will give the female row, then the male row, and then the totals row. I can sort descending (ORDER BY gender DESC), which gives me the same rows, but in reverse order.

    The values for the gender column are "m" for male, "f" for female, and "t" for totals.

    All well and good. But, traditionally in demographic data, the male comes first. I would like the male value come before the female value, even if it's alphabetically after the female. I know that I could sort descending; that would put male before female, but totals before male. It would be nice to have totals after the male and female values.

    Can it be done? Or will I just have to live with having female first or totals first?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    well, i psychological studies (my background) the just use 1 for male and 2 for female. i don't understand the problem since are basiclly free to represent the sexes with whatever value you like, buth the above were generaly used values.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    Regular Coder
    Join Date
    May 2004
    Location
    sweden
    Posts
    236
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The simple answer is to remove the totals row since totals is a result (business logic) and not actual data. The use SQL Count() or another scripting function to get your totals.
    Carl McDade
    _____________
    Hiveminds Magazine
    for web publisher and community builders
    eRuby Tutorials

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by raf
    well, i psychological studies (my background) the just use 1 for male and 2 for female. i don't understand the problem since are basiclly free to represent the sexes with whatever value you like, buth the above were generaly used values.
    That had occurred to me, but using numbers instead of letters is not exaclty intuitive...I mean, how would you know that male is represented by 1 and female by 2?

    Quote Originally Posted by carl_mcdade
    The simple answer is to remove the totals row since totals is a result (business logic) and not actual data. The use SQL Count() or another scripting function to get your totals.
    How owould using Count or another function help me get a totals value for two rows out of the whole tale?

    But then would I be able to select all gender values in a single query? I've got the querying system set up nicely already...the user can select a gender to query on (male, female, totals, or all, which displays all 3 "genders"). It sems to me that the querying system could get pretty messy if I understand what you're saying...

  • #5
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Using suitable CASE statements you could construct an extra field whose value (e.g. 1 or 2) depended on the male/female value. Then just sort on that.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Uhh? Could you elaborate on that some, M@rco?

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Grant Palin
    That had occurred to me, but using numbers instead of letters is not exaclty intuitive...I mean, how would you know that male is represented by 1 and female by 2?
    As i said, it was the general convention it that field.

    You will nowhere find a database in big research or business project where they use 'm' or 'f'. They will always use a numerical field because it is filterd on quicker then a string (certainly when indexed).

    Adding an extra column is an option, but if it's a large table, then these redundant data should be replaced by setting up a small table. Like
    Code:
    PK|shortlabel| Long Label | Comment
    1 | male | Male subjects |Null
    2 | female |Female subjects|Null
    8 | company |Companys | We only use the gender for persons. In dichotom alogorithems, this value is converted to Null and eliminated from thus eliminated from statistical processing.
    9 | unknown | Not specified gender | These can be observations with a missing value for that attribute in the inputfile In dichotom alogorithems, this value is converted to Null and eliminated from thus eliminated from statistical processing.
    And when you then pull up data to display them, then you join with this table to get the required info.

    The idea that data in a database-table should be meaningfull to a human observer, is typical spreadsheet thinking.
    Inside a db, the data should be stored in such a way that it allows the maximum of flexability + fast dataselection and manipulation. That it is clear to the human reader what it 'represents' is completely irrelevant. All variables and relationships should be documented anyway, so that the complete valuerange and dummymanipulations are known. (MsAcess even lets you enter a comment after each column in the designview of your table + you could also set up an outputmask to turn 1 into 'm' or 'male' or screen etc)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #8
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Grant Palin
    Uhh? Could you elaborate on that some, M@rco?
    Code:
    SELECT blah1, blah2, blah2, CASE WHEN SEX = 'male' THEN 1 ELSE 2 END AS SexNum ...
    FROM ...


    Note: That's T-SQL (i.e. for MSSQL)... Access should have an equivalent syntax if it's not the same.
    Last edited by M@rco; 06-30-2004 at 07:02 PM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by M@rco
    Code:
    SELECT blah1, blah2, blah2, CASE WHEN SEX = 'male' THEN 1 ELSE 2 END AS SexNum ...
    FROM ...


    Note: That's T-SQL (i.e. for MSSQL)... Access should have an equivalent syntax if it's not the same.
    In fact with T-SQL (SQL Server) you can do this:

    Code:
    SELECT blah1, blah2, blah2, sex, ... from ... order by  CASE WHEN SEX = 'male' THEN 1 ELSE 2 END
    That way the 1=Male, 2=Female never even exists outside the SQL statement itself.

    I don't know if MS Access is capable of doing that but if not, try looking at using MSDE instead.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #10
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Roy Sinclair
    In fact with T-SQL (SQL Server) you can do this:

    Code:
    SELECT blah1, blah2, blah2, sex, ... from ... order by  CASE WHEN SEX = 'male' THEN 1 ELSE 2 END
    That way the 1=Male, 2=Female never even exists outside the SQL statement itself.

    I don't know if MS Access is capable of doing that but if not, try looking at using MSDE instead.
    Even better!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #11
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    I'd forgotten about this thread I had going...The sorting issue got put on the back burner for a few days while I worked on some other stuff. But back to it now.

    Now I understand your suggestion about the CASE statement, M@rco...I thought you were talking about SELECT CASE... in VBScript, and was thinking, "what??? ".

    I know that Oracle has the SELECT CASE statement for SQL, having used it before...I don't know about Access, so I'll have to check.

    The way I see this now is, I could convert "m" in the gender column to 1, "f" to 2 and "t" to 3. Then just do the select as before and do ORDER BY gender ASC. So I would get male, then female, then total. But I would need to translate between numbers and letters on the front end...That wouldn't be too big a deal; I could set up a function that takes that number from the DB column and returns the text representation.

    It seems that Roy Sinclair's suggestion would work well, since there would be no need to convert from a letter to a number, or vice versa. I tried adding that chunk of code to my SQL statement, but got an "unspecified error". I presume that's Access saying that it can't process that new bit of code.

    EDIT: Although, even if ti did work, how would sorting by 1 or 2 cause the male column to appear before the female column? Unless you're referring to the numbers those genders are mapped to...?

    So I guess that where that leaves me is to try changing the gender letters to numbers (1,2,3 for m,f,t), and translate them when necessary, or try MSDE.

    Or are there any other options?
    Last edited by Grant Palin; 07-08-2004 at 01:19 AM.

  • #12
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Grant Palin
    Now I understand your suggestion about the CASE statement, M@rco...I thought you were talking about SELECT CASE... in VBScript, and was thinking, "what??? ".


    Quote Originally Posted by Grant Palin
    I know that Oracle has the SELECT CASE statement for SQL, having used it before...I don't know about Access, so I'll have to check.

    It seems that Roy Sinclair's suggestion would work well, since there would be no need to convert from a letter to a number, or vice versa. I tried adding that chunk of code to my SQL statement, but got an "unspecified error". I presume that's Access saying that it can't process that new bit of code.
    I've had a quick look and it seems that it's not supported (it's not a reserved keyword) so it certainly doesn't support it in *exactly* the same way that MSSQL does, but that's not to say that there isn't an equivalent statement (you'll have to hunt for that yourself).

    Quote Originally Posted by Grant Palin
    The way I see this now is, I could convert "m" in the gender column to 1, "f" to 2 and "t" to 3. Then just do the select as before and do ORDER BY gender ASC. So I would get male, then female, then total. But I would need to translate between numbers and letters on the front end...That wouldn't be too big a deal; I could set up a function that takes that number from the DB column and returns the text representation.
    If Access does NOT support a statement equivalent to MSSQL's "CASE" then yes, modifying your database is the way to go. However, rather I suggest that you simply add a lookup "Gender" table and then store the GenderID in your main table (e.g. 1 = male), then JOIN the Gender lookup table when you perform the query, allowing you to sort on the numeric value in the way that you want to, but then return the text value (from the JOINed lookup) in the recordset. It's much cleaner to do it this way rather than hardcode the 1=male, 2=female (etc) values, even though arguably in this particular case these are unlikely to change. As an added bonus, you can use this same Gender lookup table to drive a listbox (or radio buttons) on a form used for data collection...

    Just got back from a business trip to Milan an hour ago... signing out (and crashing out) now...! ZZZZZZZZZzzzzzzzzzz........
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by M@rco
    If Access does NOT support a statement equivalent to MSSQL's "CASE" then yes, modifying your database is the way to go. However, rather I suggest that you simply add a lookup "Gender" table and then store the GenderID in your main table (e.g. 1 = male), then JOIN the Gender lookup table when you perform the query, allowing you to sort on the numeric value in the way that you want to, but then return the text value (from the JOINed lookup) in the recordset. It's much cleaner to do it this way rather than hardcode the 1=male, 2=female (etc) values, even though arguably in this particular case these are unlikely to change. As an added bonus, you can use this same Gender lookup table to drive a listbox (or radio buttons) on a form used for data collection...
    Sounds familiar ... but passed unnoticed.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #14
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by raf
    Sounds familiar ... but passed unnoticed.
    Oops... so it does...!!
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #15
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by raf
    Sounds familiar ... but passed unnoticed.
    Not unnoticed. I was hoping that I could find a solution that wouldn't require changing the structure of the database.

    I think I found Access' equivalent of the CASE statement in the IIF statement.

    Roy's example:
    Code:
    ORDER BY CASE WHEN SEX = 'male' THEN 1 ELSE 2 END
    The Access equivalent with IIS:
    Code:
    ORDER BY IIF(gender='m',1,2)
    When I ran that, it worked! I had the male row, then the female row, and then the total. That was with a single region and single year.

    When I run the same query, but with multiple regions or years, I get male, then total, and then female.

    So I get the sorting that I want, but only with a single year and a single region.

    I'm not sure that this is the right way to be doing this...On the query page, the user can select to see data for just males, just females, just totals, or all three. Of course, when doing a single gender, order doesn't matter. But when doing all three, I want male, then female, and then totals. I'm not sure I understand how this is going to help. I mean, how is ordering by 1 or 2 going to make a difference?

    I notice that I'm checking for whether the gender is male...what's the point of that?

    I'm rather confused about this. Is this workable?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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