Need report help! repeating data....

J

justin

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

justin

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

justin

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

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

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

justin

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?
 
J

justin

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

justin

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

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

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?
 
J

justin

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

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

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

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

justin

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

justin

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?
 
J

justin

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

Duane Hookom

Are you sure you want to group on ProjectAccomplishmentsTbl.Description? If
it is a memo field, I can't imagine why you would even try to do that.

Either PIVOT a description field or place the numeric ActivityID values in
the Column Headings property.
 
J

justin

I don't want to group on ProjectAccomplishmentsTbl.Description. What I want
to do is display the discription field with the totals. Some of the
accomplishments have a discription that goes with them. For example, the
accomplishment Plans&Assessment, is more than just a number, there is a
discription that goes with it saying, "distributed x amount of articles" or
something like that. So I need that to show up too.

I put the numeric activityID values into teh column headings property and
now things look better. I assume I can just change the label for those on the
report to read the actual name of the activity and not just the ID number?

What do you mean by "PIVOT a description field"?

Thanks.

-Justin
 
Top