B
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
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