Lots of queries displayed in one report

[jenniebentham]

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!

 

[John Spencer]

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
..

 

[Marshall Barton]

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.

 

[jenniebentham]

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.

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
..

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!

 

[John Spencer]

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
..

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.

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!

 

[A Paid Observer]

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]

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 & """")

 

[A Paid Observer]

Most helpful. Thank you!

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,