Calculations in reports

echorley

Registered User.
Local time
Today, 16:43
Joined
Mar 11, 2003
Messages
131
I have tried to gather information for reports using calculations in queries, but have not had a lot of luck. So I tried to do most of the calculations in a report using a most basic query. This is a lot easier, but there is just one snag:

Here is some sample data my simple query generates.

QB QBName RunorPass PassStatus YardsGained
3 Fuller 2 complete 5
3 Fuller 2 incomplete 0
3 Fuller 2 complete 15
3 Fuller 2 incomplete 0
3 Fuller 2 incomplete 0
3 Fuller 2 complete 8
3 Fuller 2 complete 8

The calculations in my report (using mostly DSum and DCount) works great because there is only one quarterback.

However, If I add a different quarterback to the mix, the query shows the data, but the report does not differentiate between the two quarterbacks and I just get the counts and sums of the columns with no regard to the quarterback.

Here is the SQL that generates the query:

SELECT [Offense Game Input Table].Quarterback, [Offense Game Input Table].QBLastName, [Offense Game Input Table].[RunorPass], [Offense Game Input Table].[PassStatus], [Offense Game Input Table].[YardsGained], [Offense Game Input Table].Score, [Offense Game Input Table].Opponent
FROM [Offense Game Input Table]
WHERE ((([Offense Game Input Table].Quarterback) Is Not Null) AND (([Offense Game Input Table].QBLastName) Is Not Null) AND (([Offense Game Input Table].[RunorPass])=2) AND (([Offense Game Input Table].[PassStatus]) Is Not Null) AND (([Offense Game Input Table].Opponent)=[Forms]![Statistics]![Opponent]));

I had some luck (with a lot of help) after another post where I generated the data I needed with calculations on a query, but still could not get exactly what I need (two sets of calcuations for 2 quarterbacks, or 3 sets of calculations for 3 quarterbacks, etc).

Pat H. was helping with the calculation on the query and if that ends up working out, I will go back to it. However, using reports for all the calculations is very easy for me.

Thanks!
 
An easy way to do this is to add grouping levels into your report. In this case, group by Quarterback Name
 
Tried that

While the stats for each QB showup, the are the total overall statistics. I need individual totals for each quarterback.

Here is what I use in the control box in the report to find total yards passing:

=NZ(Sum([Passing Yards Gained]),0)

This gives the total of all yards passing, not for each quarteback. How can I tell the report that I want the sum of passing yards for each individual quarterback?
 
In the grouping options, you can sum/count or any other aggregate function on each grouping level as well as overall, you just need to add a sum function to the group footer

EG Group Header
Name of QB

Detail
Data related to that QB

Group Footer
textbox with controlsource =NZ(Sum([Passing Yards Gained]),0)

When you put this in the report footer, you will get overall stats.
 

Users who are viewing this thread

Back
Top Bottom