Getting a subform to display the correct data

D

danny.eve

I'd really appreciate help with a subform problem I'm having. I know
it's a basic problem but I've looked at the Northwind and other sample
databases and haven't come close to finding a solution.

My database is for displaying words in a foreign language and links
between the words as follows:


SameMeaning - being words with the same meaning. An example for the
word "speed" would be "rapidity".
SameSound - being words with the same pronunciation but different
meaning. An example for the word "sun" would be "son".
OppositeMeaning - being words with the opposite meaning. An example
for the word "cold" would be "hot".
Usage - being a word that combines with the word to create a phrase.
An example for the word "lie" would be "to tell a".
Distinguish - being words that are important to distinguish from
another due to common mistakes. An example for the word "your" would
be "you're".
TransIntrans - being transative and intransative versions of the same
verb. An example for the word "to tell" would be "to be told".
Misc - being a word which is somehow linked but whose relationship
does not fit into any of the above catagories. An example for the word
"politician" would be "politics".


The table structure is (following help from the tabledesign
newsgroup):

tblWords:

WordID (Primary Key)
OriginalLanguageWord
PhoneticReading
English


tblRelateTypes:

RelateTypeID (Primary Key)
RelateDescription


tblWordRelates:

WordRelatesID (Primary Key)
OriginalWordID (tblWords WordID is foreign key)
RelateWordID (tblWords WordID is foreign key)
RelateTypeID (tblRelateTypes RelateTypeID is foreign key)


I want to have a subform on frmWords which displays the appropriate
WordRelates for each tblWords record.

The problem I have is that however I try to make the subform (using
the wizard or through a query) I can't find a way for the subform to
properly display the related word via it's OriginalLanguageWord field.
I can easily display the WordIDs of both the original and RelateWord,
but when I try to display the OriginalLanguageWord or PhonicReading
fields, only that of the OriginalWord is displayed. This is I believe
because tblWordRelates is linked by foreign key to tblWords twice. In
any query or wizard box if I select the "OriginalLanguageWord" field
it displays the OriginalLanguageWord of the record in tblWords.

An example:

tblWords:
WordID: 18
OriginalLanguageWord: houfu
PhoneticReading: houfu
English: wealth, abundance

WordID: 85
OriginalLanguageWord: yutaka
PhoneticReading: yutaka
English: abundance


tblRelateTypes:
RelateTypeID: 1
RelateDescription: Same meaning


tblWordRelates:
WordRelatesID: 1
OriginalWordID: 18
RelateWordID: 85
RelateTypeID: 1



When I create a subform for frmWords then navigate to record 18, the
subform displays the WordRelatesID (1) and RelateDescription (Same
meaning) perfectly, but the OriginalLanguageWord and PhoneticReading
fields are those of record 18 again, NOT of record 85, the related
record. With all the methods (queries, wizards, relationships window,
etc.) I can find no way to assign the OriginalLanguageWord and
PhoneticReading fields to display the related word, rather than the
original. Anyone have any tips? Your help will be much appreciated.
 
Top