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
|
|||
|
|||
Merging with Access queries - Multiple
Hi, all. I'm attempting to create a merge document that draws its'
information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
#2
|
|||
|
|||
It's not really clear exactly what is the cause of your problem, but one
thing I can tell you is that Access is far better at manipulating data than is Word. Without knowing the details it's hard to give any specific advice. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Hi, all. I'm attempting to create a merge document that draws its' information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
#3
|
|||
|
|||
I'm not sure what specifics you need but I'll try:
My document is a report that draws information from a table that has a project name and general information about a project. But another table contains information about who is involved with the project (the Contacts). It is in this secondary table because there could be many Contacts for the one Project. Sometimes (like at the start of a project) there may be NO data in the secondary Contacts table. So a query in Access wouldn't have any results and would result in a blank Word document (except for the headings) after a merge. I am trying to make sure that if there is data in the Project table but NO data in the Contacts table, there would still be General Project information in my final merged document. I have made the Projects table in Access my merge data source which works fine. But the only way I can figure out to try and include the information from the Contacts table (if it exists) is to create ANOTHER merged document that just has the Contacts information and then use an {INCLUDEText} field to include the information from the Contacts merged document in the first document with the Projects info. But Word won't let me do this and says that it can't find the fields from the Contacts in the merge data source (which is from the Proejcts table). In Access, I have been able to create a report that contains all the data I need but when I convert it to Word, it becomes an RTF file and I need it in Word format (besides all the formatting clean-up that has to be done in an RTF file). Maybe I should pose this question to an Access group. What do you think? Thanks for any ideas. Carol. "Doug Robbins" wrote in message ... It's not really clear exactly what is the cause of your problem, but one thing I can tell you is that Access is far better at manipulating data than is Word. Without knowing the details it's hard to give any specific advice. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Hi, all. I'm attempting to create a merge document that draws its' information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
#4
|
|||
|
|||
Well, I would do it all in Access. It's report formatting capabilities are
quite good so I don't understand the need to bring it into Word, or, if you do, why there is so much formatting to be done. For mailmerge, Word can really only use a "flat file" as the data source. Using visual basic, there are ways to get around this, so if you are up to that, you can learn a bit from the "Multiple items per condition" item under the "Special merges" section of fellow MVP CIndy Meister's website at: http://homepage.swissonline.ch/cindy...r/MergFram.htm and my looking at another MVP, Albert Kallal's website: http://www.attcanada.net/~kallal.msn.../msaccess.html -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... I'm not sure what specifics you need but I'll try: My document is a report that draws information from a table that has a project name and general information about a project. But another table contains information about who is involved with the project (the Contacts). It is in this secondary table because there could be many Contacts for the one Project. Sometimes (like at the start of a project) there may be NO data in the secondary Contacts table. So a query in Access wouldn't have any results and would result in a blank Word document (except for the headings) after a merge. I am trying to make sure that if there is data in the Project table but NO data in the Contacts table, there would still be General Project information in my final merged document. I have made the Projects table in Access my merge data source which works fine. But the only way I can figure out to try and include the information from the Contacts table (if it exists) is to create ANOTHER merged document that just has the Contacts information and then use an {INCLUDEText} field to include the information from the Contacts merged document in the first document with the Projects info. But Word won't let me do this and says that it can't find the fields from the Contacts in the merge data source (which is from the Proejcts table). In Access, I have been able to create a report that contains all the data I need but when I convert it to Word, it becomes an RTF file and I need it in Word format (besides all the formatting clean-up that has to be done in an RTF file). Maybe I should pose this question to an Access group. What do you think? Thanks for any ideas. Carol. "Doug Robbins" wrote in message ... It's not really clear exactly what is the cause of your problem, but one thing I can tell you is that Access is far better at manipulating data than is Word. Without knowing the details it's hard to give any specific advice. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Hi, all. I'm attempting to create a merge document that draws its' information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
#5
|
|||
|
|||
Thanks, Doug. I've already produced the report in Access but have been told
that exporting it to Word (actually .RTF format) isn't good enough (because of the loss of formatting, fonts, etc.). But I've also been told that it needs to be in Word format because it has to be included with lots of other stuff for a board meeting. I'm already familiar with Cindy's and Albert's sites (even have them bookmarked) but I'll look at them again. Thanks. Carol. "Doug Robbins" wrote in message ... Well, I would do it all in Access. It's report formatting capabilities are quite good so I don't understand the need to bring it into Word, or, if you do, why there is so much formatting to be done. For mailmerge, Word can really only use a "flat file" as the data source. Using visual basic, there are ways to get around this, so if you are up to that, you can learn a bit from the "Multiple items per condition" item under the "Special merges" section of fellow MVP CIndy Meister's website at: http://homepage.swissonline.ch/cindy...r/MergFram.htm and my looking at another MVP, Albert Kallal's website: http://www.attcanada.net/~kallal.msn.../msaccess.html -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... I'm not sure what specifics you need but I'll try: My document is a report that draws information from a table that has a project name and general information about a project. But another table contains information about who is involved with the project (the Contacts). It is in this secondary table because there could be many Contacts for the one Project. Sometimes (like at the start of a project) there may be NO data in the secondary Contacts table. So a query in Access wouldn't have any results and would result in a blank Word document (except for the headings) after a merge. I am trying to make sure that if there is data in the Project table but NO data in the Contacts table, there would still be General Project information in my final merged document. I have made the Projects table in Access my merge data source which works fine. But the only way I can figure out to try and include the information from the Contacts table (if it exists) is to create ANOTHER merged document that just has the Contacts information and then use an {INCLUDEText} field to include the information from the Contacts merged document in the first document with the Projects info. But Word won't let me do this and says that it can't find the fields from the Contacts in the merge data source (which is from the Proejcts table). In Access, I have been able to create a report that contains all the data I need but when I convert it to Word, it becomes an RTF file and I need it in Word format (besides all the formatting clean-up that has to be done in an RTF file). Maybe I should pose this question to an Access group. What do you think? Thanks for any ideas. Carol. "Doug Robbins" wrote in message ... It's not really clear exactly what is the cause of your problem, but one thing I can tell you is that Access is far better at manipulating data than is Word. Without knowing the details it's hard to give any specific advice. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Hi, all. I'm attempting to create a merge document that draws its' information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
#6
|
|||
|
|||
Hi Carol,
There is no reason that you should lose any formatting when sending an Access Report to an .RTF file. On rare occassions on very complex reports, I have noticed minor differences in character spacing that may cause a "line wrap" to occur that necessitated going back to Access and making a slight adjustment to the width of the offending control on the report. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Thanks, Doug. I've already produced the report in Access but have been told that exporting it to Word (actually .RTF format) isn't good enough (because of the loss of formatting, fonts, etc.). But I've also been told that it needs to be in Word format because it has to be included with lots of other stuff for a board meeting. I'm already familiar with Cindy's and Albert's sites (even have them bookmarked) but I'll look at them again. Thanks. Carol. "Doug Robbins" wrote in message ... Well, I would do it all in Access. It's report formatting capabilities are quite good so I don't understand the need to bring it into Word, or, if you do, why there is so much formatting to be done. For mailmerge, Word can really only use a "flat file" as the data source. Using visual basic, there are ways to get around this, so if you are up to that, you can learn a bit from the "Multiple items per condition" item under the "Special merges" section of fellow MVP CIndy Meister's website at: http://homepage.swissonline.ch/cindy...r/MergFram.htm and my looking at another MVP, Albert Kallal's website: http://www.attcanada.net/~kallal.msn.../msaccess.html -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... I'm not sure what specifics you need but I'll try: My document is a report that draws information from a table that has a project name and general information about a project. But another table contains information about who is involved with the project (the Contacts). It is in this secondary table because there could be many Contacts for the one Project. Sometimes (like at the start of a project) there may be NO data in the secondary Contacts table. So a query in Access wouldn't have any results and would result in a blank Word document (except for the headings) after a merge. I am trying to make sure that if there is data in the Project table but NO data in the Contacts table, there would still be General Project information in my final merged document. I have made the Projects table in Access my merge data source which works fine. But the only way I can figure out to try and include the information from the Contacts table (if it exists) is to create ANOTHER merged document that just has the Contacts information and then use an {INCLUDEText} field to include the information from the Contacts merged document in the first document with the Projects info. But Word won't let me do this and says that it can't find the fields from the Contacts in the merge data source (which is from the Proejcts table). In Access, I have been able to create a report that contains all the data I need but when I convert it to Word, it becomes an RTF file and I need it in Word format (besides all the formatting clean-up that has to be done in an RTF file). Maybe I should pose this question to an Access group. What do you think? Thanks for any ideas. Carol. "Doug Robbins" wrote in message ... It's not really clear exactly what is the cause of your problem, but one thing I can tell you is that Access is far better at manipulating data than is Word. Without knowing the details it's hard to give any specific advice. -- Please respond to the Newsgroup for the benefit of others who may be interested. Questions sent directly to me will only be answered on a paid consulting basis. Hope this helps, Doug Robbins - Word MVP "CPutnam" wrote in message r.com... Hi, all. I'm attempting to create a merge document that draws its' information from multiple Access queries. The data that I need to include in the document comes from multiple tables. I've created 3 queries -- one combines information from about 6 tables and the other two just have information from 1 table each. I split the queries out this way to try and get around the problem with relational databases that if there isn't any information in one of the tables in a query then the whole query returns nothing. (And if I try to do Right Joins to get around this problem, Access gets really mixed up.) I've found out (through reading this newsgroup and others) that each merge document can only have one data source. I've also found out that Master documents are too buggy to count on (especially since the merge document will be distributed to multiple users in many different locations). I also tried to create other merge documents for using the two other queries as their data sources and then use {INCLUDETEXT} to bring in information from those documents but the "master" merge document didn't want to use any fields that weren't in the main data source. I could do multiple merges into different documents and than have the users copy and paste into the "master" merge document but I can already hear the protests! Does anyone have any ideas at all that might help me? Are there alternatives to using Master Documents? Thanks in advance. Carol. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Access query for same parameter in multiple columns | Rich Palarea | General Discussion | 2 | August 19th, 2004 10:54 AM |
Merging from an Access Query | Sandra | Mailmerge | 1 | August 10th, 2004 06:03 PM |
Access DB Queries to MS Word Document, Applying Styles | Mark Jerde | General Discussion | 10 | June 29th, 2004 10:07 PM |
Need help with Access decision | aualias | General Discussion | 23 | June 21st, 2004 02:04 AM |