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
|
|||
|
|||
Set up a report using more than one query
I have set up 5 different queries to calculate one specific total each.
I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#2
|
|||
|
|||
A report has one record source. Either place all your queries into a single
query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#3
|
|||
|
|||
Duane,
God bless you. This problem has been nagging me for a long time. I was developing a new database and I needed to know the answer. I am a very simple minded person and I needed to have a simple answer. After reading your directions on the subject, I thought and checked out each option. #1 It was impossible to combine the queries into one due to conflicting criteria in the same field. #2 I don't know how to write code. #3 The information on the Dlookup ( ) Type functions didn't seem to fit and was very confusing to me. #4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me. ***For those people out there that are like me and know nothing about writing code or using complex expressions, here's how I made the "SUBREPORT" option workwork for me. 1. Use the report wizard in reports to setup a report. (use "ONE" of your tables, forms or queries that you want on the report) You may have to delete a heading in the report if you do not want it in the header. It depends on how you ultimately want to set up the report. You will notice that the report wizard will put a label and a control box on the report. This is very simple to get rid of. In design view, in the properties of each, change the "Special Effects" to Flat and the "Border Style" to Transparent. Next, on the label and controlbox change the back fill color to White. Finally, Select the font color you want on the label text. From here on it's just a matter of positioning the labels and control boxs on the report. (You cannot see the labels and control boxes on the report in report view, it looks like a regular printed report) 2. Here's where the "SUBREPORTS" come in. In design view of the report, click on the subreport tool and drag it to where you what it on the report and click. It will go through another wizard to select which table, query, etc. Select only "ONE" and create the subreport. Do as many subreports as you want on the report. They must be done individually. Position them on the report where you want them. 3. Follow step 1. after the report was created on all of the labels and control boxes in the subreports. Then it's just a matter of positioning. Do your placement in design view and switch to report view to check your work. Duane, the above proceedure is probably not what you had in mind, but it works for me and it will work for others like me that are mentally challenge by code and complexed expressions. It takes just a little graphic sense to obtain the same result. I want to thank you so much for responding to my need. With your help, I solved a big problem that had been bugging me for a very long...time. Have a Blessed Day jbeck2010 "Duane Hookom" wrote: A report has one record source. Either place all your queries into a single query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#4
|
|||
|
|||
jbeck2010,
Thanks for sharing your experience it was not "simple minded". What is even more cool is in step 2, if your main report is not maximized, you can position it where you can see both the database window (reports) and your main report in design view. Rather than drag the subreport control onto the report, click and drag the actual subreport on to the main report. Access is an amazing application and I am glad to hear you are experiencing some success. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, God bless you. This problem has been nagging me for a long time. I was developing a new database and I needed to know the answer. I am a very simple minded person and I needed to have a simple answer. After reading your directions on the subject, I thought and checked out each option. #1 It was impossible to combine the queries into one due to conflicting criteria in the same field. #2 I don't know how to write code. #3 The information on the Dlookup ( ) Type functions didn't seem to fit and was very confusing to me. #4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me. ***For those people out there that are like me and know nothing about writing code or using complex expressions, here's how I made the "SUBREPORT" option workwork for me. 1. Use the report wizard in reports to setup a report. (use "ONE" of your tables, forms or queries that you want on the report) You may have to delete a heading in the report if you do not want it in the header. It depends on how you ultimately want to set up the report. You will notice that the report wizard will put a label and a control box on the report. This is very simple to get rid of. In design view, in the properties of each, change the "Special Effects" to Flat and the "Border Style" to Transparent. Next, on the label and controlbox change the back fill color to White. Finally, Select the font color you want on the label text. From here on it's just a matter of positioning the labels and control boxs on the report. (You cannot see the labels and control boxes on the report in report view, it looks like a regular printed report) 2. Here's where the "SUBREPORTS" come in. In design view of the report, click on the subreport tool and drag it to where you what it on the report and click. It will go through another wizard to select which table, query, etc. Select only "ONE" and create the subreport. Do as many subreports as you want on the report. They must be done individually. Position them on the report where you want them. 3. Follow step 1. after the report was created on all of the labels and control boxes in the subreports. Then it's just a matter of positioning. Do your placement in design view and switch to report view to check your work. Duane, the above proceedure is probably not what you had in mind, but it works for me and it will work for others like me that are mentally challenge by code and complexed expressions. It takes just a little graphic sense to obtain the same result. I want to thank you so much for responding to my need. With your help, I solved a big problem that had been bugging me for a very long...time. Have a Blessed Day jbeck2010 "Duane Hookom" wrote: A report has one record source. Either place all your queries into a single query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#5
|
|||
|
|||
Duane,
Thanks again for your additional information and encouragement. I will definitely use your additional information on similar type reports. Duane, you appear to be a very intelligent and knowledgeable Individual. I really appreciate your help. If you would be so kind as to help me with one more small problem. I would like to have a form open and stay in maximized view. I have tried a solution that was posted on the discussion group, but it didn't work. It was to type DoCmd.Maximize in the forms Open or Load event proceedure. The result was that I received the following error message: "Can't find the macro DoCmd." The macro (or it's group) doesn't exist, or the macro is new but hasn't been saved. Am I doing something wrong or not doing something else that I should be doing. Puzzled jbeck2010 "Duane Hookom" wrote: jbeck2010, Thanks for sharing your experience it was not "simple minded". What is even more cool is in step 2, if your main report is not maximized, you can position it where you can see both the database window (reports) and your main report in design view. Rather than drag the subreport control onto the report, click and drag the actual subreport on to the main report. Access is an amazing application and I am glad to hear you are experiencing some success. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, God bless you. This problem has been nagging me for a long time. I was developing a new database and I needed to know the answer. I am a very simple minded person and I needed to have a simple answer. After reading your directions on the subject, I thought and checked out each option. #1 It was impossible to combine the queries into one due to conflicting criteria in the same field. #2 I don't know how to write code. #3 The information on the Dlookup ( ) Type functions didn't seem to fit and was very confusing to me. #4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me. ***For those people out there that are like me and know nothing about writing code or using complex expressions, here's how I made the "SUBREPORT" option workwork for me. 1. Use the report wizard in reports to setup a report. (use "ONE" of your tables, forms or queries that you want on the report) You may have to delete a heading in the report if you do not want it in the header. It depends on how you ultimately want to set up the report. You will notice that the report wizard will put a label and a control box on the report. This is very simple to get rid of. In design view, in the properties of each, change the "Special Effects" to Flat and the "Border Style" to Transparent. Next, on the label and controlbox change the back fill color to White. Finally, Select the font color you want on the label text. From here on it's just a matter of positioning the labels and control boxs on the report. (You cannot see the labels and control boxes on the report in report view, it looks like a regular printed report) 2. Here's where the "SUBREPORTS" come in. In design view of the report, click on the subreport tool and drag it to where you what it on the report and click. It will go through another wizard to select which table, query, etc. Select only "ONE" and create the subreport. Do as many subreports as you want on the report. They must be done individually. Position them on the report where you want them. 3. Follow step 1. after the report was created on all of the labels and control boxes in the subreports. Then it's just a matter of positioning. Do your placement in design view and switch to report view to check your work. Duane, the above proceedure is probably not what you had in mind, but it works for me and it will work for others like me that are mentally challenge by code and complexed expressions. It takes just a little graphic sense to obtain the same result. I want to thank you so much for responding to my need. With your help, I solved a big problem that had been bugging me for a very long...time. Have a Blessed Day jbeck2010 "Duane Hookom" wrote: A report has one record source. Either place all your queries into a single query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#6
|
|||
|
|||
You are probably attempting to place a line of code into a property value.
To add code to an event procedure, click the drop down of the event property and select [Event Procedure]. You can then click the [...] button to get to the code window for that event. To see how this might work, find the On Dbl Click event of the detail section of a form. Drop down the list and select [Event Procedure]. Then click the builder button [...] and change the code to look like: Private Sub Detail_DblClick(Cancel As Integer) MsgBox "You double-clicked the detail section", _ vbOKOnly + vbExclamation, "Dbl Click Event" End Sub Make sure you have a space to the left of the underscore in the MsgBox line of code. If you see responses in these news groups, most of the time we are suggesting code entry. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thanks again for your additional information and encouragement. I will definitely use your additional information on similar type reports. Duane, you appear to be a very intelligent and knowledgeable Individual. I really appreciate your help. If you would be so kind as to help me with one more small problem. I would like to have a form open and stay in maximized view. I have tried a solution that was posted on the discussion group, but it didn't work. It was to type DoCmd.Maximize in the forms Open or Load event proceedure. The result was that I received the following error message: "Can't find the macro DoCmd." The macro (or it's group) doesn't exist, or the macro is new but hasn't been saved. Am I doing something wrong or not doing something else that I should be doing. Puzzled jbeck2010 "Duane Hookom" wrote: jbeck2010, Thanks for sharing your experience it was not "simple minded". What is even more cool is in step 2, if your main report is not maximized, you can position it where you can see both the database window (reports) and your main report in design view. Rather than drag the subreport control onto the report, click and drag the actual subreport on to the main report. Access is an amazing application and I am glad to hear you are experiencing some success. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, God bless you. This problem has been nagging me for a long time. I was developing a new database and I needed to know the answer. I am a very simple minded person and I needed to have a simple answer. After reading your directions on the subject, I thought and checked out each option. #1 It was impossible to combine the queries into one due to conflicting criteria in the same field. #2 I don't know how to write code. #3 The information on the Dlookup ( ) Type functions didn't seem to fit and was very confusing to me. #4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me. ***For those people out there that are like me and know nothing about writing code or using complex expressions, here's how I made the "SUBREPORT" option workwork for me. 1. Use the report wizard in reports to setup a report. (use "ONE" of your tables, forms or queries that you want on the report) You may have to delete a heading in the report if you do not want it in the header. It depends on how you ultimately want to set up the report. You will notice that the report wizard will put a label and a control box on the report. This is very simple to get rid of. In design view, in the properties of each, change the "Special Effects" to Flat and the "Border Style" to Transparent. Next, on the label and controlbox change the back fill color to White. Finally, Select the font color you want on the label text. From here on it's just a matter of positioning the labels and control boxs on the report. (You cannot see the labels and control boxes on the report in report view, it looks like a regular printed report) 2. Here's where the "SUBREPORTS" come in. In design view of the report, click on the subreport tool and drag it to where you what it on the report and click. It will go through another wizard to select which table, query, etc. Select only "ONE" and create the subreport. Do as many subreports as you want on the report. They must be done individually. Position them on the report where you want them. 3. Follow step 1. after the report was created on all of the labels and control boxes in the subreports. Then it's just a matter of positioning. Do your placement in design view and switch to report view to check your work. Duane, the above proceedure is probably not what you had in mind, but it works for me and it will work for others like me that are mentally challenge by code and complexed expressions. It takes just a little graphic sense to obtain the same result. I want to thank you so much for responding to my need. With your help, I solved a big problem that had been bugging me for a very long...time. Have a Blessed Day jbeck2010 "Duane Hookom" wrote: A report has one record source. Either place all your queries into a single query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
#7
|
|||
|
|||
Duane,
As usual, you were right on the money. It got me to thinking about the code: DoCmd.Maximize, I was not putting it where it was suppose to be. I was just typing it in the "ON Open" property box. That is the reason for the error. After reading your comments, In the "On Open" Property box I clicked on the build button and it gave me a choice of expression builder, macro builder, or code builder. I selected code builder and it took me to the code and placed the curser in a specific spot. With my fingers crossed I typed in Docmd.Maximize and closed the window. I closed the form and reopened it. "Bam" It was Maximized. It works great! Thanks again Duane for your guidance. "Your The Man" I, also, have something further to report on the other problem I was having about combining 5 different queries on 1 report. My alternative solution worked great on the report, but when I opened the the original forms that subform tool made the subforms from their graphics had been changed. It was a mess. I corrected the changes to the forms and deleted the report and started over.With your great advise in mind, I did it your cool way. I made the original report the way I mentioned earlier and then minimized the windows to take advantage of your drag and drop technique using the queries instead of forms. The result was, it was much easier and faster to make the graphic changes and placement of the labels and controls. The alignment and look of the report is first class. No one would ever Know it was done that way. The best part is nothing else changed and everything looks geat and works like a champ. Duane, I really appreciate you sharing your knowledge and you have my utmost respect. Have a Blessed Day, jbeck2010 "Duane Hookom" wrote: You are probably attempting to place a line of code into a property value. To add code to an event procedure, click the drop down of the event property and select [Event Procedure]. You can then click the [...] button to get to the code window for that event. To see how this might work, find the On Dbl Click event of the detail section of a form. Drop down the list and select [Event Procedure]. Then click the builder button [...] and change the code to look like: Private Sub Detail_DblClick(Cancel As Integer) MsgBox "You double-clicked the detail section", _ vbOKOnly + vbExclamation, "Dbl Click Event" End Sub Make sure you have a space to the left of the underscore in the MsgBox line of code. If you see responses in these news groups, most of the time we are suggesting code entry. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, Thanks again for your additional information and encouragement. I will definitely use your additional information on similar type reports. Duane, you appear to be a very intelligent and knowledgeable Individual. I really appreciate your help. If you would be so kind as to help me with one more small problem. I would like to have a form open and stay in maximized view. I have tried a solution that was posted on the discussion group, but it didn't work. It was to type DoCmd.Maximize in the forms Open or Load event proceedure. The result was that I received the following error message: "Can't find the macro DoCmd." The macro (or it's group) doesn't exist, or the macro is new but hasn't been saved. Am I doing something wrong or not doing something else that I should be doing. Puzzled jbeck2010 "Duane Hookom" wrote: jbeck2010, Thanks for sharing your experience it was not "simple minded". What is even more cool is in step 2, if your main report is not maximized, you can position it where you can see both the database window (reports) and your main report in design view. Rather than drag the subreport control onto the report, click and drag the actual subreport on to the main report. Access is an amazing application and I am glad to hear you are experiencing some success. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... Duane, God bless you. This problem has been nagging me for a long time. I was developing a new database and I needed to know the answer. I am a very simple minded person and I needed to have a simple answer. After reading your directions on the subject, I thought and checked out each option. #1 It was impossible to combine the queries into one due to conflicting criteria in the same field. #2 I don't know how to write code. #3 The information on the Dlookup ( ) Type functions didn't seem to fit and was very confusing to me. #4 *Hallelujah, Praise Jesus..."SUBREPORTS" IS THE ANSWER...for me. ***For those people out there that are like me and know nothing about writing code or using complex expressions, here's how I made the "SUBREPORT" option workwork for me. 1. Use the report wizard in reports to setup a report. (use "ONE" of your tables, forms or queries that you want on the report) You may have to delete a heading in the report if you do not want it in the header. It depends on how you ultimately want to set up the report. You will notice that the report wizard will put a label and a control box on the report. This is very simple to get rid of. In design view, in the properties of each, change the "Special Effects" to Flat and the "Border Style" to Transparent. Next, on the label and controlbox change the back fill color to White. Finally, Select the font color you want on the label text. From here on it's just a matter of positioning the labels and control boxs on the report. (You cannot see the labels and control boxes on the report in report view, it looks like a regular printed report) 2. Here's where the "SUBREPORTS" come in. In design view of the report, click on the subreport tool and drag it to where you what it on the report and click. It will go through another wizard to select which table, query, etc. Select only "ONE" and create the subreport. Do as many subreports as you want on the report. They must be done individually. Position them on the report where you want them. 3. Follow step 1. after the report was created on all of the labels and control boxes in the subreports. Then it's just a matter of positioning. Do your placement in design view and switch to report view to check your work. Duane, the above proceedure is probably not what you had in mind, but it works for me and it will work for others like me that are mentally challenge by code and complexed expressions. It takes just a little graphic sense to obtain the same result. I want to thank you so much for responding to my need. With your help, I solved a big problem that had been bugging me for a very long...time. Have a Blessed Day jbeck2010 "Duane Hookom" wrote: A report has one record source. Either place all your queries into a single query, use subreports, write some code, or try DLookup() type functions. -- Duane Hookom MS Access MVP "jbeck2010" wrote in message ... I have set up 5 different queries to calculate one specific total each. I have also set up 5 accompaning forms. They work great. When I try to produce a report through the report wizard it says that the source of the queries are not compatible or something like that. What I would like to do is be able to print these individual totals on one printed report. Please help jbeck2010 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help!! I'm running around in circles! | CathyA | New Users | 19 | December 12th, 2004 07:50 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Newbie? Do I use Report or Query | John Egan | New Users | 11 | June 28th, 2004 08:31 PM |