using intermediate tables

[Papa Jonah]

I have a subform that I use to relate Bible verses to another form. In the
subform I have a field for book, another for chapter, and another for verse.
These get stored in a separate table from the info in the parent form. My
trouble is that often one verse is listed several times - which is fine I
guess. What I want to do is add the text for the verses and I don't want
that to be repeated. I am trying to figure out how to set up another table
to store the text of the verses in a memo field along with the verse
references.
What I am thinking is that the subform's underlying table might be an
intermediate table that is used to link the parent table to the new table
with the text. I am not sure how to do this.

 

[Geof Wyght]

I'm having trouble picturing this because you haven't told
us what's in the underlying table of the parent form.
Geof Wyght.

 

[Papa Jonah]

the underlying table of the parent form simply has things such as entry id,
date, location, person name, notes.

 

[Geof Wyght]

I wonder why the verse is repeated? In any event, you
could have a table with bookid, chapterid, verseid, The
you could have a verse table with verseid (autonumber) and
verse (memo). Link the two table by verseid in Tools,
Relationships. Does that help?
Geof Wyght.

 

[Papa Jonah]

no.
The reason is because the way I currently have it, which may be wrong, the
verse is recorded independent of the book and chapter fields. The reason a
verse may be repeated is because it may be referenced in multiple entries in
the parent form.
I have the book, chapter, and verse captured in different fields to ensure
consistent entry by different users, and so that the books can be selected in
a combobox.
Does that make sense?

 

[Geof Wyght]

When I suggest that you create a verse table consisting
of an autonumber verseid, and a memo verse field, you are
making verse independent of anything else. Doesn't this
satisfy one of your original objectives?
Geof Wyght.

 

[John Vinson]

I have a subform that I use to relate Bible verses to another form. In the
subform I have a field for book, another for chapter, and another for verse.
These get stored in a separate table from the info in the parent form. My
trouble is that often one verse is listed several times - which is fine I
guess. What I want to do is add the text for the verses and I don't want
that to be repeated. I am trying to figure out how to set up another table
to store the text of the verses in a memo field along with the verse
references.
What I am thinking is that the subform's underlying table might be an
intermediate table that is used to link the parent table to the new table
with the text. I am not sure how to do this.

I think your intermediate table might have the following fields:

CitationID Autonumber (Primary Key)
ParentTableID << link to whatever your main form's table is
Book
Chapter
Verse

The three Bible fields could be filled using combo boxes - you can
even have the Chapter combo show only the proper number of chapters
for each book, and the Verse combo for each chapter.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps