query that merges records

[ram_son]

I am working with a data base that has two tables for words and synonyms. I have managed a query that goes like this:

SELECT word, synonym
FROM pat_words, pat_synonyms
WHERE (pat_words.word_id=pat_synonyms.word_id);

An sample of the result I get is:

word synonym

abandonment desertion
abandonment leaving
abase belittle
abase degrade

The result I'd like to see is :

word synonym

abandonment desertion/ leaving
abase belittle/ degrade



I imagine that, for an expert, it is something quite basic and simple; but for someone like me (very new to Access) it constitutes an effective of headache.

I would appreciate any help.

Ram_son

[RuralGuy]

Maybe you will find this link helpful: http://allenbrowne.com/func-concat.html

[ram_son]

I got the module to compile Ok but when I refer to it in the SELECT statement I get "undefined function" error.

[ram_son]

I followed the instruction in the article from the link got the module to compile ok but when I refer to it in the SELECT statement I get "undefined function" error. Why am I getting this error.

[RuralGuy]

It sounds like you named the module the same as the Function; don't! Name it basFunctions or something.

[ram_son]

When I run the the statement now I get an error about ambiguous name in the ConcatRelated part of the statement. I tried changing the id names to no avail. This is the last one I tried. May be you can see what's wrong with it.

SELECT word, ConcatRelated("synonym", "pat_Synonyms", "[pat_words.word_id] = " & [pat_synonyms.word_id])
FROM pat_words;

Thanks

[RuralGuy]

Why are you scoping those fields? I would think it should be:
SELECT word, ConcatRelated("synonym", "pat_Synonyms", "[word_id] = " & [word_id])
FROM pat_words;