Re: Bible Query

From: Rebecca (anonymous_at_discussions.microsoft.com)
Date: 06/02/04 

Date: Wed, 2 Jun 2004 08:33:19 -0700

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

Relevant Pages

  • Re: 3.840 was a riot earty this morning!
    ... newbie, the comments in code had me in stitches... ... I haven't dropped in on arts' little corner in quite awhile, ... John ...
    (rec.radio.amateur.policy)
  • Re: Slight O/T - In response to JOS leaving,,
    ... Despite John's comments that the ratio of newbie to professional has ... John is something that happens to most long time usenet posters at one ... idiot trolls has increased, thus leading to a reactive spiral. ...
    (alt.guitar.bass)
  • Re: unique values out of text area
    ... I've been able to do the following in other languages ... > but am a asp.net newbie and need some help. ... > dim chatters as arraylist ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Explanation of Amps
    ... so when you don't explicitly state that that's ... Moreover, John clearly defined his terms in order to, ostensibly, ... it's confusing to introduce a newbie to the concept of the ...
    (sci.electronics.basics)
  • Re: Totally [OT] Re: Opium
    ... >> a newbie is nonsense. ...
    (Debian-User)