Sorting Organization names

  • Thread starter Thread starter Tomk
  • Start date Start date
T

Tomk

is there a way to sort the list of names of organizations in a table and
ignore the word "the" preceeding a name?
 
Firstly, never sort a table per se, always use a query to sort records. The
exception is if the query is used as a RecordSource for a report, in which
case use an unsorted query and use the query's internal sorting and grouping
mecahnism to oreder the records.

As regards your question you can use the Replace function to return a value
which excludes the "the " (note the trailing space, its important). So in a
query you'd order it with:

ORDER BY Replace([Orgabization], "the ","")

To do this in query design view, in the 'field' row of a blnak column enter:

SortColumn: Replace([Organization], "the ","")

In its 'sort' row select 'Ascending'.

If you are opening the query directly or using it as the basis for a form
you can uncheck the 'show' checkbox. If using it as the basis for a report
then leave it checked and sort the report internally on SortColumn using the
sorting and grouping dialogue in report design view. You don't need to show
the SortColumn in the report of course.

The reason for the trailing space BTW is that 'the' might be a substring of
an organization name e.g. 'The theatre company'; you don't want this to be
returned as 'atre company' which is what would happen without the trailing
space. You might have 'the ' as a word within a name or as a substring at
the end of a word of course, which would be replaced for sorting purposes
with a zero length string, but that's probably not going to make any
significant difference to the resulting sort order.

Incidentally the Replace function doesn't actually remove any data from the
table, it simply returns a value with the string in question replaced by
another string. To permanently remove it you'd have to call it in an UPDATE
query, not a normal SELECT query.

Ken Sheridan
Stafford, England
 
SELECT Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1) AS SortedNames
FROM TableName
ORDER BY Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1);
 
Ken, you could change the replace to only replace the first occurence of
"The ", but you still could have un-intended replacements.

Replace([Organization],"The ","",1,1)
replaces only the first incidence of "The " in the name
"Wythe Corporation" would become "WYCorporation"

Safer would be to sort by

IIF([Organization] Like "The *",Mid([Organization],5),[Organization])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ken said:
Firstly, never sort a table per se, always use a query to sort records. The
exception is if the query is used as a RecordSource for a report, in which
case use an unsorted query and use the query's internal sorting and grouping
mecahnism to oreder the records.

As regards your question you can use the Replace function to return a value
which excludes the "the " (note the trailing space, its important). So in a
query you'd order it with:

ORDER BY Replace([Orgabization], "the ","")

To do this in query design view, in the 'field' row of a blnak column enter:

SortColumn: Replace([Organization], "the ","")

In its 'sort' row select 'Ascending'.

If you are opening the query directly or using it as the basis for a form
you can uncheck the 'show' checkbox. If using it as the basis for a report
then leave it checked and sort the report internally on SortColumn using the
sorting and grouping dialogue in report design view. You don't need to show
the SortColumn in the report of course.

The reason for the trailing space BTW is that 'the' might be a substring of
an organization name e.g. 'The theatre company'; you don't want this to be
returned as 'atre company' which is what would happen without the trailing
space. You might have 'the ' as a word within a name or as a substring at
the end of a word of course, which would be replaced for sorting purposes
with a zero length string, but that's probably not going to make any
significant difference to the resulting sort order.

Incidentally the Replace function doesn't actually remove any data from the
table, it simply returns a value with the string in question replaced by
another string. To permanently remove it you'd have to call it in an UPDATE
query, not a normal SELECT query.

Ken Sheridan
Stafford, England

Tomk said:
is there a way to sort the list of names of organizations in a table and
ignore the word "the" preceeding a name?
Click to expand...
Click to expand...
 
SELECT Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1) AS SortedNames
FROM TableName
ORDER BY Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1);
Click to expand...

Eh? This will sort the names by the second word in the name, whatever the
first word might be!
 
But the 1st word is "The" which they don't want - I think / hope :-)
Click to expand...

Ah. I interpreted it as a desire to sort in an order like:

Abrams Tank Corp.
The Accurate Answer Inc.
Ala Moana Shopping Center

in which case you would want to only *selectively* remove the first word.
 
Hi John you are correct - as always.

I should have put

SELECT TableName.FieldName
FROM TableName
ORDER BY Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1);



ooops !!

So Mr/Ms/Mrs Tomk - this is your answers

Add the field to a query with the names in
in a blank column put this
SortedName:Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1);
Take off the Tick in the "show" row for this colomn

This will give

Abrams Tank Corp.
The Accurate Answer Inc.
Ala Moana Shopping Center

As John said you needed - sorry
 
Thank you for the help. I beleive my problem is solved.
--
regards,
Tom


Ken Sheridan said:
Firstly, never sort a table per se, always use a query to sort records. The
exception is if the query is used as a RecordSource for a report, in which
case use an unsorted query and use the query's internal sorting and grouping
mecahnism to oreder the records.

As regards your question you can use the Replace function to return a value
which excludes the "the " (note the trailing space, its important). So in a
query you'd order it with:

ORDER BY Replace([Orgabization], "the ","")

To do this in query design view, in the 'field' row of a blnak column enter:

SortColumn: Replace([Organization], "the ","")

In its 'sort' row select 'Ascending'.

If you are opening the query directly or using it as the basis for a form
you can uncheck the 'show' checkbox. If using it as the basis for a report
then leave it checked and sort the report internally on SortColumn using the
sorting and grouping dialogue in report design view. You don't need to show
the SortColumn in the report of course.

The reason for the trailing space BTW is that 'the' might be a substring of
an organization name e.g. 'The theatre company'; you don't want this to be
returned as 'atre company' which is what would happen without the trailing
space. You might have 'the ' as a word within a name or as a substring at
the end of a word of course, which would be replaced for sorting purposes
with a zero length string, but that's probably not going to make any
significant difference to the resulting sort order.

Incidentally the Replace function doesn't actually remove any data from the
table, it simply returns a value with the string in question replaced by
another string. To permanently remove it you'd have to call it in an UPDATE
query, not a normal SELECT query.

Ken Sheridan
Stafford, England

Tomk said:
is there a way to sort the list of names of organizations in a table and
ignore the word "the" preceeding a name?
Click to expand...
Click to expand...
 
oops,
i build a query as you discribed, but it will only sort on the second word
of selected item in my table
 
John,
of all the different options you guys presented, this one best fits the
database I am using and for which I needed the sort.
thank you
--

regards,Tom


John Spencer said:
Ken, you could change the replace to only replace the first occurence of
"The ", but you still could have un-intended replacements.

Replace([Organization],"The ","",1,1)
replaces only the first incidence of "The " in the name
"Wythe Corporation" would become "WYCorporation"

Safer would be to sort by

IIF([Organization] Like "The *",Mid([Organization],5),[Organization])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Ken said:
Firstly, never sort a table per se, always use a query to sort records. The
exception is if the query is used as a RecordSource for a report, in which
case use an unsorted query and use the query's internal sorting and grouping
mecahnism to oreder the records.

As regards your question you can use the Replace function to return a value
which excludes the "the " (note the trailing space, its important). So in a
query you'd order it with:

ORDER BY Replace([Orgabization], "the ","")

To do this in query design view, in the 'field' row of a blnak column enter:

SortColumn: Replace([Organization], "the ","")

In its 'sort' row select 'Ascending'.

If you are opening the query directly or using it as the basis for a form
you can uncheck the 'show' checkbox. If using it as the basis for a report
then leave it checked and sort the report internally on SortColumn using the
sorting and grouping dialogue in report design view. You don't need to show
the SortColumn in the report of course.

The reason for the trailing space BTW is that 'the' might be a substring of
an organization name e.g. 'The theatre company'; you don't want this to be
returned as 'atre company' which is what would happen without the trailing
space. You might have 'the ' as a word within a name or as a substring at
the end of a word of course, which would be replaced for sorting purposes
with a zero length string, but that's probably not going to make any
significant difference to the resulting sort order.

Incidentally the Replace function doesn't actually remove any data from the
table, it simply returns a value with the string in question replaced by
another string. To permanently remove it you'd have to call it in an UPDATE
query, not a normal SELECT query.

Ken Sheridan
Stafford, England

Tomk said:
is there a way to sort the list of names of organizations in a table and
ignore the word "the" preceeding a name?
Click to expand...
Click to expand...
Click to expand...
 
I think some of the problems here are that we don’t know what it is you are
looking for.

If you have a table containing these records in a field call FieldName

And you sort them apathetically you will get this as it sorts on the 1st
letter then the next etc

Banana
Some grapes
The apple
The potatoes

If you use the formula given before you will get this as it sorts on the 1st
letter of the 2nd word

Mid(TableName!FieldName,InStr(TableName!FieldName," ")+1);

The apple
Banana
Some grapes
The potatoes

If you use this (different formula) – this will sort on the 1st letter of
the 2nd word “if†the 1ft word is “The “ (THE with a space)after. If the 1st
word is not "The" then it will sort on the 1st letter of the 1st word.

NewSortedName: IIf([TableName]![FieldName] Like "The
*",Mid([TableName]![FieldName],InStr([TableName]![FieldName],"
")+1),[TableName]![FieldName])

Banana
Some grapes
The apple
The potatoes

(It look the same as the 1st example jut because of the words I choose but
it would different results if the words changed – but it’s Sunday morning so
bear with me).

Hope this helps

If not you need to give some examples of the text you are sorting and what
you need the result to be
 

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

Access Sort Order 7
Excel Transpose Cells in Excel 4
organ article 1
Breakthrough in 3D organ printing 0
Table / organization question 2
Alpha-sort addresses, ignoring first 1-3 numerals 5
Best way to track availability in a form 0
multi-value field 1

Back
Top