Working with many-to-many relationships

I wish to create a report that has on each line a book title followed by a
list of the authors.

The titles are in a table (TITLES), the authors are in a table (AUTHORS),
and there is a "bridge" table between them that contains records with a
titleID and an authorID (TA_BRIDGE). Since a book may have several authors,
and each author may participate in several books this organization is
necessary.

How can I create a report that displays each title from TITLES followed by a
list of the authors associated with it?

Base the report on the TITLES table, and concatenate the authors into a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed by a
list of the authors.

The titles are in a table (TITLES), the authors are in a table (AUTHORS),
and there is a "bridge" table between them that contains records with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization is
necessary.

How can I create a report that displays each title from TITLES followed by
a
list of the authors associated with it?

Yikes! I assume that your answer means that this can only be done using code.

Thanks for your reponse Allen; It is very cool that I can get an answer so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common that it
would be available more easily, so I am curious why it is not. Can you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors into a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed by a
list of the authors.

The titles are in a table (TITLES), the authors are in a table (AUTHORS),
and there is a "bridge" table between them that contains records with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization is
necessary.

How can I create a report that displays each title from TITLES followed by
a
list of the authors associated with it?

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do in
VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done using
code.

Thanks for your reponse Allen; It is very cool that I can get an answer so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common that it
would be available more easily, so I am curious why it is not. Can you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors into a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization is
necessary.

How can I create a report that displays each title from TITLES followed
by
a
list of the authors associated with it?

OK, I am ready to attempt to implement your suggested solution, but I have no
idea where to put the code (not to mention how to invoke it. I was thinking
it would be best to get it working in a query, and then base the report on
the query.

The article you cited says the code can be used in a query, but gives no
clue how to embed the code, so I am hoping you will enlighten me about this.

"Allen Browne" wrote:

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do in
VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done using
code.

Thanks for your reponse Allen; It is very cool that I can get an answer so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common that it
would be available more easily, so I am curious why it is not. Can you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors into a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization is
necessary.

How can I create a report that displays each title from TITLES followed
by
a
list of the authors associated with it?

1. Click the Modules tab of the Database window.

2. Click New. Access opens a new code window.

3. Paste in the code from the article.

4. To ensure Access understands it, choose Compile on the Debug menu.
If you get an Unknown Type error on the first line, choose References on the
Tools menu, and check the box beside:
Microsoft DAO 3.6 Library

5. Save the module with a name such as Module1.

You can now use the function in a query, just like the built-in functions
such as Trim() or Left(). Follow the example so it reads your table name,
primary key field, field to concatenate, data type, etc. For the data type,
use "String" (include the quotes) for a text field. For a Number field, use
"Long", "Integer", or "Double", depending on the data type of your field
(first property in the lower pane, when you open the table in design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
OK, I am ready to attempt to implement your suggested solution, but I have
no
idea where to put the code (not to mention how to invoke it. I was
thinking
it would be best to get it working in a query, and then base the report on
the query.

The article you cited says the code can be used in a query, but gives no
clue how to embed the code, so I am hoping you will enlighten me about
this.

"Allen Browne" wrote:

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do in
VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done
using
code.

Thanks for your reponse Allen; It is very cool that I can get an answer
so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common that
it
would be available more easily, so I am curious why it is not. Can you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors into
a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed
by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records
with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization
is
necessary.

How can I create a report that displays each title from TITLES
followed
by
a
list of the authors associated with it?

Thanks Allen. However, I think that this does not accomplish the task I was
asking about originally (or I am missing the understanding that it does do
it!).

In studying the text in the fConcatChild module and looking at the Northwind
database, it looks like it concatenates data in the Order Details table.

However, the Order Details table serves as a "bridge" between the Orders
table and the Products table. My need takes data from the Products table
that goes with each Order; the Order Details tables provides the linkage
between these tables.

In the context of this example, what I require is to create for each record
in the Orders table a string that concatenates the ProductName data from the
Products table for all the ProductIDs called out in the Order Details table.

If I am really being dense here please excuse me, but I don't see that the
fConcatChild() function every references the Products table.

"Allen Browne" wrote:

1. Click the Modules tab of the Database window.

2. Click New. Access opens a new code window.

3. Paste in the code from the article.

4. To ensure Access understands it, choose Compile on the Debug menu.
If you get an Unknown Type error on the first line, choose References on the
Tools menu, and check the box beside:
Microsoft DAO 3.6 Library

5. Save the module with a name such as Module1.

You can now use the function in a query, just like the built-in functions
such as Trim() or Left(). Follow the example so it reads your table name,
primary key field, field to concatenate, data type, etc. For the data type,
use "String" (include the quotes) for a text field. For a Number field, use
"Long", "Integer", or "Double", depending on the data type of your field
(first property in the lower pane, when you open the table in design view.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
OK, I am ready to attempt to implement your suggested solution, but I have
no
idea where to put the code (not to mention how to invoke it. I was
thinking
it would be best to get it working in a query, and then base the report on
the query.

The article you cited says the code can be used in a query, but gives no
clue how to embed the code, so I am hoping you will enlighten me about
this.

"Allen Browne" wrote:

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do in
VBA.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done
using
code.

Thanks for your reponse Allen; It is very cool that I can get an answer
so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common that
it
would be available more easily, so I am curious why it is not. Can you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors into
a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title followed
by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records
with a
titleID and an authorID (TA_BRIDGE). Since a book may have several
authors,
and each author may participate in several books this organization
is
necessary.

How can I create a report that displays each title from TITLES
followed
by
a
list of the authors associated with it?

Okay: to use this code, you would need to create a query that uses both
OrderDetails and Product. The query needs to output the fields you need for
the function (e.g. OrderID foreign key), and Product.ProductName.

Use the query as the "table" name, and you now have access to the product
name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Thanks Allen. However, I think that this does not accomplish the task I
was
asking about originally (or I am missing the understanding that it does do
it!).

In studying the text in the fConcatChild module and looking at the
Northwind
database, it looks like it concatenates data in the Order Details table.

However, the Order Details table serves as a "bridge" between the Orders
table and the Products table. My need takes data from the Products table
that goes with each Order; the Order Details tables provides the linkage
between these tables.

In the context of this example, what I require is to create for each
record
in the Orders table a string that concatenates the ProductName data from
the
Products table for all the ProductIDs called out in the Order Details
table.

If I am really being dense here please excuse me, but I don't see that the
fConcatChild() function every references the Products table.

"Allen Browne" wrote:

1. Click the Modules tab of the Database window.

2. Click New. Access opens a new code window.

3. Paste in the code from the article.

4. To ensure Access understands it, choose Compile on the Debug menu.
If you get an Unknown Type error on the first line, choose References on
the
Tools menu, and check the box beside:
Microsoft DAO 3.6 Library

5. Save the module with a name such as Module1.

You can now use the function in a query, just like the built-in functions
such as Trim() or Left(). Follow the example so it reads your table name,
primary key field, field to concatenate, data type, etc. For the data
type,
use "String" (include the quotes) for a text field. For a Number field,
use
"Long", "Integer", or "Double", depending on the data type of your field
(first property in the lower pane, when you open the table in design
view.)

"Tom Gettys" wrote in message
...
OK, I am ready to attempt to implement your suggested solution, but I
have
no
idea where to put the code (not to mention how to invoke it. I was
thinking
it would be best to get it working in a query, and then base the report
on
the query.

The article you cited says the code can be used in a query, but gives
no
clue how to embed the code, so I am hoping you will enlighten me about
this.

"Allen Browne" wrote:

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do
in
VBA.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done
using
code.

Thanks for your reponse Allen; It is very cool that I can get an
answer
so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common
that
it
would be available more easily, so I am curious why it is not. Can
you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors
into
a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title
followed
by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records
with a
titleID and an authorID (TA_BRIDGE). Since a book may have
several
authors,
and each author may participate in several books this
organization
is
necessary.

How can I create a report that displays each title from TITLES
followed
by
a
list of the authors associated with it?

Allen, thank you so very much! I was able to actually get this working, and
even extend it a bit. There were several missing pieces to untangle, but
that just made the success that much sweeter.

I don't know why you pour so much of yourself into this, but I certainly
appreciate your generous efforts. I really hope Microsoft is paying you a
hefty fee for your efforts.

FYI: this database I am developing is for the rather extensive collection of
music left in my stewardship by a friend that passed away recently. His son
asked if I would take on this task, so that it would live on after his dad.
I gladly accepted, as he was a great inspiration to me. I know little about
databases, but certainly know more now!

I just wanted you to know a bit about what you have contributed to. thank
you again.

"Allen Browne" wrote:

Okay: to use this code, you would need to create a query that uses both
OrderDetails and Product. The query needs to output the fields you need for
the function (e.g. OrderID foreign key), and Product.ProductName.

Use the query as the "table" name, and you now have access to the product
name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Thanks Allen. However, I think that this does not accomplish the task I
was
asking about originally (or I am missing the understanding that it does do
it!).

In studying the text in the fConcatChild module and looking at the
Northwind
database, it looks like it concatenates data in the Order Details table.

However, the Order Details table serves as a "bridge" between the Orders
table and the Products table. My need takes data from the Products table
that goes with each Order; the Order Details tables provides the linkage
between these tables.

In the context of this example, what I require is to create for each
record
in the Orders table a string that concatenates the ProductName data from
the
Products table for all the ProductIDs called out in the Order Details
table.

If I am really being dense here please excuse me, but I don't see that the
fConcatChild() function every references the Products table.

"Allen Browne" wrote:

1. Click the Modules tab of the Database window.

2. Click New. Access opens a new code window.

3. Paste in the code from the article.

4. To ensure Access understands it, choose Compile on the Debug menu.
If you get an Unknown Type error on the first line, choose References on
the
Tools menu, and check the box beside:
Microsoft DAO 3.6 Library

5. Save the module with a name such as Module1.

You can now use the function in a query, just like the built-in functions
such as Trim() or Left(). Follow the example so it reads your table name,
primary key field, field to concatenate, data type, etc. For the data
type,
use "String" (include the quotes) for a text field. For a Number field,
use
"Long", "Integer", or "Double", depending on the data type of your field
(first property in the lower pane, when you open the table in design
view.)

"Tom Gettys" wrote in message
...
OK, I am ready to attempt to implement your suggested solution, but I
have
no
idea where to put the code (not to mention how to invoke it. I was
thinking
it would be best to get it working in a query, and then base the report
on
the query.

The article you cited says the code can be used in a query, but gives
no
clue how to embed the code, so I am hoping you will enlighten me about
this.

"Allen Browne" wrote:

The code may look scarier than it is.
It's quite easy to use. Just follow the example.

This is one of the things SQL doesn't do easily, so it's easier to do
in
VBA.

"Tom Gettys" wrote in message
...
Yikes! I assume that your answer means that this can only be done
using
code.

Thanks for your reponse Allen; It is very cool that I can get an
answer
so
fast, but the complexity of the answer is pretty discouraging.

I had imagined that this sort of functionality would be so common
that
it
would be available more easily, so I am curious why it is not. Can
you
explain what it is that makes it non-trivial?

"Allen Browne" wrote:

Base the report on the TITLES table, and concatenate the authors
into
a
string with code like this:
http://www.mvps.org/access/modules/mdl0004.htm

"Tom Gettys" wrote in message
...
I wish to create a report that has on each line a book title
followed
by
a
list of the authors.

The titles are in a table (TITLES), the authors are in a table
(AUTHORS),
and there is a "bridge" table between them that contains records
with a
titleID and an authorID (TA_BRIDGE). Since a book may have
several
authors,
and each author may participate in several books this
organization
is
necessary.

How can I create a report that displays each title from TITLES
followed
by
a
list of the authors associated with it?

Excellent! Thrilled you are able to not only get there, but take it further.

Presumably your 2nd paragraph is tongue-in-cheek: you are probably aware MS
doesn't pay MVPs.

Glad to know you are able to contribute to your friend's life in a way that
will be encouraging and endearing as he works through his grief and
memories.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Tom Gettys" wrote in message
...
Allen, thank you so very much! I was able to actually get this working,
and
even extend it a bit. There were several missing pieces to untangle, but
that just made the success that much sweeter.

I don't know why you pour so much of yourself into this, but I certainly
appreciate your generous efforts. I really hope Microsoft is paying you a
hefty fee for your efforts.

FYI: this database I am developing is for the rather extensive collection
of
music left in my stewardship by a friend that passed away recently. His
son
asked if I would take on this task, so that it would live on after his
dad.
I gladly accepted, as he was a great inspiration to me. I know little
about
databases, but certainly know more now!

I just wanted you to know a bit about what you have contributed to.
thank
you again.