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
|
|||
|
|||
Lots of queries displayed in one report
I have 42 queries that I need to display in one report. Each query has its
own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Apologies if this has already been answered, I have searched but couldn't find anything I could understand! |
#2
|
|||
|
|||
Lots of queries displayed in one report
You can use a form to input the date range one time.
Check out this article for a detailed discussion. http://www.fontstuff.com/access/acctut08.htm Or check out this from MS http://office.microsoft.com/en-us/ac...730581033.aspx Or for another example http://allenbrowne.com/ser-62.html You'll need to create a small unbound Form (let's call it frmCriteria) with two unbound controls on it - StartDate and EndDate. Save this form. Now use =[Forms]![frmCriteria]![StartDate] and =[Forms]![frmCriteria]![EndDate] as the criterion in your queries. Open the form before you run the queries. or better you can call the report using a button on the form DOCMD.OpenReport "ReportName" As for having 42 queries and 42 subreports, you might consider using the vb domain functions to get your values DCount("*","NameOfQuery") or DLookup("CountItems","NameOfQuery") can be used to return a single value from a query or table. If the 42 queries are getting data from the same table then there may well be a way of consolidating some of the queries. However without details it is hard to say what can be done. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "jenniebentham" wrote in message ... I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Apologies if this has already been answered, I have searched but couldn't find anything I could understand! |
#3
|
|||
|
|||
Lots of queries displayed in one report
jenniebentham wrote:
I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Depends on what your queries are doing. If your queries are only returnng a single row with just one value, you can use DCount or DSum in a report text box instead of using separate queries and you certainly do not need a separate subreport for each query. If your queries are too complex to replace with a domain aggregate function, then you can use a DLookup to retrieve the value from a query. If your queries are returning multiple field values, then you should consider using code to open a recordset to get the one row results, and then stuff the values into report text boxes. You should only need to use subreports if the queries return multiple rows. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Lots of queries displayed in one report
Thanks John. I've done a little parameter input form now that works.
I ended up doing lots of individual sub reports. However, I need to do a calculation on 2 values from 2 separate sub reports and display the info within the parent report. Can this be done? eg. I've tried a text box with [report1]![field1] + [report2]![field1], but it doen't like it. "John Spencer" wrote: You can use a form to input the date range one time. Check out this article for a detailed discussion. http://www.fontstuff.com/access/acctut08.htm Or check out this from MS http://office.microsoft.com/en-us/ac...730581033.aspx Or for another example http://allenbrowne.com/ser-62.html You'll need to create a small unbound Form (let's call it frmCriteria) with two unbound controls on it - StartDate and EndDate. Save this form. Now use =[Forms]![frmCriteria]![StartDate] and =[Forms]![frmCriteria]![EndDate] as the criterion in your queries. Open the form before you run the queries. or better you can call the report using a button on the form DOCMD.OpenReport "ReportName" As for having 42 queries and 42 subreports, you might consider using the vb domain functions to get your values DCount("*","NameOfQuery") or DLookup("CountItems","NameOfQuery") can be used to return a single value from a query or table. If the 42 queries are getting data from the same table then there may well be a way of consolidating some of the queries. However without details it is hard to say what can be done. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "jenniebentham" wrote in message ... I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Apologies if this has already been answered, I have searched but couldn't find anything I could understand! |
#5
|
|||
|
|||
Lots of queries displayed in one report
You will need to reference the objects correctly
Reports![Name of your report]![Name of SubReport Control].Report![NameOfControl on subreport] You might be able to use ME![Name of SubReport Control].Report![NameOfControl on subreport] -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "jenniebentham" wrote in message news Thanks John. I've done a little parameter input form now that works. I ended up doing lots of individual sub reports. However, I need to do a calculation on 2 values from 2 separate sub reports and display the info within the parent report. Can this be done? eg. I've tried a text box with [report1]![field1] + [report2]![field1], but it doen't like it. "John Spencer" wrote: You can use a form to input the date range one time. Check out this article for a detailed discussion. http://www.fontstuff.com/access/acctut08.htm Or check out this from MS http://office.microsoft.com/en-us/ac...730581033.aspx Or for another example http://allenbrowne.com/ser-62.html You'll need to create a small unbound Form (let's call it frmCriteria) with two unbound controls on it - StartDate and EndDate. Save this form. Now use =[Forms]![frmCriteria]![StartDate] and =[Forms]![frmCriteria]![EndDate] as the criterion in your queries. Open the form before you run the queries. or better you can call the report using a button on the form DOCMD.OpenReport "ReportName" As for having 42 queries and 42 subreports, you might consider using the vb domain functions to get your values DCount("*","NameOfQuery") or DLookup("CountItems","NameOfQuery") can be used to return a single value from a query or table. If the 42 queries are getting data from the same table then there may well be a way of consolidating some of the queries. However without details it is hard to say what can be done. -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County .. "jenniebentham" wrote in message ... I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Apologies if this has already been answered, I have searched but couldn't find anything I could understand! |
#6
|
|||
|
|||
Lots of queries displayed in one report
Marshall,
Based on your post, I just fixed one of my reports that was drawing just a total or sum from several different tables. It now uses DCount and DSum rather than 12 different queries (proved to be much quicker.) My question though, is how can I filter out records by date (example, =01/01/2007) using DCount or DSum? Thanks! "Marshall Barton" wrote: jenniebentham wrote: I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? Depends on what your queries are doing. If your queries are only returnng a single row with just one value, you can use DCount or DSum in a report text box instead of using separate queries and you certainly do not need a separate subreport for each query. If your queries are too complex to replace with a domain aggregate function, then you can use a DLookup to retrieve the value from a query. If your queries are returning multiple field values, then you should consider using code to open a recordset to get the one row results, and then stuff the values into report text boxes. You should only need to use subreports if the queries return multiple rows. -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
Lots of queries displayed in one report
The Domain Aggregate functions all accept a where condition
in the third argument. For example: DCount("somefield","table","datefield=" & Format(dteDate, "\#yyyy-m-d\#") ) Searching with a date criteria is kinda messy to avoid problems with Windows date formatting settings. For a number criteria, it would just be: DCount("somefield","table","numfield=" & intNum) For a text field use: DCount("somefield","table","textfield=""" & strText & """") -- Marsh MVP [MS Access] A Paid Observer wrote: Based on your post, I just fixed one of my reports that was drawing just a total or sum from several different tables. It now uses DCount and DSum rather than 12 different queries (proved to be much quicker.) My question though, is how can I filter out records by date (example, =01/01/2007) using DCount or DSum? "Marshall Barton" wrote: Depends on what your queries are doing. If your queries are only returnng a single row with just one value, you can use DCount or DSum in a report text box instead of using separate queries and you certainly do not need a separate subreport for each query. jenniebentham wrote: I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? |
#8
|
|||
|
|||
Lots of queries displayed in one report
Most helpful. Thank you!
"Marshall Barton" wrote: The Domain Aggregate functions all accept a where condition in the third argument. For example: DCount("somefield","table","datefield=" & Format(dteDate, "\#yyyy-m-d\#") ) Searching with a date criteria is kinda messy to avoid problems with Windows date formatting settings. For a number criteria, it would just be: DCount("somefield","table","numfield=" & intNum) For a text field use: DCount("somefield","table","textfield=""" & strText & """") -- Marsh MVP [MS Access] A Paid Observer wrote: Based on your post, I just fixed one of my reports that was drawing just a total or sum from several different tables. It now uses DCount and DSum rather than 12 different queries (proved to be much quicker.) My question though, is how can I filter out records by date (example, =01/01/2007) using DCount or DSum? "Marshall Barton" wrote: Depends on what your queries are doing. If your queries are only returnng a single row with just one value, you can use DCount or DSum in a report text box instead of using separate queries and you certainly do not need a separate subreport for each query. jenniebentham wrote: I have 42 queries that I need to display in one report. Each query has its own individual criteria, but the result is just a number (a count of the records). I need to display the results of each of these queries in one summary report. I have just started with sub reports, but wondered if there is a better way than creating 42 sub reports from 42 queries! Also some of the queries have the same parameters (a date range) how do I stop the dates being asked for several times? |
Thread Tools | |
Display Modes | |
|
|