Need report help! repeating data....

G

Guest

Now that I've created my tables and forms to record the data, I'm trying to
build a report to view some information, and also some summary figures about
that information. But I'm running into problems.

First, I made a query pulling the fields I need to display from the various
tables. Then I designed a report around this query, using the wizard and then
modifying the design as needed.

The problem(and there may be multiple problems) is that when I view the
report, it repeats a project over and over again based on the number of
payments or accomplishments that project has. So, if I made 2 payments for 1
project, when I look at the report, it repeats that project 2 times to show
those payments.

How do you set up a report to show the multiple payments, or multiple
accomplishments without repeating the entire project multiple times? Because
in the end I want to be able to total the payments, and total the
accomplishments, to create summaries of the amount spent and the work
accomplished?

Thanks for any advice in advance.

-Justin
 
D

Duane Hookom

I expect you might have used a subform on your project form to record
payments. You should use a subreport to display the payments associated with
each project. Make sure you remove the payments table from your main
report's record source.
 
G

Guest

Ok, here's how I'd like my report to look.

[FFY] Summary Report
-Grant Info here-
-Project Info here- (there can be multiple projects for each grant)
-Accomplishments here- (there can be multiple accomplishments for
each project)

I also need to be able to total the accomplishments for all projects under a
grant, as well as total the payments and matching payments made for all the
projects under a grant.
 
D

Duane Hookom

I expect your report is grouped on FFY, Grant, Project, Accomplishments with
each adjacent table being related one to many (left to right).

You shouldn't require subreports if this is how your tables are set up.

--
Duane Hookom
MS Access MVP


justin said:
Ok, here's how I'd like my report to look.

[FFY] Summary Report
-Grant Info here-
-Project Info here- (there can be multiple projects for each grant)
-Accomplishments here- (there can be multiple accomplishments for
each project)

I also need to be able to total the accomplishments for all projects under
a
grant, as well as total the payments and matching payments made for all
the
projects under a grant.

--
I'm not an MVP.


justin said:
Now that I've created my tables and forms to record the data, I'm trying
to
build a report to view some information, and also some summary figures
about
that information. But I'm running into problems.

First, I made a query pulling the fields I need to display from the
various
tables. Then I designed a report around this query, using the wizard and
then
modifying the design as needed.

The problem(and there may be multiple problems) is that when I view the
report, it repeats a project over and over again based on the number of
payments or accomplishments that project has. So, if I made 2 payments
for 1
project, when I look at the report, it repeats that project 2 times to
show
those payments.

How do you set up a report to show the multiple payments, or multiple
accomplishments without repeating the entire project multiple times?
Because
in the end I want to be able to total the payments, and total the
accomplishments, to create summaries of the amount spent and the work
accomplished?

Thanks for any advice in advance.

-Justin
 
G

Guest

I tried setting it up that way, but it shows duplicate accomplishments and it
also doesn't display some of the projects. They just aren't there?

I also have a report setup the other way you suggested, with the subreport,
and that is kind of working. At least, it's working better than this one I
just created.

How do you total fields from different reports? What I mean is, I want to
add up the totals I have in the subreport and display them on the main
report. Is that possible?

For example, lets say there are 2 projects under 1 grant. Each project has 2
payments with it. For each project I have a totals field, that calculates the
projects payments. Now I want to total those totals fields and display them
at the bottom of each grant.

Does that make sense? If you'd like I can write out how I have my tables set
up, if that helps?
--
I''m not an MVP.


Duane Hookom said:
I expect your report is grouped on FFY, Grant, Project, Accomplishments with
each adjacent table being related one to many (left to right).

You shouldn't require subreports if this is how your tables are set up.

--
Duane Hookom
MS Access MVP


justin said:
Ok, here's how I'd like my report to look.

[FFY] Summary Report
-Grant Info here-
-Project Info here- (there can be multiple projects for each grant)
-Accomplishments here- (there can be multiple accomplishments for
each project)

I also need to be able to total the accomplishments for all projects under
a
grant, as well as total the payments and matching payments made for all
the
projects under a grant.

--
I'm not an MVP.


justin said:
Now that I've created my tables and forms to record the data, I'm trying
to
build a report to view some information, and also some summary figures
about
that information. But I'm running into problems.

First, I made a query pulling the fields I need to display from the
various
tables. Then I designed a report around this query, using the wizard and
then
modifying the design as needed.

The problem(and there may be multiple problems) is that when I view the
report, it repeats a project over and over again based on the number of
payments or accomplishments that project has. So, if I made 2 payments
for 1
project, when I look at the report, it repeats that project 2 times to
show
those payments.

How do you set up a report to show the multiple payments, or multiple
accomplishments without repeating the entire project multiple times?
Because
in the end I want to be able to total the payments, and total the
accomplishments, to create summaries of the amount spent and the work
accomplished?

Thanks for any advice in advance.

-Justin
 
D

Duane Hookom

The accomplishments should not repeat if they are each linked to only a
single project and each project is linked to a single Grant and each Grant
is linked to a single FFY.

If some projects aren't displayed, change your report's record source to a
query with joins that include all records from the Projects table.

You can grab a total from a subreport by first creating a total in the
subreport's footer section. Then add a text box to the same section of the
main report with a control source like:
=IIf(subrptControlA.Report.HasData, subrptControlA.Report!txtTotal, 0)

Whoa... where did the payments come from?
You previously stated
===============
[FFY] Summary Report
-Grant Info here-
-Project Info here- (there can be multiple projects for each grant)
-Accomplishments here- (there can be multiple accomplishments for
each project)
==============

Payments should be either combined into a single total per grant/project or
displayed in a subreport.

--
Duane Hookom
MS Access MVP
--

justin said:
I tried setting it up that way, but it shows duplicate accomplishments and
it
also doesn't display some of the projects. They just aren't there?

I also have a report setup the other way you suggested, with the
subreport,
and that is kind of working. At least, it's working better than this one I
just created.

How do you total fields from different reports? What I mean is, I want to
add up the totals I have in the subreport and display them on the main
report. Is that possible?

For example, lets say there are 2 projects under 1 grant. Each project has
2
payments with it. For each project I have a totals field, that calculates
the
projects payments. Now I want to total those totals fields and display
them
at the bottom of each grant.

Does that make sense? If you'd like I can write out how I have my tables
set
up, if that helps?
--
I''m not an MVP.


Duane Hookom said:
I expect your report is grouped on FFY, Grant, Project, Accomplishments
with
each adjacent table being related one to many (left to right).

You shouldn't require subreports if this is how your tables are set up.

--
Duane Hookom
MS Access MVP


justin said:
Ok, here's how I'd like my report to look.

[FFY] Summary Report
-Grant Info here-
-Project Info here- (there can be multiple projects for each
grant)
-Accomplishments here- (there can be multiple accomplishments
for
each project)

I also need to be able to total the accomplishments for all projects
under
a
grant, as well as total the payments and matching payments made for all
the
projects under a grant.

--
I'm not an MVP.


:

Now that I've created my tables and forms to record the data, I'm
trying
to
build a report to view some information, and also some summary figures
about
that information. But I'm running into problems.

First, I made a query pulling the fields I need to display from the
various
tables. Then I designed a report around this query, using the wizard
and
then
modifying the design as needed.

The problem(and there may be multiple problems) is that when I view
the
report, it repeats a project over and over again based on the number
of
payments or accomplishments that project has. So, if I made 2 payments
for 1
project, when I look at the report, it repeats that project 2 times to
show
those payments.

How do you set up a report to show the multiple payments, or multiple
accomplishments without repeating the entire project multiple times?
Because
in the end I want to be able to total the payments, and total the
accomplishments, to create summaries of the amount spent and the work
accomplished?

Thanks for any advice in advance.

-Justin
 
G

Guest

Duane Hookom said:
Whoa... where did the payments come from?

Sorry about that, let me clarify how my tables are set up.

GrantInfoTbl (1 to many relationship with ProjectInfoTbl)
GrantID
Account#
Grant#
FFY
GrantName
GrantAwarded

ProjectInfoTbl
GrantID
ProjectID
Project#
ProjectName
Awarded
AdditionalFundsRequested

PaymentsTbl (many to 1 relationship with ProjectInfoTbl)
ProjectID
PaymentID
Payment
Match

ProjectAccomplishmentsTbl (many to 1 relationship with ProjectInfoTbl)
ProjectID
ActivityID
Amount
Description

ActivityTbl (1 to many relationship with the ProjectAccomplishmentsTbl)
ActivityID
ActivityText

Ok, so those are the tables and the fields being used in the reports. There
are some other tables, but I don't need them in the report, and some of these
tables have other fields, but again, they aren't needed in the final reports.

Ok, so here is how I have my current report set up. This one seems to be
working the most like I intend this report to work.
I made the query a select query for the FFY field. So when user opens up the
report they are asked for the year they'd like to see.
Then it is grouped by Grants (I have account#, Grant#, GrantName, GrantAward
all in a header)
Then it is grouped by Projects (I have Project#, ProjectName, ProjectAward,
and I think I will add AdditionalFundsRequested here, all in the next header)

Then in the detail section, I have two subreports.

One of them is a Payments subreport (with payment and Match in the detail
section, and text boxes in the report footer with the following:
=Sum([Payment]) in one box, =Sum([Match]) in another box, and
=Sum([Payment]+[Match]) in the final box labeled Total Project Amount.)

The other subreport is Accomplishments subreport and it has ActivityText,
Amount, and Description in it's Detail section.

And with everything set up like this, it works fine, but it's not entirely
what I need to show. Like I mentioned previously I need to be able to total
all the payments made for projects under a single grant, and also total all
the accomplishments made by projects under a single grant. I'll try working
with that IIf statement you posted, see if that helps me out, but maybe now
that I've explained things a little clearer, there may be something else
needed or a different setup?

Anyway, thanks for the help. I'm supposed to be done with this project in
the next few days, and I couldn't be anywhere without the help of this
discussion group.

-Justin
 
D

Duane Hookom

To get the " total all the payments made for projects under a single grant":
- Create a totals query that joins the ProjectInfo table
with the Payments table.
- Group By GrantID and total Payment and Match.
- Add this query to your report's record source query
and join the GrantID fields.
You can now add the SumOfPayment and SumOfMatch to your report without
worrying about pulling totals from subreports.

--
Duane Hookom
MS Access MVP
--

justin said:
Duane Hookom said:
Whoa... where did the payments come from?

Sorry about that, let me clarify how my tables are set up.

GrantInfoTbl (1 to many relationship with ProjectInfoTbl)
GrantID
Account#
Grant#
FFY
GrantName
GrantAwarded

ProjectInfoTbl
GrantID
ProjectID
Project#
ProjectName
Awarded
AdditionalFundsRequested

PaymentsTbl (many to 1 relationship with ProjectInfoTbl)
ProjectID
PaymentID
Payment
Match

ProjectAccomplishmentsTbl (many to 1 relationship with ProjectInfoTbl)
ProjectID
ActivityID
Amount
Description

ActivityTbl (1 to many relationship with the ProjectAccomplishmentsTbl)
ActivityID
ActivityText

Ok, so those are the tables and the fields being used in the reports.
There
are some other tables, but I don't need them in the report, and some of
these
tables have other fields, but again, they aren't needed in the final
reports.

Ok, so here is how I have my current report set up. This one seems to be
working the most like I intend this report to work.
I made the query a select query for the FFY field. So when user opens up
the
report they are asked for the year they'd like to see.
Then it is grouped by Grants (I have account#, Grant#, GrantName,
GrantAward
all in a header)
Then it is grouped by Projects (I have Project#, ProjectName,
ProjectAward,
and I think I will add AdditionalFundsRequested here, all in the next
header)

Then in the detail section, I have two subreports.

One of them is a Payments subreport (with payment and Match in the detail
section, and text boxes in the report footer with the following:
=Sum([Payment]) in one box, =Sum([Match]) in another box, and
=Sum([Payment]+[Match]) in the final box labeled Total Project Amount.)

The other subreport is Accomplishments subreport and it has ActivityText,
Amount, and Description in it's Detail section.

And with everything set up like this, it works fine, but it's not entirely
what I need to show. Like I mentioned previously I need to be able to
total
all the payments made for projects under a single grant, and also total
all
the accomplishments made by projects under a single grant. I'll try
working
with that IIf statement you posted, see if that helps me out, but maybe
now
that I've explained things a little clearer, there may be something else
needed or a different setup?

Anyway, thanks for the help. I'm supposed to be done with this project in
the next few days, and I couldn't be anywhere without the help of this
discussion group.

-Justin
 
G

Guest

What do you mean by "Add this query to your report's record source query and
join the GrantID fields."

How do you add a query when there already is one for the record source?
 
G

Guest

Duane, disregard my last question, I figured it out. Thanks.

New question. How can I do this totals set up for my Accomplishments? I need
to be able to see the total acres for all projects under a grant, along with
totals for all the other possible accomplishments. Thanks.

-Justin
 
D

Duane Hookom

"total acres"? Are we supposed to understand where this comes from?

I suggest you apply the same method I suggested for the payments.
 
G

Guest

Duane Hookom said:
I suggest you apply the same method I suggested for the payments.

I've tried this, and it doesn't work. It repeats the projects multiple
times, and doesn't work.
"total acres"? Are we supposed to understand where this comes from?

Sorry, that comes from the ActivityTbl, ActivityText field. The ActivityText
field has the following rows; TotalAcres, DS, Thin, FuelBreaks, Slash, Prun,
RxBurn, Plans&Assessment, I&E, Other.

And what I need to appear on the report is something like:

TotalAcres DS Thin Prun
10 5 3 2

where that would represent the total amount accomplished of all projects in
a grant

Thanks

-Justin
 
D

Duane Hookom

What is the SQL view of "I've tried this, and it doesn't work. It repeats
the projects multiple times, and doesn't work."?

Do you understand crosstab queries?
Are you set on displaying the TotalAcres, DS, Thin,... across the report
rather and down?
 
G

Guest

Duane Hookom said:
What is the SQL view of "I've tried this, and it doesn't work. It repeats
the projects multiple times, and doesn't work."?

Here's the SQL of that query I built:

SELECT ProjectInfoTbl.GrantID, ProjectAccomplishmentsTbl.ActivityID,
Sum(ProjectAccomplishmentsTbl.Amount) AS SumOfAmount,
ProjectAccomplishmentsTbl.Description
FROM ProjectInfoTbl INNER JOIN (ActivityTbl INNER JOIN
ProjectAccomplishmentsTbl ON ActivityTbl.ActivityID =
ProjectAccomplishmentsTbl.ActivityID) ON ProjectInfoTbl.ProjectID =
ProjectAccomplishmentsTbl.ProjectID
GROUP BY ProjectInfoTbl.GrantID, ProjectAccomplishmentsTbl.ActivityID,
ProjectAccomplishmentsTbl.Description;
Do you understand crosstab queries?

I was actually just reading about crosstab queries before I checked the
discussion group. Yes, I kind of understand them. I haven't worked with them
before, only read about them.
Are you set on displaying the TotalAcres, DS, Thin,... across the report
rather and down?

No, I was thinking of it that way as a means to save some space (and paper)
on the reports.
 
D

Duane Hookom

You need to create one record per GrantID which can be done with a crosstab.

If your ActivityIDs are static, you could create a crosstab as per your SQL
view with GrantID as the Row Heading, ActivityID as the Column Heading, and
Sum(Amount) as the Value. Set the Column Headings property to every possible
ActivityID value like:
Column Headings: TotAcres, DS, Thin,....
 
G

Guest

Ok, I turned that query into a crosstab and put in the column headings like
you suggested, but when I try to run the query I get : error 3464 Data type
mismatch in criteria expression. What does that mean?
 
D

Duane Hookom

It means you need to post your SQL view to the NG and provide the data types
of your columns...
 
G

Guest

Here's the SQL view:

TRANSFORM Sum(ProjectAccomplishmentsTbl.Amount) AS SumOfAmount
SELECT ProjectInfoTbl.GrantID
FROM ProjectInfoTbl INNER JOIN (ActivityTbl INNER JOIN
ProjectAccomplishmentsTbl ON ActivityTbl.ActivityID =
ProjectAccomplishmentsTbl.ActivityID) ON ProjectInfoTbl.ProjectID =
ProjectAccomplishmentsTbl.ProjectID
GROUP BY ProjectInfoTbl.GrantID, ProjectAccomplishmentsTbl.Description
PIVOT ProjectAccomplishmentsTbl.ActivityID In ("Total
Acres","DS","Thin","Fuel
Breaks","Slash","Prun","RxBurn","Plans&Assessment","I&E","Other");

"to the NG" what does that mean?

GrantID is a autonumber type, Description is a memo type, ActivityID is an
autonumber type, and Amount is a number field. Is this what you're asking for?
 
G

Guest

the query works if I remove the column headings part, but then it just
displays the activity number as the heading instead of the name of the
activity.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top