MS Access database support - Sorting by using parts of a string

Author

Sorting by using parts of a string

deancarstens@yahoo.com

2006-04-07, 11:32 am

Hi,

This is a tougher one, but I'm quite sure someone will have a solution
for this. Of course, a last minute thing thrown at me by my boss.

I have a unique identifier consisting of regions, districts, a road
class value and then sequential numbers, in that order, eg:
GA-GAD-C-005 The thing is that the sequential values don't follow onto
each other as it does the sorting on the road class value first (which
could be A, I or C). So, you'll have the first three parametres
correct, but the number skips values, which appears in a different
"group" for that district. I need to thus sort the roads by ignoring
the road class and only start by sorting it on the region, then
district and then the sequential number. Do I need to do this
programmatically? I'd like to do this in SQL however as most of my
reports and forms run off the SQL statements.

Your help will be GREATLY appreciated.

Dean Carstens

Scott Wilson
DFID Support to Feeder Roads
Ghana

MGFoster

2006-04-07, 1:31 pm

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Assuming the unique ID is always
2 chars - 3 chars - 1 char - 3 numerals:

SELECT unique_id, <other cols>
FROM table_name
WHERE ...
ORDER BY Left(unique_id,7) & Right(unique_id,3)

The string concatenation yields a unique_id without the road class;
you'd sort on that.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/ AwUBRDaaiIechKqOuFEg
EQJYUQCgldN6AgiVWmNJ
hEavuBA3uyFPR0IAnRoL

y8PT29cwcxet2XxVnEkL
gwd9
=p/vx
-----END PGP SIGNATURE-----

deancarstens@yahoo.com wrote:
> Hi,
>
> This is a tougher one, but I'm quite sure someone will have a solution
> for this. Of course, a last minute thing thrown at me by my boss.
>
> I have a unique identifier consisting of regions, districts, a road
> class value and then sequential numbers, in that order, eg:
> GA-GAD-C-005 The thing is that the sequential values don't follow onto
> each other as it does the sorting on the road class value first (which
> could be A, I or C). So, you'll have the first three parametres
> correct, but the number skips values, which appears in a different
> "group" for that district. I need to thus sort the roads by ignoring
> the road class and only start by sorting it on the region, then
> district and then the sequential number. Do I need to do this
> programmatically? I'd like to do this in SQL however as most of my
> reports and forms run off the SQL statements.

Tim Marshall

2006-04-07, 1:31 pm

deancarstens@yahoo.com wrote:

> I have a unique identifier consisting of regions, districts, a road
> class value and then sequential numbers, in that order, eg:
> GA-GAD-C-005 The thing is that the sequential values don't follow onto
> each other as it does the sorting on the road class value first (which
> could be A, I or C).

Warning - this is how I do it - if anyone sees a better way, for the
love of all that's holy, please dress me down and correct me!!!!

I didn't understand what you were saying after this, but in the example
you give, I'm assuming C is the road class, district is GAD and region
is GA. I'm also going to assume that any of these identifiers can vary
in length (it's easier if they are constant) and that they are always
separated by dashes and that road class will always be *AFTER* the
*SECOND* dash.

The following makes use of the instr() and mid() functions. Look them
up and familiarize yourself with them. The following may be hard to
follow, but it is (one line, no breaks mean to be in place:

mid((mid(& #91;Identifier],inst
r(1,[Identifier], "-")+1)),instr(1,
(mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")+1)

Which will, for the above example, give "C-005" as a result. You can
put this as a calculated expression somewhere in the query builder and
sort by it.

To ease the confusion the above may cause, look at how it's built.

mid takes a portion out of a specified string and instr returns a number.

1. the first instance of a dash: instr(1,[Identifier], "-") will
return the number 3

2. to use this number to get stuff coming after that dash, we want to
take the example and start at position 4, so add one to expression #1
and use it as the start position in mid():

mid(& #91;Identifier],inst
r(1,[Identifier], "-")+1)

This will return "GAD-C-005"

3. Let's repeat step #1 to find the location of the second dash in
what's remaining, "GAD-C-005":

instr(1, (mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")

This results in a 4 which is the position of the "-" in "GAD-C-005".

4. You want to start at position 5, so add 1 to the expression in #3
for 5. Now, use the mid expression to find what results in position 5
and onward of the expression derived in #2:

mid((mid(& #91;Identifier],inst
r(1,[Identifier], "-")+1)),instr(1,
(mid([Identifier], instr(1,[Identifier], "-")+1) ),"-")+1)

This sort of thing can be confusing with all the parenthesis.

IS there a better way? Probably, but I've been doing this sort of thing
this way for years...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Barry

2006-04-07, 1:31 pm

This can probably be done in the query without code. Are the fields
sizes consistent? In other words, is region always two characters,
districts always three, etc? If so, you can create fields in the query
using the Left and Mid functions to grab those fields and then sort on
them. If the fields are not of fixed width but do use hyphens, as your
exmplae suggests, you would need to also use the InStr function to find
the positions of the hyphens.

HTH,
Barry

David W. Fenton

2006-04-07, 1:31 pm

deancarstens@yahoo.com wrote in
news:1144426229.816799.57970@t31g2000cwb.googlegroups.com:

> I have a unique identifier consisting of regions, districts, a
> road class value and then sequential numbers, in that order, eg:
> GA-GAD-C-005 The thing is that the sequential values don't follow
> onto each other as it does the sorting on the road class value
> first (which could be A, I or C). So, you'll have the first three
> parametres correct, but the number skips values, which appears in
> a different "group" for that district. I need to thus sort the
> roads by ignoring the road class and only start by sorting it on
> the region, then district and then the sequential number. Do I
> need to do this programmatically? I'd like to do this in SQL
> however as most of my reports and forms run off the SQL
> statements.

This is denormalized data. Subparts of the value stored in the
single field have individual meanings that are independent of the
other parts.

This should be broken down into 4 fields.

This will allow each field to be indexed (you'll want four indexes,
a unique compound one on the four fields together, and one
non-unique each on the other three fields), and will vastly increase
the speed of sorting and selecting on subparts of your value.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Sponsored Links