troubleshooting Question

How to implement a running total in Access 2003

Avatar of Badotz
BadotzFlag for United States of America asked on
Microsoft Access
18 Comments1 Solution329 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
GRayL

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 18 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros