Concatenate with intervening table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dpj - 02 Aug 2006 03:31 GMT

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.

Reply to this Message

dpj - 02 Aug 2006 03:35 GMT

Sorry, Tbl_AB_Relate has: DoctroID, BoardID (foreign keys for Tbl_A and Tbl_B)

Regards.

> I have 3 tables, Tbl_A is many-to-many with Tbl_B. The pertinent columns are
> as follows:
[quoted text clipped - 13 lines]
>
> Thanks for any help conceptually and with syntax.

Reply to this Message

Duane Hookom - 02 Aug 2006 04:52 GMT

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;

Signature

Duane Hookom
MS Access MVP

>I have 3 tables, Tbl_A is many-to-many with Tbl_B. The pertinent columns
>are
[quoted text clipped - 15 lines]
>
> Thanks for any help conceptually and with syntax.

Reply to this Message

dpj - 03 Aug 2006 19:31 GMT

Duane, thanks. That worked perfectly.

> Sure, something like:
>
[quoted text clipped - 22 lines]
> >
> > Thanks for any help conceptually and with syntax.

Reply to this Message

Marshall Barton - 02 Aug 2006 04:53 GMT

>I have 3 tables, Tbl_A is many-to-many with Tbl_B. The pertinent columns are
>as follows:
[quoted text clipped - 11 lines]
>Column2: BoardName1, BoardName2, ... (where this is a comma-delimited
>concatenation of all the BoardNames for each Doctor)

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message