Re: Changing Ordering

On Mon, 6 Feb 2006 02:11:30 -0800, Sian wrote:


Sorry I don't think I explained my problem properly. I can't just use desc
as this isn't what I need.

For example, I have the following parts on the shelf, in this (logical) order:

Table 1
FDB2
FDB12
FDB125
FDB1000
FDB2100

However, if I run a report that includes these part numbers on T21, the
report sorts them as follows:

Table 2
FDB1000
FDB12
FDB125
FDB2
FDB2100

I can see the sorting logic here (ie part no. is not an integer and
therefore it is read and sorted as text - from left to right) but I?m afraid
this sort order (Table 2) is no good.

We need a system option to manage/redefine sort orders for alpha-numeric
part numbers on all reports that contain part number lists.


Hi Sian,

I was afraid of this....

The problem is caused by a flawed design. Apparently, these part numbers
are not a single code, but a concatenation of two attributes. And at
least one of those attributes has more meaning than just identifying a
part.

Since "FDB" and "1000" are two distinct attributes, they should have
been stored in two distinct columns. You can always concatenate them in
the front-end, in a view, or -if you really must- in a computed column
in the base table.

Until you get your design fixed, you can try using this kludge as a
temporary workaround:

ORDER BY LEFT(BadColumn, 3),
CAST(SUBSTRING(BadColumn, 4, LEN(BadColumn) - 3) AS integer)

However, this *will* break if you have any data in the table where the
part number doesn't follow the same layout. And it will perform very
bad, because the optimizer can't leverage an index on the BadColumn to
facilitate the sorting operation.

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: Access Report Completely Disregards IsNull in Order By
    ... Reports have their own sort order. ... do this by re-building the underlying query. ... the report, it rebuilds the query to have no sort ... The report ignored the IsNull portion of the order by. ...
    (microsoft.public.access.queries)
  • Re: Dependent ListBox
    ... That was the reason for the sort order button, to allow the user to search ... Adjust the listbox to show the value they want ... by adjusting the column widths and the sort order to only display the column ... For your report, once the user picks the ID or Name they are looking for, ...
    (microsoft.public.access.formscoding)
  • Re: Sorting
    ... The sort order in a report is determined by what is in the Sorting And Grouping dialog. ... A very simple report might sort by primary key order or by the ORDER BY clause of RecordSource (if bound to a query or SQL statement.) But don't rely on it: making a change, or using a WhereCondition in OpenReport could mean it is not reliable. ...
    (microsoft.public.access.reports)
  • Re: I need a report that has two columns, that each have two columns...
    ... Make a query that includes the Name, Picture, and the new field C. ... Make a report based on your Query. ... In Sorting and grouping, sort first on Pg, Sort Order: Ascending. ...
    (microsoft.public.access.reports)
  • RE: Report Sorting help
    ... If you build a filter based upon the user selection of sort order and set it ... equal to OpenArgs it will carry to the report. ...
    (microsoft.public.access.reports)