Concatenate fields from multiple tables

  • Thread starter Thread starter BillA
  • Start date Start date
B

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
 
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. 8-)

good luck,

gary
 
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;
 
Back
Top