If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Working with many-to-many relationships
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? |
#3
|
|||
|
|||
Working with many-to-many relationships
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? |
#4
|
|||
|
|||
Working with many-to-many relationships
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? |
#5
|
|||
|
|||
Working with many-to-many relationships
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? |
#6
|
|||
|
|||
Working with many-to-many relationships
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? |
#7
|
|||
|
|||
Working with many-to-many relationships
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? |
#8
|
|||
|
|||
Working with many-to-many relationships
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? |
#9
|
|||
|
|||
Working with many-to-many relationships
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? |
#10
|
|||
|
|||
Working with many-to-many relationships
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. |
Thread Tools | |
Display Modes | |
|
|