Working with many-to-many relationships

[Tom Gettys]

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 Browne]

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]

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]

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]

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]

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]

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]

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.

 

[Tom Gettys]

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]

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.