I have 3 tables:

tblFaculty | tblDegrees | tblFacultyDegrees
FacultyID Auto# Key | Degrees Text Key | ID Auto# Key
Name Text | | FacultyID Number
Degrees Text | | Degrees Text


The are linked (using tblFacultyDegrees) as a many to many .

Sample Data
tblFaculty | tblDegrees | tblFacultyDegrees
1 Joe Smith | RN | 1 1 RN
2 Mary Brown | BS | 2 1 BS
3 Jane Jones | PhD | 3 1 PhD

I want to write a query that takes the names and places the Degrees
after them separated by commas to produce the following:

Joe Smith RN, BS, PhD
Mary Brown RN, BS
Jane Jones PhD, MS

Hope this makes sense - Thanks in advance

Ola E:

In DAO, Create a function returning a string. Open a recordset on the
tblFacultyDegrees, loop thru the records and append them to a string
which is then returned:

<uncompiled code>

Public Function MyDegs(InFac as Long) as String
Dim rs as Recordset
Dim wkVal as string

wkVal = ""
Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblFacultyDegrees
WHERE FacultyID = " & InFac, dbOpenSnapshot)
rs.MoveFirst
While Not rs.Eof
wkVal = wkVal & rs.Fields("Degrees") & ", "
rs.MoveNext
Wend
rs.Close: Set rs = Nothing
if (len(wkVal) > 0) then
MyDegs = Mid$(wkVal, 1, Len(wkVal) - 2): ' trim off last comma-space
End Function

</uncompiled code>

Then call the function from your query:

"SELECT tblFaculty [tblFaculty].[FacultyID], [tblFaculty].[Name],
MyDegs([tblFaculty].[[FacultyID]) AS Degrees FROM tblFaculty ORDER BY
[tblFaculty].[Name]"

Rich Harrington
Chi

enjayare3 (AT) hotmail (DOT) com (enjayare) wrote in message news:<e413548f.0407091747.5e1d91ab (AT) posting (DOT) google.com>...