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!
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!