We help IT Professionals succeed at work.
Get Started

How to implement a running total in Access 2003

Badotz asked
Last Modified: 2012-05-11
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 ;-)
Watch Question
This problem has been solved!
Unlock 1 Answer and 18 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE