Re: Change report sort order
From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 03/12/04
- Next message: Phil: "percentages in quries"
- Previous message: wohtohai: "split field into two"
- In reply to: Ed Sch: "Re: Change report sort order"
- Next in thread: Ed Sch: "Re: Change report sort order"
- Reply: Ed Sch: "Re: Change report sort order"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Mar 2004 10:56:16 -0600
Dear Ed:
I expect you have some rules you would want to follow in how this is
ordered, but just what you wish is not entirely clear.
In order to make this work, the general approach would have to be to
parse the column into component parts that are intended to have an
effect on the ordering. For 123 A-11 these component parts might be
123, A, and 11. For each part, decide whether you want it ordered
numerically or alphabetically. For example, the set 1, 2, 3, 4, 10,
11, 12 is ordered numerically, but if ordered alphabetically it would
be 1, 10, 11, 12, 2, 3, 4. So, after you parse it, convert the
numerical parts into numbers.
Create a calculated column to return each part of the parsing, in the
order you want to sort (highest sort order first) and set the sorting
to use them. You can display them at first to help test the parsing,
then make them not display later when it's working properly.
An important point is this. You could have created 3 separate columns
for the 3 values and then assemble the components parts into one when
you display it. This might be done using an integer, then a text
column of 1 character, then another integer. Not only can you sort
this directly without first parsing, but you can even create an index
for the table on that, making it operate very fast. It is usually
easier to assemble the pieces for display purposes than it is to parse
it for calculation purposes. These are some of the reasons why the
rule of "atomicity" has been formulated for database design. The rule
simply says not to construct a column out of multiple pieces that must
sometimes be considered separately.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Fri, 12 Mar 2004 03:31:09 -0800, Ed Sch
<anonymous@discussions.microsoft.com> wrote:
>
> Hi Tom... Thanks for responding. In this instance, yes, the first three digits are always followed by a hyphen, followed by numbers. I also need a solution to deal with the following format (123 A-1, 123 A-2 etc). The hyphen was bad enough, but this group also has a space as well. Appreciate your help. Ed
>
> ----- Tom Ellison wrote: -----
>
> Dear Ed:
>
> The procedure would be to split this column in two, then sort by those
> two parts.
>
> Before tackling the specifics of this, I need to know:
>
> - Does the portion left of the hyphen always have 3 digits? If not,
> do you want to sort alphabetically (1, 10, 11, 12, 2, 20, 21, 3, . .
> ..) or numerically (1, 2, 3, 10, 11, 12, 20, 21, . . .)?
>
> - Is there always exactly one hyphen in the value? If not, please
> describe the variations that may be found and how you want to handle
> them.
>
> The problem is really in your question itself. 123-1 is NOT a number.
> It could be considered as two numbers, 123 and 1, with a hyphen
> between. It could be considered as an arithmetic problem, 123-1 =
> 122.
>
> I'm not saying this just to be picky, but because this is the way the
> computer sees it. It is, as you also say, a text field. It cannot be
> both a text field and a number, even if the text field did contain
> only pure numbers. Sorting numbers and sorting text is decidedly
> different.
>
> Anyway, I think I can understand what you probably mean, but need just
> a bit more information to proceed.
>
> Tom Ellison
> Microsoft Access MVP
> Ellison Enterprises - Your One Stop IT Experts
>
>
> On Thu, 11 Mar 2004 11:36:05 -0800, Ed Sch
> <anonymous@discussions.microsoft.com> wrote:
>
> >How can I make my access report display the following numbers (123-1, 123-10, 123-2, 123-3 as produced by the query) display as (123-1, 123-2, 123-3, 123-10)? I have tried the suggestion of: ORDER BY Val([MyTextField]), but that did not correct the problem. Thanks.
>
>
- Next message: Phil: "percentages in quries"
- Previous message: wohtohai: "split field into two"
- In reply to: Ed Sch: "Re: Change report sort order"
- Next in thread: Ed Sch: "Re: Change report sort order"
- Reply: Ed Sch: "Re: Change report sort order"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
