Access multiple sort order for SQL statement

[jefargrafx]

I havea an access database that I'm querrying to diplay information on a web page. The boss wants the info sorted by date first and then item number, however, he wants to change the sort order of the item number only on some weeks through out the year. What a pain.

I am considering writing 52 queries one for each week of the year and then sorting each either ASC or DESC depending on the week.....unless you guys can offer a better idea?

let me know



jef

 

[lespaul]

Do you know which weeks need to be in which order?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[jsteph]

I wouldn't do the 52 query thing, the week/sort order will likely change anyway, right?

Just add a field to the table called SortID or something. Write some code that zips through the data week by week, opening a set for the week sorted by whatever whim your boss want's for that week. Update the sortid within the week based on that.

This way, the web pages code never needs changing, you just always do "Order By Week,sortID". When the whim changes and a particular week needs it sorted by Grandmother's middle name, then rerun the code for that week and update the sortID. Whe web page is ready with the same Order By clause.
--Jim

 

[jefargrafx]

yeah, I thought of that too and that's probaly what I'll do, unless you can write tranaction SQL for an access 97 database queried from a web page. (if statement, or case statement).

Will I have to up scale the database to mySQL or MSSQL to do such a think?

thank for the feedback
Jef

jef

 

[jsteph]

jef,
How often is the data (sort order) going to change? Will it be on regular intervals? You could set an Access app to run on a timer, say, weekly or whatever to run the sort procedure.

The sort order by week would be taken from a table (how you set up this table is something you'd only know now since I'm not sure of the details of how/why/when/etc the sort changes). Then from anywhere (even the web page) an admin (you) could enter/update the sorting logic within the table.

Then next time (or on demand via a web button available to admins) you change that logic, the sort procedure runs and is reflected in the next page refresh.
--Jim

 

[lespaul]

you can write an iif statement in an Access query.

iif(condition to evaluate, value if true, value if false)

iif(somefield = 'Something', 'Something', 'Not Something')

nested:

iif(somefield = 'Something', 'Something', iif(somefield = 'Nothing', 'Nothing', 'Something else'))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual