Can Access correctly sort Library of Congress Call Numbers?

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

Guest

I'm working in a library and we're creating a database of collection items,
but I can't seem to get Access to sort them by Library of Congress call
number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
change the sort setting? Any idea how to create a new type of Access field
for this data that would sort correctly?
 
William Gee said:
I'm working in a library and we're creating a database of collection items,
but I can't seem to get Access to sort them by Library of Congress call
number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
change the sort setting? Any idea how to create a new type of Access field
for this data that would sort correctly?
Click to expand...


Tables are not sorted, so use a query that selects the
appropriate fields from the table. Add a calculated field
like:
Expr: Val(Mid(LCnumfield, 2))
and set it to sort ascending.

You may need to refine this, but I don't know what it might
be.
 
On Sat, 26 Feb 2005 14:57:02 -0800, "William Gee" <William
I'm working in a library and we're creating a database of collection items,
but I can't seem to get Access to sort them by Library of Congress call
number correctly. Access sorts, for instance, S1.U556, S115.E32, S16.I6732 in
that order, rather than S1.U556, S16.I6732, S115.E32. Any ideas on how to
change the sort setting? Any idea how to create a new type of Access field
for this data that would sort correctly?
Click to expand...

As Marshall says, tables HAVE NO ORDER - they're unordered "heaps" of
data. You need to use a Query to sort records.

Access is sorting these values as single text strings; S1.U556 sorts
before S115.E32 because the character . is before the character 1 in
the collating sequence for strings. Access has NO way to know that you
*intend* the 1 and the 115 to be numeric values. I'm not sure what the
range of variation of these codes might be. If a valid call number
ALWAYS consists of two portions, each consisting of a single
alphabetic character followed by a number, you might want to consider
storing the call number in *four* fields rather than one: TextMajor
(Text 1), NumMajor (Integer), TextMinor (Text 1), and NumMinor
(Integer). S1.U556 would be stored as S, 1, U, and 556 respectively;
you can write an expression to concatenate the four fields for display
purposes. And they'll sort correctly.

To sort these values with the existing structure you'll need to use
some complex Mid() and InStr() expressions to parse out the letters
and numbers, and the sort will likely be VERY inefficient.

John W. Vinson[MVP]
 
If I remember my basic office skills correctly, Access is sorting the
values correctly by standard methods S115 would come before S16 since
the 3rd character '1' comes before '6'. Access is viewing the values as
TEXT as opposed to NUMBERS. The values that you need can be converted
from text to numeric by writting some code to extract the values and
convert them. The code should be relatively simply, but a bit longer
than I can come up with spur of the moment.

David H
Come on baby light my fire: www.spreadFireFox.com
 
Any idea how to create a new type of Access field
for this data that would sort correctly?
Click to expand...


SELECT Whatever
FROM Wherever
ORDER BY CInt(Val(Mid$(CallNumber,2))) ASC


Hope that helps


Tim F
 
William Gee said:
I'm working in a library and we're creating a database of collection
items, but I can't seem to get Access to sort them by Library of
Congress call number correctly. Access sorts, for instance, S1.U556,
S115.E32, S16.I6732 in that order, rather than S1.U556, S16.I6732,
S115.E32. Any ideas on how to change the sort setting? Any idea how
to create a new type of Access field for this data that would sort
correctly?
Click to expand...

This question has been answered in another group to which you posted it
separately.
 
Back
Top