Relating a table to itself

[Drakemar]

Hello, I'm developing an Access 2007 Database and I have the following
roadblock. I have a table that needs to relation to itself, to
multiple rows. Here is what I have:

Table1 - Words
ID autonumber
Word text

Table2 - WordTranslations
ID autonumber
WordsID number (fk of ID in words table)
SecondaryLanguageWordID number (fk of ID in words table)

Basically, the "Words" table consists of words from a few languages
and the "WordTranslations" table consists entries that are equivalent
but in different languages. IE. Table 1:: 1 cat, 2 dog, 3 chat, 4
lizard. So, Table 2 might look like:: 1 1 3

How would I do this in Microsoft Access?

Thanks!

 

[John W. Vinson]

Hello, I'm developing an Access 2007 Database and I have the following
roadblock. I have a table that needs to relation to itself, to
multiple rows. Here is what I have:

Table1 - Words
ID autonumber
Word text

Table2 - WordTranslations
ID autonumber
WordsID number (fk of ID in words table)
SecondaryLanguageWordID number (fk of ID in words table)

Basically, the "Words" table consists of words from a few languages
and the "WordTranslations" table consists entries that are equivalent
but in different languages. IE. Table 1:: 1 cat, 2 dog, 3 chat, 4
lizard. So, Table 2 might look like:: 1 1 3

How would I do this in Microsoft Access?

Thanks!

You already did. <g>

Just join Table1.ID to Table2.WordsID in the relationship window. Then add
Table1 to the grid *again* - Access will alias it as Table11 (appending a 1 to
the name). Join this instance of ID to SecondaryLanguageWordID.

John W. Vinson [MVP]

 

[Drakemar]

You already did. <g>

Just join Table1.ID to Table2.WordsID in the relationship window. Then add
Table1 to the grid *again* - Access will alias it as Table11 (appending a 1 to
the name). Join this instance of ID to SecondaryLanguageWordID.

John W. Vinson [MVP]

Thanks for the reply. It's good to know I was doing the right thing.
I got discouraged when I used the Database Configuration Wizard in
Visual Studio 2008 (C#) and it didn't have the alias table there. So
it looks like one of the relations is missing. This may be the wrong
forum for this, but would you happen to know if this is working as
intended or do I need to edit something in VS2008?

 

[Drakemar]

Thanks for the reply. It's good to know I was doing the right thing.
I got discouraged when I used the Database Configuration Wizard in
Visual Studio 2008 (C#) and it didn't have the alias table there. So
it looks like one of the relations is missing. This may be the wrong
forum for this, but would you happen to know if this is working as
intended or do I need to edit something in VS2008?

Ah, I knew I posted that too fast. Closer inspection of Visual Studio
2008 revealed that the two relations were visually represented on top
of each other. It only looked like one was present, but they both
were.