Calculations in reports

[echorley]

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!

 

[Fizzio]

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

 

[echorley]

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?

 

[Fizzio]

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.