Lots of queries displayed in one report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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/access/HA011730581033.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 said:
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?
Click to expand...


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.
 
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 said:
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/access/HA011730581033.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 said:
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!
Click to expand...
Click to expand...
 
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 said:
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 said:
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/access/HA011730581033.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
..

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!
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...

Thanks!
 
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 & """")
 
Most helpful. Thank you!

Marshall Barton said:
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]

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,
Click to expand...
Click to expand...
 
Back
Top