Sort by words, not by column 1

[porter]

I have a column in my report called "Status". The Status will be either Complete - Incomplete - Partial. How do I make the column list by Complete - Partial - Incomplete??

I run the report from a query.

P

 

[MichaelRed]

Group by


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

I'm not clear about what you want. The subject says sort by words, not by column. However, in a query you can only "Order By" columns but the data (or words) in the columns would be sorted. For example, data in the status column could be ordered Complete, Incomplete, Partial. To do this requires only an "Order By status" clause at the end of the query.

But are you asking about how to make a Cross-Tab query with Complete, Incomplete, Partial as column headings? Please clarify.


Terry

http://members.home.net/tlbroadbent/index.htm

 

[porter]

In my report I would like to have, under Status, show all entries that are "Complete", first. Then all entries that are Partial, second. Then all entries that are Incomplete, third.

 

[tlbroadbent]

Try this.

Select * from Table1
Order By
Case status
When 'Complete' then 1
When 'Partial' then 2
When 'Incomplete' then 3
End
Terry

http://members.home.net/tlbroadbent/index.htm

 

[PaulF]

since you will probably want to sort by more than just the status field, add an expression field to your query, use an IIf statement to set the status sort order, and sort this field ascending. Just ensure you locate this field in the query grid prior to any other field you sort on.

MYSTATUS: IIf(Left([STATUS],1)="C",1,IIf(Left([STATUS],1)="P",2,3))

PaulF

 

[MichaelRed]

WHAT IS WRONG WITH JUST DOING A GROUP BY?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

First: Group By does not guarantee order.

Second: he doesn't need grouping, he needs ordering only. There are no aggregates involved.

Third: Group By or Order By [status] yields the following order; Complete, Incomplete, Partial which doesn't meet the requirement. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

The Case statement won't work in Access. Sorry about that - too much SQL Server. Use the IIf function as proposed by PaulF. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

Also throw out my comment about Group By not guaranteeing order. Again, a concept of SQL Server. Terry

http://members.home.net/tlbroadbent/index.htm

 

[porter]

tlbroadbent,
Your suggestion may work, but can you tell me where to put the code?? Sorry, not real good with codes.

P

 

[tlbroadbent]

In the query designer, open the query and create a new column with this code proposed by PaulF.

MYSTATUS: IIf(Left([STATUS],1)="C",1,IIf(Left([STATUS],1)="P",2,3))

Then select Sort Ascending on that column. If you have additional columns to Sort place them after this column. You can choose to display this new column in the result or suppress it. Terry

http://members.home.net/tlbroadbent/index.htm

 

[porter]

I copied your statement and pasted into a new column criteria in the query. I get an error message. "The expression you enter has an invalid...", click OK and the cursor blinks on the first open parentheses. I have four open parentheses and four closed parentheses. I do have the two parentheses on the end. Not sure why I'm getting this error message.

P

 

[tlbroadbent]

I copied and pasted it in Access and had no error. Did you include all of this statement?

MYSTATUS: IIf(Left([STATUS],1)="C",1,IIf(Left([STATUS],1)="P",2,3))

Terry

http://members.home.net/tlbroadbent/index.htm

 

[ruthcali]

I have found when copying and pasting from a web page to Access that the "" can get messed up. Or you end up copying hard returns or spaces that aren't really noticable but mess up your code. try typing it manually. ruth.jonkman@wcom.com

 

[porter]

Not sure what I'm doing wrong. Select the query name, click on design, then inserted a new column. Left "Field" "Table" "Sort" blank. Typed MYSTATUS: IIf(Left([STATUS],1)="C",1,IIf(Left([STATUS],1)="P",2,3)) in Criteria. Go to click on "Sort" and get the error message.


P

 

[tlbroadbent]

Place the code in the Field cell not the Criteria. Sorry I wasn't more clear on that. Terry

http://members.home.net/tlbroadbent/index.htm

 

[ruthcali]

No, the IIF statement goes in the Field row, not in the criteria row! ruth.jonkman@wcom.com

 

[porter]

Thanks, that did it. I appreciate the patients.

By the way, congraduation tlbroadbent on being voted TipMaster of the Week. Good luck on winning that leather jacket.

P