Help with a query or a report

[Serg Matvienko]

Hi everybody,



In short, there are 3 tables - A, B, C for example. The table A has 2
fields - PersonCode, PersonName. The table B has PersoneCode, LanguageCode
and the table C - LanguageCode, LanguageName. The table A is a master table,
the table B is a detail table because every person can speak several
languages. I need a report or a query where every person has only 1 row,
and this row contains only 2 columns - a name of person and a list of
language names separated with commas. Please help to make the query .



Thank in advance,



Serg

 

[Duane Hookom]

There is a generic concatenate function with samples at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. Let us
know if you have any questions about how to use it.

 

[Serg]

Hi Duane,



Thank you very much for your help and the nice link you sent. I am after
trying to use your concatenate function. At first, I joined my B and C
tables in one and now I have a query D with 2 fields - PersonCode and
LanguageName. Now i try to make query joining the table A and a query D. I
created a new query table consising from 2 columns - PersonCode and a
calculated field ListOfLang containing this concatenated LanguageName. For
this I created field A.PersonCode and a calculated field ListOfLang as
this:

ListOfLang: Concatenate( [PersonCode],"Select Name, From D Where
PersonCode = " & [PersonID]). And I added one more parameter in your
concatenate function (in the beginning) - "PersonID As long". But
unfortunately it doesn't work. I would be extremely grateful if you could
help me again.



Thanks

Serg

 

[Duane Hookom]

Try:
ListOfLang: Concatenate( "Select [Name] From D Where PersonCode = " &
[PersonID])
THis assumes PersonCode and PersonID are the join fields and that the fields
are numeric.

 

[Serg]

Hi Duane,

Thank you very much for your help. I appreciate it greatly. I tried it this
way and it works! But i tried to make your generic function even more
generic. I was wondering how i could have a string of names for any
PersonCode that i could send as parameter of your function. This is why I
added the extra parameter to your nice function. But i still don't now how i
can do it,

Thank again,

Serg

 

[Duane Hookom]

In a query, this will send each PersonID in the function and return a
different result for each.