...

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

Grant Palin
06-29-2004, 08:02 PM
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?

raf
06-29-2004, 09:13 PM
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.

06-29-2004, 10:04 PM
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.

Grant Palin
06-29-2004, 11:28 PM
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?

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...

M@rco
06-29-2004, 11:31 PM
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.

Grant Palin
06-30-2004, 01:12 AM
Uhh? :confused: Could you elaborate on that some, M@rco?

raf
06-30-2004, 09:13 AM
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

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)

M@rco
06-30-2004, 07:00 PM
Uhh? :confused: Could you elaborate on that some, M@rco?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.

Roy Sinclair
06-30-2004, 11:26 PM
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:

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.

M@rco
06-30-2004, 11:56 PM
In fact with T-SQL (SQL Server) you can do this:

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! :thumbsup:

Grant Palin
07-08-2004, 01:05 AM
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??? :confused: ".

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?

M@rco
07-08-2004, 11:22 PM
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??? :confused: ".:D

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).

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........

raf
07-09-2004, 12:14 AM
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.

M@rco
07-09-2004, 12:20 AM
Sounds familiar ... but passed unnoticed.Oops... so it does...!! :rolleyes: :thumbsup:

Grant Palin
07-09-2004, 12:30 AM
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:

ORDER BY CASE WHEN SEX = 'male' THEN 1 ELSE 2 END

The Access equivalent with IIS:

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?

raf
07-09-2004, 12:40 AM
Not wanting to make a big deal out of it, but the classical rules do have some value.

If you work with a classical star db-design (one big factstable, that only contains numercical values --> measures and foreign keys of other, smaller dimensiontables) then you don't run into this sort of issues.
Performance will be better + your db-design will in almost all cases be closer to the informationneeds that you have on each page in your application + you'll need less code to proces the recordsets and certainly no hardcoded conversion (wether they are in sql or your applicationlayer, they are almost never the best sollution.)

So, if you follow classical rules, and do your db-normalisation (in this particular case : put the sexes in a different (dimension)-table and the primary key values of this simensiontable inside your factstable.

Like M@rco alreay indicated : you can then use the dimensiontable to generate a (sexes)dropdown for your inputform. But you can also use the labels in your reports. And the comments as footnotes etc
And unless you are joinofobic, there are no downsides.

Grant Palin
07-09-2004, 12:43 AM
You know what, I figured it out! :D

I need to add another IIF statement to the false entry for the first IIF statement. So if the gender in the first IIF was NOT male, then the nested IIF statement would execute. In the nested IIF statement, if the gender is female, then I sort by 2. If the gender is not female, I give it a bogus value (3), so it won't do anything. Int his case, it would be at just tyhe totals row, and wouldn't need to sort anyway.

Here's what I've got now:

ORDER BY IIF(gender='m', 1, IIF(gender='f', 2, 3))

This works with a single region and single year, and also works with multiple regions and years. And I always get male, then female, and then totals!

Grant Palin
07-09-2004, 12:53 AM
Not wanting to make a big deal out of it, but the classical rules do have some value.
Fair enough. Again, I was hoping to not have to change the DB structure...

If you work with a classical star db-design (one big factstable, that only contains numercical values --> measures and foreign keys of other, smaller dimensiontables) then you don't run into this sort of issues.
Performance will be better + your db-design will in almost all cases be closer to the informationneeds that you have on each page in your application + you'll need less code to proces the recordsets and certainly no hardcoded conversion (wether they are in sql or your applicationlayer, they are almost never the best sollution.)
I remember star schemas from my DB classes. I know what you're talking about. The fact table should contain just numbers (foreign keys and actual values). And the smaller, dimension tables contain related information.

So, if you follow classical rules, and do your db-normalisation (in this particular case : put the sexes in a different (dimension)-table and the primary key values of this simensiontable inside your factstable.
Makes sense.

If you note my most recent post, I'd figured out how to do a dynamic sorting. No big deal, just a little extra SQL. However, would it be really worthwhile to create another database table, just for containing gender information?

Like M@rco alreay indicated : you can then use the dimensiontable to generate a (sexes)dropdown for your inputform. But you can also use the labels in your reports.
Does sound convenient.

And the comments as footnotes etc
Do you mean the coments in Access? I know that when looking at a table in design view, you can enter column-level comments. Is that what you're talkign about?

And unless you are joinofobic, there are no downsides.
joinofobic? :confused:

raf
07-09-2004, 01:12 AM
I most definitely think it would be worthwhile to set up a dimensiontable.

- It will take you 1 create statement. 2 or 3 inserstatements and 2 or 3 updatestatements. But it will save you time in the end.
- I only have a very limited braincapacity, so i always try to only use techniques/methods that are universal. My setup would work on each db without any altrnations, while you would need to earn a new trick if you upsize to another db. You would not only need to learn new things, you'd also need to go over your code to set all relevant querys straigt.
- performance will be far better with a normalised design
- every experienced coder or dba will understand the logic bhind your design, without needing to go over your code
- updating the labels will be no problem at all with the dimensiontable (just updating a few cells) while in your setup ... (Imaging you'd wanted to use this in a multi-language site or so ..)

and then there is the issue of instant availability of the valueranges and labels to for instance use it to build a formfield or so.

Do you mean the coments in Access? I know that when looking at a table in design view, you can enter column-level comments. Is that what you're talkign about?
No. Look at my second post --> you can include a comments column in your dimensiontable to explain some of the values, and use that to display it inside reports or descriptions of your statistcal output

and a "joinofobic" is my petname for someone with "an irrational fear for joins" (you know, like an arachnafobic is someone with an irrational fear for spiders)

M@rco
07-09-2004, 01:42 AM
Not unnoticed. I was hoping that I could find a solution that wouldn't require changing the structure of the database.Actually, raf was referring to post no.7 in this thread, which I had forgotten about... ;)

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

Roy's example:

ORDER BY CASE WHEN SEX = 'male' THEN 1 ELSE 2 END

The Access equivalent with IIS:

ORDER BY IIF(gender='m',1,2)

When I ran that, it worked!Excellent. My brain's obviously fried today - using IIF hadn't occured to me. (Note to self: why aren't I in bed already?!!) :D

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?Yes, it's perfectly workable - you just need to nest another two IIF statements inside the first (and inside each other) so that 1 is returned for "m", 2 for "f", 3 for "t" and (for cleanliness), Null for any other value.

;)

Grant Palin
07-09-2004, 02:55 AM
- It will take you 1 create statement. 2 or 3 inserstatements and 2 or 3 updatestatements. But it will save you time in the end.
- I only have a very limited braincapacity, so i always try to only use techniques/methods that are universal. My setup would work on each db without any altrnations, while you would need to earn a new trick if you upsize to another db. You would not only need to learn new things, you'd also need to go over your code to set all relevant querys straigt.
- performance will be far better with a normalised design
- every experienced coder or dba will understand the logic bhind your design, without needing to go over your code
- updating the labels will be no problem at all with the dimensiontable (just updating a few cells) while in your setup ... (Imaging you'd wanted to use this in a multi-language site or so ..)

All very good points. After thinking about the issue for a while, I decided it would be better in the long run to go with the seperate dimension table.

So, I created a new table called "genders" with the following columns: "genderid", "genderabbrev", and "gendername". "genderid" is the nunber respresenting the gender (1 for male, 2 for female, and 3 for totals). "genderabbrev" is the one-letter abbreviation of the gender to represent it as the value of the gender radio buttons I have set up already, and is used as the actual gender criteria. the number for the gender is only used for ordering results, so male comes before female. "gendername" is the full name of each gender. I'm not sure if I'll need to keep this, since I use the one-letter abbreviation for output.

But, in the end, I took your advice (why break with tradition? ;) ) and set up a new table and did a join when doing my query. And it works, and it's simpler than doing that fancy logic inside the query!

and then there is the issue of instant availability of the valueranges and labels to for instance use it to build a formfield or so.
Although I'm sure that it's not exactly a lot of work to build a 3- or 4-item radio list of listbox. ;)

and a "joinofobic" is my petname for someone with "an irrational fear for joins" (you know, like an arachnafobic is someone with an irrational fear for spiders)
Okay, I wasn't sure if it was a typo or something... :p

I'm not afraid of joins, although they can be tricky sometimes, especially when you're trying to make sure you're matching the correct data! :p ;)

Excellent. My brain's obviously fried today - using IIF hadn't occured to me.
I actually wondered if there was something about ASP/Access that you didn't already know!! :p

(Note to self: why aren't I in bed already?!!) :D

Yes, it's perfectly workable - you just need to nest another two IIF statements inside the first (and inside each other) so that 1 is returned for "m", 2 for "f", 3 for "t" and (for cleanliness), Null for any other value.
Thanks for that, but not it's necessary now - read my reply to raf.

raf
07-09-2004, 03:40 AM
i don't quite see why you need a category in your gendertable for the total ...
normally, if you store agregated data, then you store them at the level they belong to. Which means you'll have an extra column (at that table)
So in your case, table genders would have a collumn (sumwhatever) and in each row, you'd have the total.
The overal total would not be stored but computed at runtime.
If you would store it, then it should be at a higher level. (For instance a table with the other descriptive data of your complete population.

Note: most db's are optimised for agregationfunctions like sum(). If you for instance create an index on a column, then the sum() value is for most db's immedeately retrievable.

Although I'm sure that it's not exactly a lot of work to build a 3- or 4-item radio list of listbox.
Hmm. Thats not the right way to look at it.
I i write a regular formpage, then all i create is a 2D array.
Each element of the array is another array which elements define the formfield (label, formfieldtype, allowed valuetype, reference to table.column for the value's, defaultvalue, table.column to store posted value)
I then have my standardpage that generates the formfields out of the array, and that processes the posted form.

This speeds up formbuilding considerably + i always just need to update something at one place. Values in the db, formbuilding in the arrayprocessing script.

If you hardcode the forms, you'll spend more time creating and updating the form (imagine changing them over to xhtml if they are all hardcoded, or imagine what would happen if you needed to alter the values of a frequently used characteristic) Code once, run forever ...

It's kinda like using a css file : Formatting your pages also doesn't take long, but with a stylesheet it's easier to update.

If you stick by the rules and nicely seperate everything and make it as dyamic as possible, then you'll see that it all comes nicely together.

Grant Palin
07-09-2004, 08:41 PM
i don't quite see why you need a category in your gendertable for the total ...
normally, if you store agregated data, then you store them at the level they belong to. Which means you'll have an extra column (at that table)
So in your case, table genders would have a collumn (sumwhatever) and in each row, you'd have the total.
The overal total would not be stored but computed at runtime.
If you would store it, then it should be at a higher level. (For instance a table with the other descriptive data of your complete population.

Note: most db's are optimised for agregationfunctions like sum(). If you for instance create an index on a column, then the sum() value is for most db's immedeately retrievable.

Because if I'm doing a query for all 3 genders (male, female, and total), I can't get existing data, and then add extra rows to the result by doing sums (that just adds columns). A way around that would be to do a query for male and female values, and then another query to get their sums, but that's going to get messy. Besides, I've got recordset paging set up nicely, and it wouldn't work so nicely with running two seperate queries. And anyway, my manager prefers to use the sums as stored data, not calculated data. I don't know why, but it doesn't really matter. Business rules! :rolleyes:

So I need to have the totals row in the genders table, so I can do a SELECT on that table, or join it with the data table. Which brings up another thing: all the ACTUAL DATA is in the "data" table; I'm just using the genders table as a small lookup table.

Hmm. Thats not the right way to look at it.
I i write a regular formpage, then all i create is a 2D array.
Each element of the array is another array which elements define the formfield (label, formfieldtype, allowed valuetype, reference to table.column for the value's, defaultvalue, table.column to store posted value)
I then have my standardpage that generates the formfields out of the array, and that processes the posted form.

This speeds up formbuilding considerably + i always just need to update something at one place. Values in the db, formbuilding in the arrayprocessing script.
Do you mean you create the form through scripting, and not through straight HTML? I'm doing a bit of each, since some parts of the querying form never change, but other parts do change depending on what options the user chose on the previous page. Here's a sort of walk-through:

1. On the first page, the user can choose the category of region (health, census, school), and goes to the second page.

2. On the second page, depending the category selected from the previous page, different region types are selected. For example, choosing the health category (ont he first page) would cause options to appear for Health Authority, Health Service Delivery Area, and Local Health Authority on the second page. The list of options is different for each category ont he first page; I have a method that handles the list generation. The user selects a region type, and chooses whetehr to query estimates (past data) or projections (future data).

3. On the third page, the form is the same for each type of region available. However, the form does change depending on whether the user selected estimates or projections on the previous page. When doing estimates, the user can query on 5-year or toals age groups, and the gender options we've discussed before (male, female, totals, all). When doing projections, however, the user can only query on the totals age group and the totals gender. I have a method that handles generating each of those two lists, depending on the choice of estimates or projections. The user then proceeds to select the criteria and run the query.

In the methods that I mentioned, I just fill a variable with the HTML to create the necessary lists. The options added are dynamic insofar as checking for whether to display all age group options or just the total, or all gender options or just the total. I don't get the options from the DB or anything like that.

If you hardcode the forms, you'll spend more time creating and updating the form (imagine changing them over to xhtml if they are all hardcoded, or imagine what would happen if you needed to alter the values of a frequently used characteristic) Code once, run forever ...
The forms are XHTML already...I've built this querying system from scratch, the right way!

And I don't imagine that the form options will be changing anytime soon. But I could go ahead and do that gender list generation from the DB, just to automate the process a little more.

It's kinda like using a css file : Formatting your pages also doesn't take long, but with a stylesheet it's easier to update.
Okay, good analogy. It makes sense.