sort by partial string

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

Guest

Is it possible to sort by part of a string in a given field? Here's my
example:

In the [RoomNumber] field, room number is specified with a string such as
19A-01, 19A-02, 19A-03, 20A-01, 20A-02, etc.
In the [Area] field, area may be listed as A.01, A.02, A.03, B.01, B.02,
B.03, etc.
There are multiple combinations. For instance, there is:
A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
B.01 / 20A-01
B.01 / 20A-02

I would like to be able to sort ascending by the "19A" portion of the room
number string first, then by the area field, then by the "01, 02, 03" portion
of the room number string, so the results would be as follows:

A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 20A-01
B.01 / 20A-02

I was thinking maybe there was a way with a wildcard in the criteria, but I
have been unsuccessful in my attempts. Normally, data in the room number
field does not contain a string, but in this instance, we needed to organize
it this way. I am hopeful I can manipulate the data to achieve the desired
results. Any suggestions are appreciated! Thank you!
 
Your best bet would be to store each part in a separate field. Then you
would have no problem with sorting. You can concatenate these values
together any time you need them on a report of form.

BTW, this is the exact reason that the First Normal Form of Relational
Database Design says that each field shall be atomic (that is, hold only one
piece of data).

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Kevin

If your field is a "string" (text, character), you will have to parse out
the portions on which to want to sort this way. Consider using the Left(),
Mid(), and Right() functions in a query to get the "pieces" by which you
wish to sort, then sort in that query.

An alternative approach would be to modify your data structure and create
fields to hold the alpha and the numeric portions separately, rather than a
single field with all the pieces jammed together. You could still use a
query to re-jam them, if needed.

Good luck

Jeff Boyce
<Access MVP>
 
Assuming the format of those room numbers is consistent, you can use:

select * from Rooms as R
order by mid(R.[Room Number], InStr (1, R.[Rom Number], "/") + 2);

Whether it is a good idea to leave the table structure as is, is another
question. But the above will get what you want, given the structure you
report.

Good Luck!
--
Chaim


Jeff Boyce said:
Kevin

If your field is a "string" (text, character), you will have to parse out
the portions on which to want to sort this way. Consider using the Left(),
Mid(), and Right() functions in a query to get the "pieces" by which you
wish to sort, then sort in that query.

An alternative approach would be to modify your data structure and create
fields to hold the alpha and the numeric portions separately, rather than a
single field with all the pieces jammed together. You could still use a
query to re-jam them, if needed.

Good luck

Jeff Boyce
<Access MVP>

Kevin said:
Is it possible to sort by part of a string in a given field? Here's my
example:

In the [RoomNumber] field, room number is specified with a string such as
19A-01, 19A-02, 19A-03, 20A-01, 20A-02, etc.
In the [Area] field, area may be listed as A.01, A.02, A.03, B.01, B.02,
B.03, etc.
There are multiple combinations. For instance, there is:
A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
B.01 / 20A-01
B.01 / 20A-02

I would like to be able to sort ascending by the "19A" portion of the room
number string first, then by the area field, then by the "01, 02, 03" portion
of the room number string, so the results would be as follows:

A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 20A-01
B.01 / 20A-02

I was thinking maybe there was a way with a wildcard in the criteria, but I
have been unsuccessful in my attempts. Normally, data in the room number
field does not contain a string, but in this instance, we needed to organize
it this way. I am hopeful I can manipulate the data to achieve the desired
results. Any suggestions are appreciated! Thank you!
Click to expand...
Click to expand...
 
Ignore my first post. Reread your original post, Kevin, which I did not do in
its entirety the first time. My bad.

I was wondering why Mr. Boyce didn't present that simple SQL that I
presented. He obviously knows how to read.

Oh well ....
--
Chaim


Chaim said:
Assuming the format of those room numbers is consistent, you can use:

select * from Rooms as R
order by mid(R.[Room Number], InStr (1, R.[Rom Number], "/") + 2);

Whether it is a good idea to leave the table structure as is, is another
question. But the above will get what you want, given the structure you
report.

Good Luck!
--
Chaim


Jeff Boyce said:
Kevin

If your field is a "string" (text, character), you will have to parse out
the portions on which to want to sort this way. Consider using the Left(),
Mid(), and Right() functions in a query to get the "pieces" by which you
wish to sort, then sort in that query.

An alternative approach would be to modify your data structure and create
fields to hold the alpha and the numeric portions separately, rather than a
single field with all the pieces jammed together. You could still use a
query to re-jam them, if needed.

Good luck

Jeff Boyce
<Access MVP>

Kevin said:
Is it possible to sort by part of a string in a given field? Here's my
example:

In the [RoomNumber] field, room number is specified with a string such as
19A-01, 19A-02, 19A-03, 20A-01, 20A-02, etc.
In the [Area] field, area may be listed as A.01, A.02, A.03, B.01, B.02,
B.03, etc.
There are multiple combinations. For instance, there is:
A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
B.01 / 20A-01
B.01 / 20A-02

I would like to be able to sort ascending by the "19A" portion of the room
number string first, then by the area field, then by the "01, 02, 03" portion
of the room number string, so the results would be as follows:

A.01 / 19A-01
A.01 / 19A-02
A.01 / 19A-03
B.01 / 19A-01
B.01 / 19A-02
B.01 / 19A-03
A.01 / 20A-01
A.01 / 20A-02
A.01 / 20A-03
B.01 / 20A-01
B.01 / 20A-02

I was thinking maybe there was a way with a wildcard in the criteria, but I
have been unsuccessful in my attempts. Normally, data in the room number
field does not contain a string, but in this instance, we needed to organize
it this way. I am hopeful I can manipulate the data to achieve the desired
results. Any suggestions are appreciated! Thank you!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sql statement 1
SELECT Statement Question 5
Selecting whole records while using a MaxOf function. 5
Query, Ranges time group by concept 1
extract 3 numbers from a string 0
Create List of Dates that fall between 2 Dates 2
Counts number of entries per day in query 4
next date?? 1

Back
Top