Concatenate fields from multiple tables

[BillA]

I am attempting to use Duane Hookom’s concatenate function without any
success. I’ve read many threads, relating to Duane’s function, yet none seem
to fit my situation.

I am working with multiple tables, with a M:M ‘Joining’ table in the middle

Here is an abridged description of my tables and data for illustrative
purposes:

tbl_Investigator
InvestigatorID 1 2 3
FirstName Bob Tim Ted
LastName Smith Johnson May

tbl_Join
JoinID 1 2 3
InvestigatorID 1 2 3
ProjectID 3 3 3
InvestigatorRoleID 1 3 3

tbl_Project
ProjectID 1 2 3
ProjectTitle Project1 Project2 Project3

lup_InvestigatorRole
InvestigatorRoleID 1 2 3
InvestigatorRole Lead Co-Lead Assistant

I would like to know if Duane’s function can concatenate such a scenario
where I have multiple tables and a M:M relationship connecting them all.

Ideally, I would like to create a query based upon a PROJECT that will
concatenate FIRSTNAME, LAST NAME and (INVESTIGATOR ROLE) in parentheses. I
want the concatenated fields in a single field with a comma separating each
entry (no carriage returns or tabs).

This data will be merged to a Word document and various reports within my
Access db.

I’ve tried for 3 days to make this work and I’m starting to repeat failed
attempts.

Any advice would be greatly appreciated.
Thank you in advance.
Bill

 

[Gary Walter]

Hi Bill,

It sure look like you are making it harder than needs to be...

Add the 4 tables in Query Designer.

Give each table an Alias
(right-mouse click on table,choose Properties),
say

tbl_Investigator I
tbl_Join J
tbl_Project P
lup_InvestigatorRole IR

then join them...

Then, in a Field row of grid, type

Field: Invsgtr: I.FirstName & ", " & I.LastName & " (" &
IR.InvestigatorRole & ")"
Table:
Sort:
Show: <checked>
Criteria:
Or:

Double-click on other needed fields in tables
to add to grid.

The "problem" is that you designed your
tables too well and function was not needed.

good luck,

gary

 

[BillA]

Gary,
Thank you for your response.

Although your suggestion brought me half-way to my goal. I used your
recommendation as a pre-query and accomplished my goal of concatenating the
person's name and (role) into a single field by using the concatenation
function.

Thanks again Gary for giving me the needed push.
Bill

For anyone who is interested, this is the SQL that worked:
SELECT DISTINCT tbl_Project.ProjectNumber, tbl_Project.StudyTitle,
Concatenate("SELECT Name FROM preqry_ConcatInvestigator WHERE
preqry_ConcatInvestigator.ProjectID = " & tbl_Project.ProjectID) AS Name
FROM (tbl_Project INNER JOIN preqry_ConcatInvestigator ON
tbl_Project.ProjectID = preqry_ConcatInvestigator.ProjectID) INNER JOIN
lk_Join AS [Join] ON tbl_Project.ProjectID = Join.ProjectID;

 

[stevanturk]

Check www.pathtoolong.com it will delete anything.






ref: http://www.codeverge.net/item.aspx?item=52356