how to sort data?

[gegeying]

I am trying to create a report in Access 2002. Below is the SQL.

SELECT segment_detail.segment_no, Shops.County, segment_detail.shop, segment_detail.routes, sponsors.name
FROM Shops INNER JOIN (sponsors RIGHT JOIN segment_detail ON sponsors.sponsor_id = segment_detail.sponsor_id) ON Shops.Shop = segment_detail.shop
WHERE (((segment_detail.segment_no)="52695N00760173" Or (segment_detail.segment_no)="52695N01800248" Or (segment_detail.segment_no)="52695N00000080"));


My problem is I want report to show the data exactly following the order of segment_no in "where" part. Like below;

52695N00760173
52695N01800248
52695N00000080

but, when I run the report, the order of data is not like above. How to fix that?

Thanks.

 

[fneily]

For simplicity, you could do an old programming trick. In your segment_detail table, add anothr field, let's say SortOrder, and use an ascending letter or number to specify the exact order you want. So:
52695N00760173 1
52695N01800248 2
52695N00000080 3
Then include the field in your pickoff and Order By it. On the report, you can just make the field invisible.

 

[dhookom]

Try:

SELECT segment_detail.segment_no, Shops.County, segment_detail.shop, segment_detail.routes, sponsors.name
FROM Shops INNER JOIN (sponsors RIGHT JOIN segment_detail ON sponsors.sponsor_id = segment_detail.sponsor_id) ON Shops.Shop = segment_detail.shop
WHERE segment_detail.segment_no IN ("52695N00760173" , "52695N01800248","52695N00000080");

In the report, set the sorting and group dialog expression to:
[tt][red]
=Instr("52695N00760173~52695N01800248~52695N00000080",[Segment_No])[/red][/tt]

Duane
Hook'D on Access
MS Access MVP

 

[gegeying]

Thank dhookom.
Your expression works fine. however, I have a long list (about 45 segment_no) need to show on report. I tried and I was told that the expression is too long.

do you have other way ?

many thanks.

 

[lespaul]

if you have that many you'll probably be better off adding a field to the existing table as suggested above or if you can't modify the existing one, create a small little table to determine the sort order:
[tt]
TblSortOrder
Segment_no SortOrder
52695N00760173 1
52695N01800248 2
52695N00000080 3
.... 45[/tt]

then you would just have to join into this new table include the sort order in the query so that the report can sort by that field.

HTH



Leslie

Have you met Hardy Heron?

 

[dhookom]

I agree with the table solution. Here is one of my favorite links

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx.

Duane
Hook'D on Access
MS Access MVP