consolidating reports

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

Guest

I have multiple reports showing the details of underlying fields. For
example, I have twelve reports that break out my data by month. (Each report
points to its specific query where the data is actually filtered from the
master table.) Each report sums two fields- estimated cost and actual cost.

How do I create a separate report (a consolidation) that shows me just the
sum totals for the two fields for each of the months?

Thanks in advance.
 
wesley weise said:
I have multiple reports showing the details of underlying fields. For
example, I have twelve reports that break out my data by month. (Each report
points to its specific query where the data is actually filtered from the
master table.) Each report sums two fields- estimated cost and actual cost.

How do I create a separate report (a consolidation) that shows me just the
sum totals for the two fields for each of the months?
Click to expand...


Not clear which 12 months you want to report here. Maybe
this query can be used for the summary report:

SELECT Format(transdate, "yyyymm"),
Sum(estimate), Sum(cost)
FROM mastertable
GROUP BY Format(transdate, "yyyymm")
WHERE transdate Between DateAdd("yyyy", -1, Date())
And Date()

I think that will calculate all the values you want and the
report should then be trivial.
 
I still don't understand- I happen to be a novice at Access. Are you saying
to do away with the report and use a query instead? The reason I was using a
report was that I need some way to display and show the numberss (along with
other information- the details behind the numbers). I was hoping that I could
simply create a new report and point to each value in the monthly reports.
Besides showing the estimated and actual sums by month, I also need to show
them for coordinator, type, and target audience. The individual reports for
each category are already built. I would now like to bring each of the sum
values into one consolidated report. How?

Thanks much.
 
Here might be a better way to ask the question. I have twelve reports. Each
report has its own query where the data is filtered. Each report shows the
formatted data along with a couple sums (basically adding up the cost
(estimated and actual) per month =Sum([Cost (est)]) and =Sum([Cost
(actual)]). Since each of the 12 reports, in addition to having the sums,
also has the details- I want to create a consolidated report. How can I
create another report and bring those 24 sums from the 12 other reports into
this one easy to see page?

Thank you very much in advance!!
 
PMFJI,
If your 12 reports are basically the same with the only difference being the
records returned in the Record Source, then you should consider using only
one report and filtering the Record Source based on a parameter prompt in
the query or a criteria based on a value entered into a control on a form.

This solution would also allow you to use the same report for your summary.
You might need to just hide the detail section so that only the totals
displayed.

--
Duane Hookom
MS Access MVP
--


wesley weise said:
Here might be a better way to ask the question. I have twelve reports.
Each
report has its own query where the data is filtered. Each report shows the
formatted data along with a couple sums (basically adding up the cost
(estimated and actual) per month =Sum([Cost (est)]) and =Sum([Cost
(actual)]). Since each of the 12 reports, in addition to having the sums,
also has the details- I want to create a consolidated report. How can I
create another report and bring those 24 sums from the 12 other reports
into
this one easy to see page?

Thank you very much in advance!!

wesley weise said:
I still don't understand- I happen to be a novice at Access. Are you
saying
to do away with the report and use a query instead? The reason I was
using a
report was that I need some way to display and show the numberss (along
with
other information- the details behind the numbers). I was hoping that I
could
simply create a new report and point to each value in the monthly
reports.
Besides showing the estimated and actual sums by month, I also need to
show
them for coordinator, type, and target audience. The individual reports
for
each category are already built. I would now like to bring each of the
sum
values into one consolidated report. How?

Thanks much.
Click to expand...
Click to expand...
 
I had thought of that solution when I was first creating the database.
However, because so many different people are going to be accessing the file
and pulling off different reports, I thought it would be much easier if all
the reporting was already set up for them. They just need to open and print.
In saying that, is there a way to create the summary report and point to the
caluculations in the detailed reports?
Thanks

Duane Hookom said:
PMFJI,
If your 12 reports are basically the same with the only difference being the
records returned in the Record Source, then you should consider using only
one report and filtering the Record Source based on a parameter prompt in
the query or a criteria based on a value entered into a control on a form.

This solution would also allow you to use the same report for your summary.
You might need to just hide the detail section so that only the totals
displayed.

--
Duane Hookom
MS Access MVP
--


wesley weise said:
Here might be a better way to ask the question. I have twelve reports.
Each
report has its own query where the data is filtered. Each report shows the
formatted data along with a couple sums (basically adding up the cost
(estimated and actual) per month =Sum([Cost (est)]) and =Sum([Cost
(actual)]). Since each of the 12 reports, in addition to having the sums,
also has the details- I want to create a consolidated report. How can I
create another report and bring those 24 sums from the 12 other reports
into
this one easy to see page?

Thank you very much in advance!!

wesley weise said:
I still don't understand- I happen to be a novice at Access. Are you
saying
to do away with the report and use a query instead? The reason I was
using a
report was that I need some way to display and show the numberss (along
with
other information- the details behind the numbers). I was hoping that I
could
simply create a new report and point to each value in the monthly
reports.
Besides showing the estimated and actual sums by month, I also need to
show
them for coordinator, type, and target audience. The individual reports
for
each category are already built. I would now like to bring each of the
sum
values into one consolidated report. How?

Thanks much.

:


I have multiple reports showing the details of underlying fields. For
example, I have twelve reports that break out my data by month. (Each
report
points to its specific query where the data is actually filtered from
the
master table.) Each report sums two fields- estimated cost and actual
cost.

How do I create a separate report (a consolidation) that shows me just
the
sum totals for the two fields for each of the months?


Not clear which 12 months you want to report here. Maybe
this query can be used for the summary report:

SELECT Format(transdate, "yyyymm"),
Sum(estimate), Sum(cost)
FROM mastertable
GROUP BY Format(transdate, "yyyymm")
WHERE transdate Between DateAdd("yyyy", -1, Date())
And Date()

I think that will calculate all the values you want and the
report should then be trivial.
Click to expand...
Click to expand...
Click to expand...
 
Your hope of having the summary report point to values in
other reports is not really a viable approach.

No, I am not suggesting you do away with the report. I was
suggesting a query that you could use as the report's record
source. From what you explained so far, I think a report
based on my suggested query should provide the numbers you
want, but you just mentioned "along with other information",
so I can't be sure.
 
As long as you are creating lots of reports, just create a single report
based on a totals query.

You might want to kick your applications up a notch and not even allow users
to see the database window. You can use forms for navigation, editing data,
selecting and running reports,....

--
Duane Hookom
MS Access MVP


wesley weise said:
I had thought of that solution when I was first creating the database.
However, because so many different people are going to be accessing the
file
and pulling off different reports, I thought it would be much easier if
all
the reporting was already set up for them. They just need to open and
print.
In saying that, is there a way to create the summary report and point to
the
caluculations in the detailed reports?
Thanks

Duane Hookom said:
PMFJI,
If your 12 reports are basically the same with the only difference being
the
records returned in the Record Source, then you should consider using
only
one report and filtering the Record Source based on a parameter prompt in
the query or a criteria based on a value entered into a control on a
form.

This solution would also allow you to use the same report for your
summary.
You might need to just hide the detail section so that only the totals
displayed.

--
Duane Hookom
MS Access MVP
--


wesley weise said:
Here might be a better way to ask the question. I have twelve reports.
Each
report has its own query where the data is filtered. Each report shows
the
formatted data along with a couple sums (basically adding up the cost
(estimated and actual) per month =Sum([Cost (est)]) and =Sum([Cost
(actual)]). Since each of the 12 reports, in addition to having the
sums,
also has the details- I want to create a consolidated report. How can I
create another report and bring those 24 sums from the 12 other reports
into
this one easy to see page?

Thank you very much in advance!!

:

I still don't understand- I happen to be a novice at Access. Are you
saying
to do away with the report and use a query instead? The reason I was
using a
report was that I need some way to display and show the numberss
(along
with
other information- the details behind the numbers). I was hoping that
I
could
simply create a new report and point to each value in the monthly
reports.
Besides showing the estimated and actual sums by month, I also need to
show
them for coordinator, type, and target audience. The individual
reports
for
each category are already built. I would now like to bring each of the
sum
values into one consolidated report. How?

Thanks much.

:


I have multiple reports showing the details of underlying fields.
For
example, I have twelve reports that break out my data by month.
(Each
report
points to its specific query where the data is actually filtered
from
the
master table.) Each report sums two fields- estimated cost and
actual
cost.

How do I create a separate report (a consolidation) that shows me
just
the
sum totals for the two fields for each of the months?


Not clear which 12 months you want to report here. Maybe
this query can be used for the summary report:

SELECT Format(transdate, "yyyymm"),
Sum(estimate), Sum(cost)
FROM mastertable
GROUP BY Format(transdate, "yyyymm")
WHERE transdate Between DateAdd("yyyy", -1, Date())
And Date()

I think that will calculate all the values you want and the
report should then be trivial.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top