You got it at the end

[echorley]

If one quarterback throws 2 interceptions during a game and another throws 1 interception during the same game, I want my report to indicate that.

I did try a different approach. Rather than doing the calculations in a query, and then having to tie different queries together, I did a simple query to gather the needed information from one table and then did the calculations in the report itself. This was a lot easier for many other reports I need to make.

However, there is duplicate text on muliple pages (scattered randomly) and I cannot get the reports to distinguish between the two quarterbacks.

Thanks for your help, again and again!

 

[jfgambit]

How are your tables set-up? What are the fields?

I have to assume that this can be completed with a simple Totals query. Grouped by PlayerType (Quaterback) and a Count of Interceptions.

Can you provide a little more detail on the table structure?

 

[echorley]

Here is my setup

I have a table that holds all of my offense information. The fields in use to count the number of completions, incompletions and interceptions are:

Opponent
Quarterback
Pass Play Status

Pass Play Status is a text field that can be null, "Complete", "Incomplete" or "Interception" It is null when there is a running play.

I keep the quarterback seperate from running backs, wide receivers, etc.

Here is the suggestion Pat H. supplied:

SELECT [Offense Game Input Table].Quarterback, Count(*) AS CountOfIncomplete
FROM [Offense Game Input Table]
WHERE [Offense Game Input Table].[Pass Play Status]="Incomplete"
Group By [Offense Game Input Table].Quarterback;

But that does not give stats for each individual quarterback (she acknowledged that.)

The problem is when there are no interceptions in the query. Then a null value is returned for everything. I have tried the Nz function but to no avail.

Very simply, I want to be able to COUNT completions, incompletions and interceptions for EACH quarterback with the possibility that there might be nulls to ignore and return a value of zero.



---
To go a different direction, I went ahead and created a very simple query that holds the three field above with the Opponent having the only restriction. I made a report based on this query and started to create text boxes. This works well for a lot of stats I need, except for the following:

Here is an example:

To find the number of completions I used a DCount:

=Nz(DCount("[Pass Play Status]","qryIndPass"," [Pass Play Status] = 'Complete' "),0)

This does give the correct number of completions and returns a value of zero if there are no completions, but it will no do it for individual quarterbacks in the report. I set up the grouping to group by quarterbacks, but the report shows the same statistics for each quarterback. That is, the expression above used the whole query to calculate the number of completions, rather than calculate them by quarterback.

However, this expression did give each quarterback the correct individual statistic for the average yards per completion:

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

I am assuming this is a difference between domain aggregate functions and aggregrate functions. Or I could have a grouping error, but I tried every permutation possible with the grouping options.

So I am at a loss here. This is really the final hurdle I need to finish creating the individual player reports.

 

[echorley]

Yes that works!

and so does this which you posted elsewhere:

Sum(IIf([Pass Play Status]="Interception",1,0))

used in the control box in the report gives individual statistics and gives 0 for when there are no interceptions. Both of which can be attributed to you. You have no idea how many places this expression can be used. Thanks again!