hi guys!,

I am quite familiar with datbase design and SQL, but I need your help to the problem I am facing right now!

I have a table in the database I am building now, in which I store words, their meaning and their etymology. Some of the words are derivatives of words in other records. For example, I may have one main word looking like:
word_id derivative_of word maening etymology
word_id - "analyze" "meaning of the word" "etymology"
word_id "analyze" "analyzable" "meaning of the word" (no etymology)

Whenever I want to get all the words that contain a word in their etymology, I run the proper query and everything goes OK. What I cannot do though, is to return the derivatives of the words along with the main words, when a word in the etymology exists. The main words have etymology only; their derivatives do not, as they are assumed to have the same etymology as their parent!
Do you think of a good design so that I get the word AND its derivtive(s) in one query?
One thought is to run the simple query, then programmatically ask for each derivative according to the returned parent word and then merge the 2 queries.
Another approach would be to store the derivatives in another table and return all the main words as well as the linked derivatives from the other. But this approach would not allow me to query one table when searching for parts in the word field.
Do you have any thoughts of a better design?
Thanks in advance,

Manos

Post the DDL for your tables.

[QUOTE=blindman]Post the DDL for your tables.[/
The database is in MS Access. The definition of fields (if I am not mistakn this is what you are asking me for) is the following:


WordId (autonumber)
Derivative (memo)
MainWord (memo)
Explanation (memo)
Etymology (memo)


Is this what you asked me to post?

Thanks again,

Manos

Well, the first thing that knocks me back in my seat is that you are using memo for all your datatypes.
Not only is this overkill, and a performance crusher, but the solution to your problem is going to rely on joining the table to itself on the Derivative and MainWord columns.
I wouldn't even store MainWord in your table. I'd store the WordID of the MainWord.... MainWordID.

You are right, but then again I did not want to take any chance and trim any data fields. Are you suggesting that in the derivative field I should save its parent's wordID?
If so, have you got any suggestion as to how to retrieve the derivatives along with the main word?

Thanks again,

Manos

Yes.
A join.

thanks alot for your quick replies. You suggestion did work.

For reference to other people, I write the methodology I followed.

Derivatives have now the parent word's ID and I build a typical query which would return all the words according to the criteria given in the query.

In order to solve the problem announced here, would be solved by inserting another table and joining it in the query build.

Thanks again,


Manos

Respectfully....no. You are not. But you are on your way.
Good luck, and check back with us if you need help with anything else.