Re: Bible Query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 06/02/04 

Date: Wed, 02 Jun 2004 10:06:47 -0400

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
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.