Order in Access - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Order in Access

Author

Topic

goksut
Starting Member

5 Posts

Posted - 2003-05-21 : 16:38:13

I am trying to order a column in access. The field name is Project Status and I would lilke to order it by red first yellow second and green third. Anyone knows how I can order that way?

Thanks
Goksu

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-21 : 17:18:41

I here Mauve is a very fast database color...

What are you talking about?

Are you looking at an excel spreadsheet?

Brett

8-)

goksut
Starting Member

5 Posts

Posted - 2003-05-21 : 17:27:46

I am looking at Access as I mentioned and in the query I have written statements of "red" "yellow" "green". I would like to sort them in the order I asked above.

quote:

I here Mauve is a very fast database color...

What are you talking about?

Are you looking at an excel spreadsheet?

Brett

8-)

izaltsman
A custom title

1139 Posts

Posted - 2003-05-21 : 18:25:36

Use iif() function to assign a numeric value to each of those colors like so:


SELECT MyMessedUpColors.color, IIf([color]="red",1,IIf([color]="yellow",2,3)) AS Expr1
FROM MyMessedUpColors
ORDER BY IIf([color]="red",1,IIf([color]="yellow",2,3));

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-21 : 19:08:13

You could also create an order table with two fields color, order

so it would have
red 1
yellow 2
green 3

Next do the following query

SELECT *
FROM MyTable A INNER JOIN ColorsTable B ON A.[Project Status] = B.color
ORDER BY B.Order

If you need a different order just change the table.

goksut
Starting Member

5 Posts

Posted - 2003-05-22 : 10:04:53

Thanks a lot.

quote:

Use iif() function to assign a numeric value to each of those colors like so:

SELECT MyMessedUpColors.color, IIf([color]="red",1,IIf([color]="yellow",2,3)) AS Expr1
FROM MyMessedUpColors
ORDER BY IIf([color]="red",1,IIf([color]="yellow",2,3));

goksut
Starting Member

5 Posts

Posted - 2003-05-22 : 10:05:37

Thanks a lot.

quote:

You could also create an order table with two fields color, order

so it would have
red 1
yellow 2
green 3

Next do the following query

SELECT *
FROM MyTable A INNER JOIN ColorsTable B ON A.[Project Status] = B.color
ORDER BY B.Order

If you need a different order just change the table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources