Relating Fields to Each Other

[lmcc007]

Hello everyone,

I'm trying to create a simple form for my sister's classroom. That is, if she types "set," the form will display "sett" as the homonym.

I created a main table with the following fields:

1. ID
2. Word (a combo box from the Word table)
3. Pronunciation
4. Homonym (a combox from the Word table)
5. Meaning.

A second table with one field:

1. Word
A. Which is the primary key, Indexed,
no Duplicates).

I'm lost on how to properly set this up like a dictionary type form and how to relate the word to the appropriate homonym.

Help! Please!!!

 

[Pat Hartman]

At its simplest, this is the structure:

One table to define all the words.

1. Word (PK)
2. Pronunciation
3. Meaning

A second table that relates a word to a homonym:

1. Word (a combo box from the Word table) (PK field1)
2. Homonym (a combox from the Word table) (PK field2)

If you wanted to define other types of relationships, you would add an additional field to the second table:

3. RelationshipType (homonym, synonym, antonym, etc.)

As to filling table 2, create a form with a subform. The main form will show data from the Words table and the subform will show data from the relationships table and contain just field 2 (or 2 and 3 if you go that route).

 

[lmcc007]

Thanks Pat, but I am lost. Can't get it to work.

Don't understand this part:

If you wanted to define other types of relationships, you would add an additional field to the second table:

3. RelationshipType (homonym, synonym, antonym, etc.)



I tried creating the form with subform and it's not working.

As to filling table 2, create a form with a subform. The main form will show data from the Words table and the subform will show data from the relationships table and contain just field 2 (or 2 and 3 if you go that route).

Let me walk away and try it again. Thanks!

 

[lmcc007]

Sub-Form or Table question.

Hi Pat,

I finally got it work.

How do I alleviate having to rekey the definition in for the Homophones (words)? Isn’t that duplicating?

This is what I have:

1. TBL-Words (list of words). The words are the PK.

2. TBL-Defs (Word, Pron., Defs). Word is the PK.

3. TBL-Homonyms (Word, Homophone, Defs). Word and Homophone is the PKs.

4.
How do I get the definitions for the Homophones to display correctly in the subform without having to retype them?

Thanks!

 

[Pat Hartman]

1 and 2 should be combined since they would have a 1-1 relationship.

To show definitions for both words, you need to create a query that joins the relation table to the word table twice.
1. Create a new query.
2. Add the relation table (the one with the word/homophone pair)
3. Add the word table (the one with the definitions) two times to the QBE grid.
4. Draw a join line from the "Word" in the relation table to the "Word" of the first word table.
5. Draw a join line from the "homophone" in the relation table to the "Word" of the second word table.
6. When you drag the definition for each word you will need to alias it in order to avoid confusion so in the Field cell for the definition for "Word" type:
WordDef:Word
for the homophone, type:
HomophoneDef:Word

 

[lmcc007]

Sub-Form or Table question.

Thanks Pat! Is there an easier way of doing this? Basically, all I want is:

1. A "Table" to include four fields, which are: Word, Pronunciation, Meaning, and Homonyms.

2. A "Form" and "Subform," that is: A) Form will have: Word, Pronuncation, Meaning; and B) Subform will list: Homonyms and Meaning.

I think I have done something wrong because I am having to go to Table 2 (word/homonym) and define each one and then put the word, punctuation, and definition in Table 1.

Is there a better way?

 

[boblarson]

Is there a better way?

If you follow what Pat (former Access MVP) has said, that is the better way.