Multiple access reports combined into one

[Daniel Lalonde]

Hi,

Looking for suggestions here. I have been asked to create
60 reports with basically the same data for each. Each
page contains a summary of statistics for a
particular "location", and is currently based on a
seperate query for each "location". I would like to
create just one report, but have it accept each different
query as an argument, so that in the end I would get 60
different "location" reports. Suggestions???

As it is now, I am basically creating a report for each
location, but it is highly redundant.

Thanks for any and all suggestions.

Daniel

 

[Shannon Webb]

Hi,

I'm working on this with Dan, and we do need more info.

Assuming that each report is using the same query as the recordsource but
just filtered differently by the "location"...

This is exactly the problem. As it stands now, each of the 60 reports are
using *different* queries as the recordsource (one location = one query =
one report). It is so redundant I'm pulling my hair out. The only way
I've been able to figure out how to use only one generic query/report is to
have the user manually input the location parameter in a prompt when running
the report. But, this is unacceptable to the client.

So what we're trying to figure out is a) if there is a way to pass each of
the 60 individual queries as arguments to *one* generic report, or b) use
one query (bringing back all locations?) as the recordsource for one report,
and then use an array/collection in VB to cycle through the location table
and produce 60 different reports for the 60 locations.

If this doesn't match your setup, then you could leave the recordsource
property for the ONE report blank, and then use code to set its recordsource
based on some choice made on a form. That is a bit more tricky but very
doable...

Could you give us a bit more info on this part? Right now there are no
forms involved (just a macro so the user can print all 60 reports in one
shot), but perhaps this is what we're looking for.

Thanks so much for your time,

Shannon

Assuming that each report is using the same query as the recordsource but
just filtered differently by the "location" value, use the "WHERE" argument
of the DoCmd.OpenReport command to filter by the chosen location when ONE
report is opened:

DoCmd.OpenReport "ReportName", , , "[Location]=" & Me.LocationControl.Value

If this doesn't match your setup, then you could leave the recordsource
property for the ONE report blank, and then use code to set its recordsource
based on some choice made on a form. That is a bit more tricky but very
doable.

Post back if you need more info.

--
Ken Snell
<MS ACCESS MVP>

Hi,

Looking for suggestions here. I have been asked to create
60 reports with basically the same data for each. Each
page contains a summary of statistics for a
particular "location", and is currently based on a
seperate query for each "location". I would like to
create just one report, but have it accept each different
query as an argument, so that in the end I would get 60
different "location" reports. Suggestions???

As it is now, I am basically creating a report for each
location, but it is highly redundant.

Thanks for any and all suggestions.

Daniel

 

[Ken Snell]

Is each query the same except for the location that it is using as its
criterion?

--
Ken Snell
<MS ACCESS MVP>

Hi,

I'm working on this with Dan, and we do need more info.

Assuming that each report is using the same query as the recordsource but
just filtered differently by the "location"...

This is exactly the problem. As it stands now, each of the 60 reports are
using *different* queries as the recordsource (one location = one query =
one report). It is so redundant I'm pulling my hair out. The only way
I've been able to figure out how to use only one generic query/report is to
have the user manually input the location parameter in a prompt when running
the report. But, this is unacceptable to the client.

So what we're trying to figure out is a) if there is a way to pass each of
the 60 individual queries as arguments to *one* generic report, or b) use
one query (bringing back all locations?) as the recordsource for one report,
and then use an array/collection in VB to cycle through the location table
and produce 60 different reports for the 60 locations.

If this doesn't match your setup, then you could leave the recordsource
property for the ONE report blank, and then use code to set its recordsource
based on some choice made on a form. That is a bit more tricky but very
doable...

Could you give us a bit more info on this part? Right now there are no
forms involved (just a macro so the user can print all 60 reports in one
shot), but perhaps this is what we're looking for.

Thanks so much for your time,

Shannon

Assuming that each report is using the same query as the recordsource but
just filtered differently by the "location" value, use the "WHERE" argument
of the DoCmd.OpenReport command to filter by the chosen location when ONE
report is opened:

DoCmd.OpenReport "ReportName", , , "[Location]=" & Me.LocationControl.Value

If this doesn't match your setup, then you could leave the recordsource
property for the ONE report blank, and then use code to set its recordsource
based on some choice made on a form. That is a bit more tricky but very
doable.

Post back if you need more info.

--
Ken Snell
<MS ACCESS MVP>

Hi,

Looking for suggestions here. I have been asked to create
60 reports with basically the same data for each. Each
page contains a summary of statistics for a
particular "location", and is currently based on a
seperate query for each "location". I would like to
create just one report, but have it accept each different
query as an argument, so that in the end I would get 60
different "location" reports. Suggestions???

As it is now, I am basically creating a report for each
location, but it is highly redundant.

Thanks for any and all suggestions.

Daniel

 

[Daniel Lalonde]

Hi again,

Yes, the only difference between queries is the location
criteria.

Daniel

-----Original Message-----
Is each query the same except for the location that it is using as its
criterion?

--
Ken Snell
<MS ACCESS MVP>

Hi,

I'm working on this with Dan, and we do need more info.

the recordsource
but

This is exactly the problem. As it stands now, each of the 60 reports are
using *different* queries as the recordsource (one location = one query =
one report). It is so redundant I'm pulling my hair out. The only way
I've been able to figure out how to use only one

generic query/report is
to

have the user manually input the location parameter in

a prompt when
running

the report. But, this is unacceptable to the client.

So what we're trying to figure out is a) if there is a way to pass each of
the 60 individual queries as arguments to *one* generic report, or b) use
one query (bringing back all locations?) as the

recordsource for one
report,

and then use an array/collection in VB to cycle through the location table
and produce 60 different reports for the 60 locations.
to set its
recordsource

Could you give us a bit more info on this part? Right now there are no
forms involved (just a macro so the user can print all 60 reports in one
shot), but perhaps this is what we're looking for.

Thanks so much for your time,

Shannon

the recordsource
but

use the "WHERE"
argument

chosen location when
ONE

report is opened:

DoCmd.OpenReport "ReportName", , , "[Location]=" & Me.LocationControl.Value

If this doesn't match your setup, then you could leave the recordsource
property for the ONE report blank, and then use code

to set its
recordsource

based on some choice made on a form. That is a bit more tricky but very
doable.

Post back if you need more info.

--
Ken Snell
<MS ACCESS MVP>

Hi,

Looking for suggestions here. I have been asked to create
60 reports with basically the same data for each. Each
page contains a summary of statistics for a
particular "location", and is currently based on a
seperate query for each "location". I would like to
create just one report, but have it accept each different
query as an argument, so that in the end I would get 60
different "location" reports. Suggestions???

As it is now, I am basically creating a report for each
location, but it is highly redundant.

Thanks for any and all suggestions.

Daniel

.

 

[Steve Schapel]

Shannon,

PMFJI, but it seems to me that you are making this a lot more
difficult than it needs to be. If I understand you correctly, you
only need one query and one report. Use the Sorting and Grouping
function in the report design to make a Location Header and Location
Footer section (Post back if you need more detailed help with this
aspect). Set the ForceNewPage property of the Location Footer to
After Section, and propably transfer your existing Report Header
design to the Location Header, and print it. You will end up with
essentially 60 "separate" reports, one for each location, in one step.

- Steve Schapel, Microsoft Access MVP

 

[Steve Schapel]

Shannon,

Thanks for the detailed explanation, which helps a lot.

The main thing I am puzzled about is the meaning of "not allowed to
change its structure". If you mean change the tables, fair enough.
Anything else you do, whatever it is, to solve the "problem", will
involve changing the structure of the database. This is an extremely
simple system but there are many mistakes in its design at present.
And the solution is also extremely simple, which is exactly as a said
before. Scrap your 121 queries and multiple copies of the report.
Scrap your DCount controls. Scrap the idea of subreports, which would
cause unnecessary complications. Replace all of this by one query and
one report, and use the report's Sorting & Grouping to produce the
desired result. Is such a structure change permitted? If you've
never done a report like this before, it might take a bit of work to
get it right. But it will be hugely simpler and less effort than any
other approach, and I promise it will be worth the effort.

- Steve Schapel, Microsoft Access MVP

 

[Larry Linson]

I'm jumping in 'way late here, and admit that I am having a little
difficulty following the details, but perhaps Totals Queries used, along
with your existing tables/queries as data sources in the Query that is the
Record Source for the Report could eliminate the DCount problem. Seems to me
that the only question at this point would be whether there is a common
field that can be used to join the first and second queries that you
describe.

It's easy enough, too, to set the intrinsic Page variable to 1 in the
OnFormat or OnPrint event of each location header to make the information
for each location appear to be a separate report.

If we are not oversimplifying, then you must be overcomplicating. <G> If
there's more clarification, please do it here in the newsgroup, not by
e-mail. Thanks.

Larry Linson
Microsoft Access MVP

 

[Steve Schapel]

Daniel,

... If you could suggest how to do a single
select query that would:
(a) group by location,
(b) count (total) the attributes for each location,
(c) group the attribute EventType and count (total) the
occurences of each,

I don't think this can be easily done in a query or queries. This is
the *whole point* of my earlier response... Don't do it in the
queries, do it in the report.

It may turn out that I don't fully understand the requirements, but
based on what you have told us so far, I can see absolutely no reason
to complicate the scenario with Totals Queries.

I am afraid I do not know what you mean by "attribute" in this
context. However, I am happy to pursue this further with you if you
can post back with the SQL view of the existing first query, and also
give us some more specific details, maybe examples, about the data in
the significant fields.

- Steve Schapel, Microsoft Access MVP

 

[Daniel Lalonde]

Hi Steve,

Thanks again for your suggestions and your obvious
patience with us.

At the end of the day, the decision was to go with the
multiple reports as we needed to close this project. The
client didn't care how the reports were generated as long
as they were done right away.

I am still interested in figuring out how this could have
been handled better, to avoid a similar situation. So...

I tried your suggestion of using single query and
grouping/sorting in the report. This still did not yield
the results I needed because:

-The original supplied tables pretty much used number
field values (0,1,2) to indicate (NA,N,Y) for the data we
are reporting on. Fields Location, Event, Type and
EventDescription are all strings and date is obvious. If
I use the summary options for the reports, the default
setting SUMS the field values rather then providing a
COUNT of the number of Y records. I couldn't figure out
how to change the report SUM to COUNT and also have it
based on criteria (e.g. WHERE CPR = 2) so that it only
counts the total Ys.

-By far the larger problem was that I could not group the
field EventType and count the occurences of it in the
report. I was only able to sort the occurences. This was
the reason we had developed a second query. The end
result needed to display the top five EventType occurences
from each location. I will provide an example to
illustrate what the client wanted. I use a "/" to
indicate where page formatting (Spaces) would occur.

Location A
_________________________
//Report header section with results supplied by DCount
TotalEvents 42/3%ofTotal
TotalReponses 40/2.5%ofTotal
TotalCPR 5/0.1%ofTotal
..
..
..
__________________________
//Report detail section with results supplied by
//qryByLocationByEventType

EventTypeSummaryForLocationA
MotorVehicleAccident 17
Assault 12
Respiratory 2
..
..
..

So in this example, of the 42 events, 17 were from
MotorVehicleAccidents, 12 from Assaults, etc... This data
is from a one-to-many relationship table between tblData
and tblEventType. If I used the report wizard with a
single query as you suggested, it would not provide the
grouping for the second half of the report. Instead, it
would sort and return every EventType.

As requested, I will give you the SQL from the queries to
help facilitate understanding.

Query1----------------
SELECT tblData.Location, tblData.Date, tblData.Event,
tblData.Episode, tblData.Arrest, tblData.Activation,
tblData.Volunteer, tblData].CPR, tblData.AED,
tblData.Type, tblEventType.EventDescription, tblData.Acute
FROM tblEventType INNER JOIN tblData ON
tblEventType.EventType = tblData.EventType
WHERE (((tblData.Location)="Location A") AND tblData.Date)
Between #1/1/2002# And #4/30/2003#));

Query2----------------
SELECT TOP 5 [qryByLocationA].[EventDescription], Count
([qryByLocationA].[EventDescription]) AS
CountOfEventDescription
FROM [qryByLocationA]
GROUP BY [qryByLocationA].[EventDescription]
ORDER BY Count([qryByLocationA].[EventDescription]) DESC;

I most definately appreciate your suggestions for this. I
hope that I have provided you with enough detail to
understand exactly what we have been trying to
accomplish. Do you still think that one query and the
report's grouping/sorting functions could supply the same
results?

Daniel