Concatenate fields from multiple tables - repost

[BillA]

// reposted: to straighten table data //
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

 

[Duane Hookom]

Join all the tables into a single query excluding the tbl_Project. Then
create a query based on tbl_Project and use an expression like:
Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole &
')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])

 

[BillA]

Thank you Duane. Your suggestion worked brilliantly.

Thank you for your time and the concatenation function.

Bill

Join all the tables into a single query excluding the tbl_Project. Then
create a query based on tbl_Project and use an expression like:
Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole &
')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])

--
Duane Hookom
Microsoft Access MVP

// reposted: to straighten table data //
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

 

[Duane Hookom]

Glad to hear it is working for you.

--
Duane Hookom
Microsoft Access MVP

Thank you Duane. Your suggestion worked brilliantly.

Thank you for your time and the concatenation function.

Bill

Join all the tables into a single query excluding the tbl_Project. Then
create a query based on tbl_Project and use an expression like:
Concatenate("SELECT FirstName & ' ' & LastName & ' (' & InvestigatorRole &
')' FROM qselYourQuery WHERE [ProjectID]=" & [ProjectID])

--
Duane Hookom
Microsoft Access MVP

// reposted: to straighten table data //
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