Order characters in one field

[Guest]

I have 5,000 sales reps with licenses in certain states. There is a field
in the reps table with state codes that person is licensed in. For example:

AL,TX,IN,WA,AZ

how can I reorder the field in alpha order ie.

AL,AZ,IN,TX,WA

Thank you,

Steven

 

[Douglas J. Steele]

At the risk of offending, you should not have a field with multiple values
in it.

Instead, you should have a second table that contains one row for each state
in which the rep is licensed. This second table need only have 2 fields
(RepID and StateID), although you could also include additional information
if you like (when did they get licensed? when does the license expire? what
sort of license is it? etc.)

 

[John Spencer]

If you can't follow Doug Steele's very good advice, post back.

Tell us what version of Access you are using, the name of your table and the
name of the field.
Also tell us whether or not you are familar with using VBA functions.

You will probably need a function that splits the StateID's into parts and
then sorts those parts and finally reassembles them and returns the value.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

I'm not too offended. Actually not at all. Thank you for your responses.

I was thinking of doing something slick so I would have only 5000 records in
a table and do a InStr() function on the states field. I thought the process
might go faster in testing a large group of transactions but you will
probably tell me that is not the case or that the difference in time would be
small.

I will probably have 5000 reps * avg 40 states / rep = 200,000 records.

Thank you for your help.

Steven

 

[Douglas J. Steele]

I suspect using InStr will actually be a little slower, but in either case,
there shouldn't be a heck of a lot difference.

If you're going to need to print lists of reps by state, or do statistics
like "how many reps are licensed in 4 states? in 5 states?", the two table*
approach is a must.

* Technically it's 3 tables. You've got a many-to-many relationship between
States and Reps. To resolve a many-to-many relationship, you introduce a 3rd
intersection table. If you don't already have a State table, it's probably a
good idea.