Concatenate with intervening table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables, Tbl_A is many-to-many with Tbl_B. The pertinent columns are
as follows:

Tbl_A : DoctorID, DoctorName
Tbl_AB_Relate: ContactID, BoardID (foreign keys for Tbl_A and Tbl_B)
Tbl_B: BoardID, BoardName

All IDs are numerical.

I am familiar with Duane Hookum's Concatentate function. Is it possible to
use it in a query to achieve output like:

Column1:DoctorName
Column2: BoardName1, BoardName2, ... (where this is a comma-delimited
concatenation of all the BoardNames for each Doctor)

Thanks for any help conceptually and with syntax.
 
Sorry, Tbl_AB_Relate has: DoctroID, BoardID (foreign keys for Tbl_A and Tbl_B)

Regards.
 
Sure, something like:

SELECT DoctorName, Concatenate("SELECT BoardName FROM tbl_B INNER JOIN
Tbl_AB_Relate on tbl_B.BoardID = Tbl_AB_Relate.BoardID WHERE DoctorID =" &
DoctorID) as Boards
FROM tbl_A;
 
dpj said:
I have 3 tables, Tbl_A is many-to-many with Tbl_B. The pertinent columns are
as follows:

Tbl_A : DoctorID, DoctorName
Tbl_AB_Relate: ContactID, BoardID (foreign keys for Tbl_A and Tbl_B)
Tbl_B: BoardID, BoardName

All IDs are numerical.

I am familiar with Duane Hookum's Concatentate function. Is it possible to
use it in a query to achieve output like:

Column1:DoctorName
Column2: BoardName1, BoardName2, ... (where this is a comma-delimited
concatenation of all the BoardNames for each Doctor)
Click to expand...


Easy! Just join Tbl_AB_Relate (on DoctorID) in your query
and use the BoardID in the Concatenate function.

Make sure you make a conscious decision whether you want an
inner or outer join.
 
Duane, thanks. That worked perfectly.

Duane Hookom said:
Sure, something like:

SELECT DoctorName, Concatenate("SELECT BoardName FROM tbl_B INNER JOIN
Tbl_AB_Relate on tbl_B.BoardID = Tbl_AB_Relate.BoardID WHERE DoctorID =" &
DoctorID) as Boards
FROM tbl_A;
Click to expand...
 
Back
Top