Need Help in Sorting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a data base with a field that is a combination of numbers and characters such as 1.4.C.1; 1.4.C.2; 1.4.C.11; and so forth. If I sort on this field in ascending order, these examples I've listed will sort as 1.4.C.1; 1.4.C.11; and 1.4.C.2. How can I get the field to recognize 1.4.C.1 as 1.4.C.01 without typing in the zero? I am not an expert programmer and need help as soon as possible.
 
The best way is to split your field into 4 separate
fields, so 1.4.C.1 is stored in fields Level1 (Num),
Level2 (Num), Level3 (Text), Level4 (Num) as
Level1=1, Level2=4, Level3="C", Level4=1

If you do so, your table becomes more normalized.
Normalization is a method of structuring your data into
set of related tables in order to query and manipulate
data easier way. One of the normalization rules states
that data in each field must be atomic. Your field
violates that rule and that is why you have problems with
simple operation as sorting.

If you can not normalize tha table, try something like
this:
SELECT LEFT(YourFiled,6) AS BeginPart, MID(YourField,7)
AS EndPart
ORDER BY LEFT(YourFiled,6),MID(YourField,7)

This will work only and only if the beginning of your
value is like "1.4.C." = allways has exactly 6
characters. In other words, you do not have values
like "10.14.C.". Also, if you have something
like "1.4.C12" instead of "1.4.C.12" any query that
attempts to parse the string will return incorrect
results. Only healthy solution is decomposing the field
into separate fields.

:-)


-----Original Message-----
I have a data base with a field that is a combination of
Click to expand...
numbers and characters such as 1.4.C.1; 1.4.C.2;
1.4.C.11; and so forth. If I sort on this field in
ascending order, these examples I've listed will sort as
1.4.C.1; 1.4.C.11; and 1.4.C.2. How can I get the field
to recognize 1.4.C.1 as 1.4.C.01 without typing in the
zero? I am not an expert programmer and need help as
soon as possible.
 
Vonda,

This is part of the reason we normalize our databases. This field
obviously has more than one piece of data in it, if the various
segments have any meaning at all. If these were in 4 separate fields,
then you could sort by the 4 fields and get the output you want.

If you don't want to go to that much trouble, you could create a
function that parses the field into its respective pieces and then use
those values to sort on.

--
HTH

Dale Fye


I have a data base with a field that is a combination of numbers and
characters such as 1.4.C.1; 1.4.C.2; 1.4.C.11; and so forth. If I
sort on this field in ascending order, these examples I've listed will
sort as 1.4.C.1; 1.4.C.11; and 1.4.C.2. How can I get the field to
recognize 1.4.C.1 as 1.4.C.01 without typing in the zero? I am not an
expert programmer and need help as soon as possible.
 
----- Vonda wrote: -----

I have a data base with a field that is a combination of numbers and characters such as 1.4.C.1; 1.4.C.2; 1.4.C.11; and so forth. If I sort on this field in ascending order, these examples I've listed will sort as 1.4.C.1; 1.4.C.11; and 1.4.C.2. How can I get the field to recognize 1.4.C.1 as 1.4.C.01 without typing in the zero? I am not an expert programmer and need help as soon as possible.


make a query:
field: Val([enter the name of you field])
Sort: Ascending
show: turn this off as you dont need to see this field

go to the next column
field: enter the name of your field
sort: Ascending

this will sort it the way you want.
 
Dear Jane:

I call this the Dewey Decimal problem. Values with letters and/or
multiple decimal points in them are certainly not numbers. Since you
cannot store them or represent them as numbers, you store them as
strings. But they don't logically sort as strings.

Rather, each section of the number, as delineated by decimal points,
sorts with each other matching section.

In order to do this, I recommend the values be stored in a fashion
that will sort, and then displayed as though they had been stored in
the more standard fashion. In addition, you may want to be able to
enter new values in the decimal notation but store them in the same
internal fashion that sorts.

Step one is to decide the maximum number of section you may have in a
number and the maximum number of sections you will accomodate. Create
a text field whose legth is the product of those two numbers.

Since you want to accomodate values that are alphabetic characters as
well as digits, you need to decide whether the alpha values will sort
corrctly when left justified or right justified. Does BC come before
or after C?

You will need a function that accepts a multi-section value with
decimal points and changes it to the internal version, and a function
that converts the other way around.

Finally, you need a "Fill" character that sorts "early" (before
anything else) and that never occurs within the values entered. I
would suggest a space would be good.

So, just what is this internal format. Assuming 4 sections with 4
characters each, and with space as the "Fill" character, (but I
represent the spacest with a "^" so you can see them) your values
would be stored:

1.4.C.1 ^^^1^^^4^^^C^^^1
1.4.C.2 ^^^1^^^4^^^C^^^2
1.4.C.11 ^^^1^^^4^^^C^^11

That's the overview of what may do it for you.

Sorry this is a bit complex, but it is a complex problem to solve.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top