How to implement a running total in Access 2003

Badotz
Badotz used Ask the Experts™
on
To solve a puzzle, I need to be able to index letters in books and/or documents. For example, I might need to know the 51st letter of the 7th chapter.

The first book I chose is the Bible. Not for any beliefs I may or may not espouse, but simply because it is in widespread use.

I created an Access 2003 DB consisting of five tables. Each table has a column named id, an AutoNumber, as the PK:
/*

book            chapter         verse           word_link       word
====            =======         =====           =========       ====
id ------+      id -------+     id --------+    id         +--- id - PK
chapter  +----> book_id   +---> chapter_id +--> verse_id   |    word
book            chapter_num     verse_num       word_id <--+    length

I can reproduce a list of words and their lengths, given a book ("Ezra"), a chapter number (2) and a verse number (3):

PARAMETERS [@BOOK_NAME] Text ( 255 ), [@CHAPTER] Long, [@VERSE] Long;
SELECT verse.id AS verse_id, chapter.chapter, verse.verse, word.word, word.length
FROM word INNER JOIN (((book INNER JOIN chapter ON book.id=chapter.book_id) INNER JOIN verse 
ON chapter.id=verse.chapter_id) INNER JOIN word_link ON verse.id=word_link.verse_id) 
ON word.id=word_link.word_id
WHERE (book.book=[@BOOK_NAME] Or book.called=[@BOOK_NAME]) 
And chapter.chapter=[@CHAPTER] 
And verse.verse=[@VERSE];

*/

Open in new window

My problem is this: given a book id (4 - Numbers), a chapter number (2) and a verse number (3), produce the 51st letter, or at least the word in which the 51st letter resides, and a total of word lengths up to that word.

The solution is produce a running total of word_length up to the required size, exceeding said size by no more than one word length.

And that is where I'm stumped. I have tried various examples using self-joins and Sum() of word_length, but to no avail.

The DB is attached (zipped), should anyone want to try their hand at this.

This is by no means urgent or career-ending, so please do not feel there is any hurry in responding. Should you respond, however, I'd appreciate a solution that has been tested, not something that "oughtta work". I am more than capable of finding solutions like that myself ;-)
books.zip
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I'm stumped why tblBook has a foreign key Chapter.  In my opinion, it should only contain fields ID and BookName.  The rest of the tables all require a foreign key pointing back to the source, but not the highest table in the hierarchy which is tblBook.  Comments?

Commented:
Can there ever be a case where a verse contains less than 51 characters? (Don't have my Bible handy ;-) )
Top Expert 2007

Author

Commented:
The "chapter" column in table "book" should be "title", sorry.

Attached is the actual relationship between tables. DB book table relationships
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2007

Author

Commented:
51 is an arbitrary number, and I doubt whether any chapter is that short.

Remember I am after the nth letter, be it verse, chapter or book.

Commented:
Does punctuation count as a letter?
Top Expert 2007

Author

Commented:
No. Table Word contains only letters, both upper- and lower-case. Case does not matter, except for crudely reconstructiong the text.
Top Expert 2007

Author

Commented:
Spaces do not count, either, only the letters of the words.

Commented:
Numbers?  For this: "The 10 Commandments."  2 or three words
Top Expert 2007

Author

Commented:
The term "word" may include numbers, but I have not verified this.

The only thing you can know for certain is that if it is in the word table, then it has a length and should be counted.

Commented:
With this table Details:

pk      word
1      now
2      is
3      ever
4      the
5      time
6      for
7      all
8      terrific
9      homosapiens
10      to
11      come
12      to
13      afterwards
14      and
15      sprint

I run this query:

PARAMETERS EnterLength Integer;
SELECT TOP 1 a.pk, EnterLength as DesiredLength,  a.Word,
(SELECT Sum(len(b.Word)) FROM Details b WHERE b.pk<=a.pk) AS RunSumWithWord
FROM Details AS a
WHERE (SELECT Sum(len(b.Word)) FROM Details b WHERE b.pk<=a.pk) > EnterLength
ORDER BY a.pk;

And got this result:

pk      DesiredLength      Word      RunSumWithWord
8      27                      terrific      30


I noticed your zipped db size over 8 Meg.  I shudder to think how big the unzipped db is.  I don't think I need to see the whole Bible to get where you are coming from.  I imagine your query produces a record set similar to my table Details.  My table does not have Book and Chapter details, and your query does not have the word.id.  That field would replace my pk field.  You could run this as a query of a query, or make the additions to your query published in the question.  You wanted to see how to do a running sum - there it is.  
Top Expert 2007

Author

Commented:
Pardon my ignorance, but could you please use my table names in your example?

Since the link from word to book is via the word_link table, where does that come into play?
Top Expert 2007

Author

Commented:
Attached is the DB with only the first book.
books.zip
Top Expert 2007

Author

Commented:
I should mention that words are not duplicated; the word_link table connects words to a verse. The exception to this is capitalized and non-capitalized words are considered to be different words.

Your example table seems to be a breakdown of a complete phrase. In the DB, however, the list of words would be:

1      now
2      is
3      ever
4      the
5      time
6      for
7      all
8      terrific
9      homosapiens
10      to
11      come
12      afterwards
13      and
14      sprint

The word "to" would not be listed twice.

Commented:
Hi:  I'm working on it.  The only way I can solve this is through the use of a form and VBA.  I hope that is OK.  I should have something later today.

Ray
Top Expert 2007

Author

Commented:
hmmm...I can do it through VBA, thanks.

If that is the only way it can be done, then so be it. A running total via a query just seemed more elegant.

I'll just close and award points, no need for you to waste any more time on this.

Unless you wish to continue? Let me know.
Commented:
I also do it through VBA, using a form to present the results.  This is because one cannot create an ascending index or ID consistently across all books, chapters, and verses (not necessarily consecutive, but in all cases ascending), one cannot get a running total via Jet - you have to call a VBA function.  You have to open the recordset, and loop through each word summing the length to a collector until the collector exceeds the provided value using a Do ..  Loop Until function - ergo the form.  Interesting question - but as far a I can determine, a solution using just Access without a UDF is not available.
Top Expert 2007

Author

Commented:
Thanks for your time and effort. The VBA way is OK, just not what I had hoped for.

But no worries, a solution is a solution in my little world ;-)

Commented:
Thanks, glad to help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial