Concatenate one-to-many into one row

[Suzanne P.]

I am trying to take all the data from the many side of a relationship and put
it into one row so that I can export into excel without repeating all the
other informaiton multiple times for each time the "many" info appears. I've
found many posts on this, but I am just not able to get it to work. I found
Duane Hookum's link
(http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.)
which shows the below formula in the criteria section of his query:
FirstNames: Concatenate("SELECT FirstName FROM tblFamMem WHERE FamID =" &
[FamID])
I modified it many different ways, but cannot make it work. One way I tried
was
AuditScore: Concatenate("SELECT adtscr FROM tbladtscore WHERE adtID =" &
[ID]).
"AdtScr" is the field I am trying concatenate and it comes from
"tbladtscore". "adtID" is the field in "tbladtscore" that relates to "ID" in
the main table. I also tried adding the full text for each field, ie
tbladtscore.adtscr

What am I doing wrong?

 

[Suzanne P.]

Nevermind, I figured it out. I didn't realize that I had to use the module
also. Sorry-I'm new to the more advanced options in Access.