the idea is using recordset.
I 'm not testing it yet. May be you need correction in sintax
put it in your module.
Function GetProject (byval EmpID) as string
set rsProj = currentdb.openrecordset ("Select PROJECT.* FROM Project Where
[EmpID]=" & EmpID)
with rsProj
if not .eof and not .bof then
for i=1 to .recordcount
if i <> .recordcount then
project=project & !Project & ","
else
project=!Project
endif
next
endif
end with
end function
simply using it in you query
GetProject(EmpID)
----- Original Message -----
From: Roseller Ocampo
To: [email protected]
Sent: Wednesday, November 14, 2007 09:22
Subject: [ms_access] Combine/Concatenate Multiple Records
Thank you for reading this question.
I have two tables that looks like these:
EMPLOYEES
EmpID Surname
100 Smith
200 Johnson
PROJECTS
EmpID Project
100 Road
100 Bldg
100 Hwy
200 Bridge
200 Tower
Question:
I would like to have a resultant table that will look like this:
EmpID Project
100 Road, Bldg, Hwy
200 Bridge, Tower
How will I do this please? Is this can be done via query or a
program module? Please point me to ideas or examples.
[Non-text portions of this message have been removed]
