Sort by non alpha criterion

[Guest]

I have records that relate in various ways to the Books of the Bible. My
question is, what is the best way to sort records according to the normal
order that books appear in the Bible?

ie, the sort order would be Genesis, Exodus, Leviticus, Numbers, etc...

(I have a vague recollection of seeing code in an earlier thread that sorted
according to the position of a word in a string - but I don't remember the
details)

 

[Douglas J. Steele]

Assuming you've got the books stored as rows in a table, add an additional
field to the table that indicates the desired sort order, and sort on it.

 

[John Nurick]

Hi Peter,

You need a table with one record for each book in your Bible (remember
that the various churches do not all recognise the same books as
canonical), with fields including

-the name by which you know it.
-the abbreviation that (I presume) is used in your records, e.g. 1 Cor.
for 1 Corinthians. Let's call this field Abbreviation. This field should
be the primary key of the table.
-a number (let's call it SortOrder) indicating its position in your
canon, e.g. Genesis 1, Exodus 2 and so on. This field should have an
index that prevents duplicate values being entered.
-maybe other fields, such as one to indicate Old Testament/New
Testament/Apocrypha.

You can then use a query that joins this table to the table that
contains your records (on the Abbreviation field and its counterpart in
the other table). Sort the query on the SortOrder field and there you
are.