Bible Query

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

Greetings. I am using MS Office XP Access. I am a
newbie, so if you can help me, please tell me what to do
using very simple English and the procedures step-by-
step.

I have a table with three columns: In col. A are the
Bible verses, such as Gen 1:1, Gen 1:2, etc. In col. B
are the Hebrew words for each Bible verse, one word in
each row. In col. C are the English translations of the
words in col. B. There are no blank rows in the database
(that is, Gen 1:2 immediately follows Gen 1:1). It would
look something like this (the Hebrew font cannot be
displayed here, of course):

A B C
Gen 1:1 HHH in
Gen 1:1 HHHH beginning
Gen 1:1 HH God
Gen 1:1 HHHHH created

snip snip snip

Gen 1:2 HHHH and
Gen 1:2 HHHHHH the earth

etc., etc.

Is it possible to run a query or whatever so I will be
able to see in rows (perhaps in memo fields?) the entire
verses of Genesis (and the rest of the Bible books) as
follows:

Gen 1:1 in beginning God created the heavens and the earth.
Gen 1:2 and the earth etc., etc.

I would deeply appreciate any help you could give me in
trying to solve this problem because I don't think I will
ever be able to solve it on my own, though I have been
trying. But please keep in mind that I am an Access
newbie. Thank you.
 
The first problem is that you need another field to specify the ORDER of the
words within each bible verse. If this is an Access or SQL database there is no
innate order, so combining your first four sample lines could end up with

Gen 1:1 beginning God in created

or any other of 24 possibilities.

You can use a function to combine the values in the various fields. Duane
Hookom has one he has published many times

This is probably an old version of his code.

Your query would look something like:

SELECT DISTINCT ColumnA,
Concatenate("SELECT ColumnB
FROM YourTable
WHERE ColumnA=""" & ColumnA &
""" ORDER BY SomeWordOrderColumn") as Phrase
FROM YourTable

The entire section in the concatenate function should be on one line, but for
ease of reading and understanding, I've posted it on multiple lines.

Paste the function below into a module and save the module as modStringCode (or
some name OTHER THAN Concatenate).

----- Quoting Duane -----------------------------------
I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO while
the default for 2000 and newer is ADO.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ===========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
Thanks, John, but as I mentioned I am an extreme newbie,
so you'll have to instruct me as you would a five-year
old. I tried the code you provided, but I keep getting an
error message. Shouln't Col. C (which contains the
English) be included somewhere in the code?
-----Original Message-----
The first problem is that you need another field to specify the ORDER of the
words within each bible verse. If this is an Access or SQL database there is no
innate order, so combining your first four sample lines could end up with

Gen 1:1 beginning God in created

or any other of 24 possibilities.

You can use a function to combine the values in the various fields. Duane
Hookom has one he has published many times

This is probably an old version of his code.

Your query would look something like:

SELECT DISTINCT ColumnA,
Concatenate("SELECT ColumnB
FROM YourTable
WHERE ColumnA=""" & ColumnA &
""" ORDER BY SomeWordOrderColumn") as Phrase
FROM YourTable

The entire section in the concatenate function should be on one line, but for
ease of reading and understanding, I've posted it on multiple lines.

Paste the function below into a module and save the module as modStringCode (or
some name OTHER THAN Concatenate).

----- Quoting Duane -----------------------------------
I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO while
the default for 2000 and newer is ADO.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ===========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len (pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP
Greetings. I am using MS Office XP Access. I am a
newbie, so if you can help me, please tell me what to do
using very simple English and the procedures step-by-
step.

I have a table with three columns: In col. A are the
Bible verses, such as Gen 1:1, Gen 1:2, etc. In col. B
are the Hebrew words for each Bible verse, one word in
each row. In col. C are the English translations of the
words in col. B. There are no blank rows in the database
(that is, Gen 1:2 immediately follows Gen 1:1). It would
look something like this (the Hebrew font cannot be
displayed here, of course):

A B C
Gen 1:1 HHH in
Gen 1:1 HHHH beginning
Gen 1:1 HH God
Gen 1:1 HHHHH created

snip snip snip

Gen 1:2 HHHH and
Gen 1:2 HHHHHH the earth

etc., etc.

Is it possible to run a query or whatever so I will be
able to see in rows (perhaps in memo fields?) the entire
verses of Genesis (and the rest of the Bible books) as
follows:

Gen 1:1 in beginning God created the heavens and the earth.
Gen 1:2 and the earth etc., etc.

I would deeply appreciate any help you could give me in
trying to solve this problem because I don't think I will
ever be able to solve it on my own, though I have been
trying. But please keep in mind that I am an Access
newbie. Thank you.
Click to expand...
.
Click to expand...
 
Yes, it should have been ColumnC vice ColumnB. My error.

WHAT error did you get?

Also, do you have a column that specifies the word order? If not, you will get
unreliable word order back.

Can you post the SQL statement that you did try?

Do you know if you are using DAO or ADO? What version of Access are you using?
Is the database saved as an ADP (a project) or is it saved as an .mdb?

Also if your columns are named Col. C, you will have to surround them with
brackets or Access will have problems

Perhaps the SQL statement you need would be

SELECT DISTINCT [Col. A],
Concatenate("SELECT [Col. C]
FROM YourTable
WHERE [Col. A]=""" & [Col. A] & """") AS Phrase
FROM YourTable

If you are doing this in the query grid, then let me know that also.

Can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give me a hint in where I am mis-advising you or where the problem in
communication lies.
Thanks, John, but as I mentioned I am an extreme newbie,
so you'll have to instruct me as you would a five-year
old. I tried the code you provided, but I keep getting an
error message. Shouln't Col. C (which contains the
English) be included somewhere in the code?
-----Original Message-----
The first problem is that you need another field to specify the ORDER of the
words within each bible verse. If this is an Access or SQL database there is no
innate order, so combining your first four sample lines could end up with

Gen 1:1 beginning God in created

or any other of 24 possibilities.

You can use a function to combine the values in the various fields. Duane
Hookom has one he has published many times

This is probably an old version of his code.

Your query would look something like:

SELECT DISTINCT ColumnA,
Concatenate("SELECT ColumnB
FROM YourTable
WHERE ColumnA=""" & ColumnA &
""" ORDER BY SomeWordOrderColumn") as Phrase
FROM YourTable

The entire section in the concatenate function should be on one line, but for
ease of reading and understanding, I've posted it on multiple lines.

Paste the function below into a module and save the module as modStringCode (or
some name OTHER THAN Concatenate).

----- Quoting Duane -----------------------------------
I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO while
the default for 2000 and newer is ADO.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ===========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len (pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP
Greetings. I am using MS Office XP Access. I am a
newbie, so if you can help me, please tell me what to do
using very simple English and the procedures step-by-
step.

I have a table with three columns: In col. A are the
Bible verses, such as Gen 1:1, Gen 1:2, etc. In col. B
are the Hebrew words for each Bible verse, one word in
each row. In col. C are the English translations of the
words in col. B. There are no blank rows in the database
(that is, Gen 1:2 immediately follows Gen 1:1). It would
look something like this (the Hebrew font cannot be
displayed here, of course):

A B C
Gen 1:1 HHH in
Gen 1:1 HHHH beginning
Gen 1:1 HH God
Gen 1:1 HHHHH created

snip snip snip

Gen 1:2 HHHH and
Gen 1:2 HHHHHH the earth

etc., etc.

Is it possible to run a query or whatever so I will be
able to see in rows (perhaps in memo fields?) the entire
verses of Genesis (and the rest of the Bible books) as
follows:

Gen 1:1 in beginning God created the heavens and the earth.
Gen 1:2 and the earth etc., etc.

I would deeply appreciate any help you could give me in
trying to solve this problem because I don't think I will
ever be able to solve it on my own, though I have been
trying. But please keep in mind that I am an Access
newbie. Thank you.
Click to expand...
.
Click to expand...
Click to expand...
 
Everything stops at the following:

rs As New ADODB.Recordset

How do I specify the word order? What kind of column is
this? My table does contain a "KeyID" column. Is this
what you mean?

My database is saved as .mdb

This is the SQL statement I posted:

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """") AS Phrase
FROM BIBLE

A = the column with the Bible verse names (Gen 1:1, Gen
1:2, etc.)
C = the column with the English translation of the Hebrew
words in column B
-----Original Message-----
Yes, it should have been ColumnC vice ColumnB. My error.

WHAT error did you get?

Also, do you have a column that specifies the word order? If not, you will get
unreliable word order back.

Can you post the SQL statement that you did try?

Do you know if you are using DAO or ADO? What version of Access are you using?
Is the database saved as an ADP (a project) or is it saved as an .mdb?

Also if your columns are named Col. C, you will have to surround them with
brackets or Access will have problems

Perhaps the SQL statement you need would be

SELECT DISTINCT [Col. A],
Concatenate("SELECT [Col. C]
FROM YourTable
WHERE [Col. A]=""" & [Col. A] & """") AS Phrase
FROM YourTable

If you are doing this in the query grid, then let me know that also.

Can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give me a hint in where I am mis-advising you or where the problem in
communication lies.
Thanks, John, but as I mentioned I am an extreme newbie,
so you'll have to instruct me as you would a five-year
old. I tried the code you provided, but I keep getting an
error message. Shouln't Col. C (which contains the
English) be included somewhere in the code?
-----Original Message-----
The first problem is that you need another field to specify the ORDER of the
words within each bible verse. If this is an Access or SQL database there is no
innate order, so combining your first four sample lines could end up with

Gen 1:1 beginning God in created

or any other of 24 possibilities.

You can use a function to combine the values in the various fields. Duane
Hookom has one he has published many times

This is probably an old version of his code.

Your query would look something like:

SELECT DISTINCT ColumnA,
Concatenate("SELECT ColumnB
FROM YourTable
WHERE ColumnA=""" & ColumnA &
""" ORDER BY SomeWordOrderColumn") as Phrase
FROM YourTable

The entire section in the concatenate function should
Click to expand...
be
on one line, but for
ease of reading and understanding, I've posted it on multiple lines.

Paste the function below into a module and save the module as modStringCode (or
some name OTHER THAN Concatenate).

----- Quoting Duane -----------------------------------
I use a generic Concatenate() function. The code is listed below with both
ADO and DAO. There are comments regarding which lines
Click to expand...
to
comment or
uncomment based on which library you prefer. Access 97
Click to expand...
is
mostly DAO while
the default for 2000 and newer is ADO.

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines========
'====== comment out ADO below ========
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines========
'====== comment out DAO above ========
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ===========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) -
Click to expand...
Len
(pstrDelim))
End If
Concatenate = strConcat
End Function

--
Duane Hookom
MS Access MVP

Rebecca wrote:

Greetings. I am using MS Office XP Access. I am a
newbie, so if you can help me, please tell me what to do
using very simple English and the procedures step-by-
step.

I have a table with three columns: In col. A are the
Bible verses, such as Gen 1:1, Gen 1:2, etc. In col. B
are the Hebrew words for each Bible verse, one word in
each row. In col. C are the English translations of the
words in col. B. There are no blank rows in the database
(that is, Gen 1:2 immediately follows Gen 1:1). It would
look something like this (the Hebrew font cannot be
displayed here, of course):

A B C
Gen 1:1 HHH in
Gen 1:1 HHHH beginning
Gen 1:1 HH God
Gen 1:1 HHHHH created

snip snip snip

Gen 1:2 HHHH and
Gen 1:2 HHHHHH the earth

etc., etc.

Is it possible to run a query or whatever so I will be
able to see in rows (perhaps in memo fields?) the entire
verses of Genesis (and the rest of the Bible books) as
follows:

Gen 1:1 in beginning God created the heavens and the earth.
Gen 1:2 and the earth etc., etc.

I would deeply appreciate any help you could give me in
trying to solve this problem because I don't think I will
ever be able to solve it on my own, though I have been
trying. But please keep in mind that I am an Access
newbie. Thank you.
.
Click to expand...
Click to expand...
.
Click to expand...
 
Sorry, I think you have not followed the comments in Duane's code and changed it
as appropriate. I've placed the modified code which should replace what you are
currently using.

You need some field that will specify the word order, if KeyID is a value that
is in ascending order (an autonumber field) and that matches the order the words
are supposed to be in then you could use it. Your query then would look
something like the following

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """ ORDER BY KeyID") AS Phrase
FROM BIBLE

Hope this works for you. If KeyId doesn't impose the correct word order, then
it is likely that you will need to add a field to your BIBLE table to set the
field Order and enter numbers from 1 up. You might as a matter of practice
enter the numbers as multiples of 10 for each combination of [A] and [C]. That
way if you should accidentally miss a word and need to insert it between the
first and second word (numbered 10 and 20), you can just assign the new second
word an order value of 15 and that would make it fall between the original first
and second (now third) words.

'------------- NEW CODE --------------------------------------

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Everything stops at the following:

rs As New ADODB.Recordset

How do I specify the word order? What kind of column is
this? My table does contain a "KeyID" column. Is this
what you mean?

My database is saved as .mdb

This is the SQL statement I posted:

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """") AS Phrase
FROM BIBLE

A = the column with the Bible verse names (Gen 1:1, Gen
1:2, etc.)
C = the column with the English translation of the Hebrew
words in column B
Click to expand...
 
You might as a matter of practice
enter the numbers as multiples of 10 for each combination of [A] and [C]. That
way if you should accidentally miss a word and need to insert it between the
first and second word (numbered 10 and 20), you can just assign the new second
word an order value of 15 and that would make it fall between the original first
and second (now third) words.
Click to expand...

<OT> But the order of words in the Torah and the Tanakh has been
established for a long time - it would not be halacha to allow the
order to be altered! </OT> <g>
 
Bless you, John.

Fantastic! It worked like a charm. How can I thank you?
Could I ask you for one more tweak, though? Would it be
possible to include code in the SQL to limit the query to
a certain book (since the Bible database is quite large,
and it takes a while to process)?

For example, let's say I want to only query Exo (Exodus;
every book has a three letter reference, such as Mat =
Matthew, Joh = John, etc.)? Would this be possible?

Second, the query results in Bible verses that are
sequenced (if that is the right word) as follows:

Gen 1:1
Gen 1:10
Gen 1:11
Gen 1:12
snip snip snip
Gen 1:19
Gen 1:2
Gen 1:20
etc.

Would it be possible to have the verses in the correct
Biblical order, such as:

Gen 1:1
Gen 1:2
Gen 1:3

etc.

Again, thanks John for your kind help.
-----Original Message-----
Sorry, I think you have not followed the comments in Duane's code and changed it
as appropriate. I've placed the modified code which should replace what you are
currently using.

You need some field that will specify the word order, if KeyID is a value that
is in ascending order (an autonumber field) and that matches the order the words
are supposed to be in then you could use it. Your query then would look
something like the following

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """ ORDER BY KeyID") AS Phrase
FROM BIBLE

Hope this works for you. If KeyId doesn't impose the correct word order, then
it is likely that you will need to add a field to your BIBLE table to set the
field Order and enter numbers from 1 up. You might as a matter of practice
enter the numbers as multiples of 10 for each combination of [A] and [C]. That
way if you should accidentally miss a word and need to insert it between the
first and second word (numbered 10 and 20), you can just assign the new second
word an order value of 15 and that would make it fall between the original first
and second (now third) words.

'------------- NEW CODE ---------------------------------- ----

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len (pstrDelim))
End If
Concatenate = strConcat
End Function
Everything stops at the following:

rs As New ADODB.Recordset

How do I specify the word order? What kind of column is
this? My table does contain a "KeyID" column. Is this
what you mean?

My database is saved as .mdb

This is the SQL statement I posted:

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """") AS Phrase
FROM BIBLE

A = the column with the Bible verse names (Gen 1:1, Gen
1:2, etc.)
C = the column with the English translation of the Hebrew
words in column B
Click to expand...
.
Click to expand...
 
For question one. You need to use Like criteria in the query.

LIKE "Exo*" would get everything starting with EXO. To make this slightly more
robust, you could use a parameter query, see below. This will ask you for the
three letter abbreviation and go from there.

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """ ORDER BY KeyID") AS Phrase
FROM BIBLE
WHERE [A] Like [Enter 3-letter book] & "*"

The Best solution the sort order problem would be to have three fields in place
of [A]. Book, Chapter, and verse. Then you would be able to sort (or select)
by any one of the three and then just combine as needed. If you can't add the
new columns, then try the following. (Probably slow and it relies on [A] always
being in the format Letter letter letter space number(s) colon number(s))

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """ ORDER BY KeyID") AS Phrase
FROM BIBLE
WHERE [A] Like [Enter 3-letter book] & "*"
ORDER BY Left([A],3),
Val(Mid([A],5)),
Val(Mid([A],Instr(1,[A],":")+1))

What the order by does is sort by segments of [A]
The first three letters
The numeric value of starting with the 5th character
The numeric value starting with the first character after the colon

The can run into problems if there is no colon in [A] or [A] is blank or ...

Your best overall solution would be to look at redesigning your BIBLE table so
you had the requisite columns and then using that.

BIBLE
Book
Chapter
Verse
EnglishWord
HebrewWord
WordPosition (and you might need two of these, one for English and the other
for Hebrew since I think that there would not necessarily be a one-to-one
correspondence on the words or the order of the words in a verse or sentence)

Anyway, hope all this helps.
Bless you, John.

Fantastic! It worked like a charm. How can I thank you?
Could I ask you for one more tweak, though? Would it be
possible to include code in the SQL to limit the query to
a certain book (since the Bible database is quite large,
and it takes a while to process)?

For example, let's say I want to only query Exo (Exodus;
every book has a three letter reference, such as Mat =
Matthew, Joh = John, etc.)? Would this be possible?

Second, the query results in Bible verses that are
sequenced (if that is the right word) as follows:

Gen 1:1
Gen 1:10
Gen 1:11
Gen 1:12
snip snip snip
Gen 1:19
Gen 1:2
Gen 1:20
etc.

Would it be possible to have the verses in the correct
Biblical order, such as:

Gen 1:1
Gen 1:2
Gen 1:3

etc.

Again, thanks John for your kind help.
-----Original Message-----
Sorry, I think you have not followed the comments in Duane's code and changed it
as appropriate. I've placed the modified code which should replace what you are
currently using.

You need some field that will specify the word order, if KeyID is a value that
is in ascending order (an autonumber field) and that matches the order the words
are supposed to be in then you could use it. Your query then would look
something like the following

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """ ORDER BY KeyID") AS Phrase
FROM BIBLE

Hope this works for you. If KeyId doesn't impose the correct word order, then
it is likely that you will need to add a field to your BIBLE table to set the
field Order and enter numbers from 1 up. You might as a matter of practice
enter the numbers as multiples of 10 for each combination of [A] and [C]. That
way if you should accidentally miss a word and need to insert it between the
first and second word (numbered 10 and 20), you can just assign the new second
word an order value of 15 and that would make it fall between the original first
and second (now third) words.

'------------- NEW CODE ---------------------------------- ----

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") As String
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len (pstrDelim))
End If
Concatenate = strConcat
End Function
Everything stops at the following:

rs As New ADODB.Recordset

How do I specify the word order? What kind of column is
this? My table does contain a "KeyID" column. Is this
what you mean?

My database is saved as .mdb

This is the SQL statement I posted:

SELECT DISTINCT [A],
Concatenate("SELECT [C] FROM BIBLE WHERE [A]=""" & [A]
& """") AS Phrase
FROM BIBLE

A = the column with the Bible verse names (Gen 1:1, Gen
1:2, etc.)
C = the column with the English translation of the Hebrew
words in column B
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top