Report Group Footer not displaying the total that I want

[SueB]

Greetings. I have a report based on the following query (hang in there
... it's quite long):

SELECT Year([tblEvents].[eventstartdt]) AS Yr, tblEvents.eventID,
tblEvents.eventname, tblEvents.eventhost, tblEvents.eventcity,
tblEvents.eventstate, tblEvents.eventstartdt, tblEvents.eventenddt,
tblTrials.trialnbr, tblTrials.trialdt, tblTrialClass.trialclassID,
tblClasses.class, tblScores.score, tblScores.level, [tblPeople].[fname]
& (" "+[tblPeople].[midinit]+".") & " " & [tblPeople].[lname] & ("
"+[tblPeople].[suffix]+".") & " (" & [tblPeople].[city] & ", " &
[tblPeople].[state] & ")" AS judge, tblScores.qualified
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
tblTrials.eventID) INNER JOIN (((tblPeople INNER JOIN tblJudges ON
tblPeople.peopleID = tblJudges.peopleID) INNER JOIN (tblClasses INNER
JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) ON
tblJudges.judgeID = tblTrialClass.judgeID) INNER JOIN tblScores ON
tblTrialClass.trialclassID = tblScores.trialclassID) ON
tblTrials.trialID = tblTrialClass.trialID
ORDER BY Year([tblEvents].[eventstartdt]), tblEvents.eventstartdt,
tblTrials.trialnbr, tblClasses.classID, tblScores.level;
The report is sorted (ascending) and grouped by:
Yr
eventstartdt
eventname
trialnbr
class

There can be many events in a yr
there can be many trials in an event
there can be three classes in a trial

I want to display the following in the Yr Footer (year end totals):

# of Events
# of Trials (total)
Avg # of Trials in an Event
Max # of Trials in an Event

The problem: each record in the recordset is basically a "score" that
was recorded for a dog competing in a trial which is in an event. So
there are many records with the same eventname/eventID. For my test data
the number of records is 32. When I used =Count([eventID]) for the
ControlSource of the textbox for "# of Events" (in the Yr Footer) the
result was 32 instead of 2 (there are 2 events in my test data).

What I really want is something like =Count(distinct [eventID]) but that
is not a valid construct. Any ideas how to get the results I want in
this report? Or do I need a different report (i.e.; a different query)?

Thanks.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Wayne Morgan]

One option is to use the built in running sum property (data tab) of a
textbox when it is on a report. To get a count, you can set the Control
Source of the textbox to =1 and set its Running Sum property to Over All or
Over Group. If you want to count only if the value of another field is a
certain value, you can use an IIf statement instead of =1, such as
=IIf([txtOtherTextbox]="Hello", 1, 0). This will count the number of records
that have Hello in txtOtherTextbox. In the group footer, place a textbox
with its Control Source pointing to the summing textbox in the detail
section

=txtMySummingTextbox

--
Wayne Morgan
MS Access MVP
"SueB" <sl*****@verizon.net> wrote in message
news:lN************@news.uswest.net...

Greetings. I have a report based on the following query (hang in there
.. it's quite long):

SELECT Year([tblEvents].[eventstartdt]) AS Yr, tblEvents.eventID,
tblEvents.eventname, tblEvents.eventhost, tblEvents.eventcity,
tblEvents.eventstate, tblEvents.eventstartdt, tblEvents.eventenddt,
tblTrials.trialnbr, tblTrials.trialdt, tblTrialClass.trialclassID,
tblClasses.class, tblScores.score, tblScores.level, [tblPeople].[fname]
& (" "+[tblPeople].[midinit]+".") & " " & [tblPeople].[lname] & ("
"+[tblPeople].[suffix]+".") & " (" & [tblPeople].[city] & ", " &
[tblPeople].[state] & ")" AS judge, tblScores.qualified
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
tblTrials.eventID) INNER JOIN (((tblPeople INNER JOIN tblJudges ON
tblPeople.peopleID = tblJudges.peopleID) INNER JOIN (tblClasses INNER
JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) ON
tblJudges.judgeID = tblTrialClass.judgeID) INNER JOIN tblScores ON
tblTrialClass.trialclassID = tblScores.trialclassID) ON
tblTrials.trialID = tblTrialClass.trialID
ORDER BY Year([tblEvents].[eventstartdt]), tblEvents.eventstartdt,
tblTrials.trialnbr, tblClasses.classID, tblScores.level;
The report is sorted (ascending) and grouped by:
Yr
eventstartdt
eventname
trialnbr
class

There can be many events in a yr
there can be many trials in an event
there can be three classes in a trial

I want to display the following in the Yr Footer (year end totals):

# of Events
# of Trials (total)
Avg # of Trials in an Event
Max # of Trials in an Event

The problem: each record in the recordset is basically a "score" that
was recorded for a dog competing in a trial which is in an event. So
there are many records with the same eventname/eventID. For my test data
the number of records is 32. When I used =Count([eventID]) for the
ControlSource of the textbox for "# of Events" (in the Yr Footer) the
result was 32 instead of 2 (there are 2 events in my test data).

What I really want is something like =Count(distinct [eventID]) but that
is not a valid construct. Any ideas how to get the results I want in
this report? Or do I need a different report (i.e.; a different query)?

Thanks.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[SueB]

Wayne,
Thanks for the suggestion. I did, however, try the Running Sum by
defining a textbox in the EventName group header section. I printed the
value in the Event header and the number printed correctly. However,
when I tried to reference that control by name in the Yr Footer Section,
it would not work.

I don't think I would want to put that counter field in the Detail
Section because it would count every record, wouldn't it. That would
give me 32 (the total number of score records).

And your other option ... =IIf([txtOtherTextbox]="Hello", 1, 0) ...
actually I don't think that this will work, because I don't know what
constant I would be comparing to. I want to count the number of events.
Their unique value is only known during processing (eventname or
eventID). If I could reference the other fields in the Yr Section
Footer that would be ok, but I can't.

Please correct my thinking if I am wrong. Obviously I am wrong about
something because I don't have the results I want. So...please fire
away. Thanks.

Sue

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Salad]

SueB wrote:

Greetings. I have a report based on the following query (hang in there
.. it's quite long):

SELECT Year([tblEvents].[eventstartdt]) AS Yr, tblEvents.eventID,
tblEvents.eventname, tblEvents.eventhost, tblEvents.eventcity,
tblEvents.eventstate, tblEvents.eventstartdt, tblEvents.eventenddt,
tblTrials.trialnbr, tblTrials.trialdt, tblTrialClass.trialclassID,
tblClasses.class, tblScores.score, tblScores.level, [tblPeople].[fname]
& (" "+[tblPeople].[midinit]+".") & " " & [tblPeople].[lname] & ("
"+[tblPeople].[suffix]+".") & " (" & [tblPeople].[city] & ", " &
[tblPeople].[state] & ")" AS judge, tblScores.qualified
FROM (tblEvents INNER JOIN tblTrials ON tblEvents.eventID =
tblTrials.eventID) INNER JOIN (((tblPeople INNER JOIN tblJudges ON
tblPeople.peopleID = tblJudges.peopleID) INNER JOIN (tblClasses INNER
JOIN tblTrialClass ON tblClasses.classID = tblTrialClass.classID) ON
tblJudges.judgeID = tblTrialClass.judgeID) INNER JOIN tblScores ON
tblTrialClass.trialclassID = tblScores.trialclassID) ON
tblTrials.trialID = tblTrialClass.trialID
ORDER BY Year([tblEvents].[eventstartdt]), tblEvents.eventstartdt,
tblTrials.trialnbr, tblClasses.classID, tblScores.level;
The report is sorted (ascending) and grouped by:
Yr
eventstartdt
eventname
trialnbr
class

There can be many events in a yr
there can be many trials in an event
there can be three classes in a trial

I want to display the following in the Yr Footer (year end totals):

# of Events
# of Trials (total)
Avg # of Trials in an Event
Max # of Trials in an Event

The problem: each record in the recordset is basically a "score" that
was recorded for a dog competing in a trial which is in an event. So
there are many records with the same eventname/eventID. For my test data
the number of records is 32. When I used =Count([eventID]) for the
ControlSource of the textbox for "# of Events" (in the Yr Footer) the
result was 32 instead of 2 (there are 2 events in my test data).

What I really want is something like =Count(distinct [eventID]) but that
is not a valid construct. Any ideas how to get the results I want in
this report? Or do I need a different report (i.e.; a different query)?

Thanks.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

What happens if you create a field called EventCounter with a control
source of =1 in the event header or footer. Then get a running sum of
EventCounter in the Yr footer.

[SueB]

Salad,

I must be missing something. Because I keep getting a popup dialog box
asking me for the parameter value for [eventcounter].

I put a textbox in Event Header Section. ControlSource=1 with
RunningSum=Over All (I also tried with RunningSum=Over Group). Then I
put a textbox in Yr Footer Section with
ControlSource=sum([eventcounter]). That did not work. Then I changed
the ControlSource to =sum(me![eventcounter]). That did not work. I
tried ControlSource=Count([eventcounter]) and =Count(me![eventcounter]).
They all did not work. That's because it does not recognize the
controlname.

Any other thoughts?
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Salad]

SueB wrote:

Salad,

I must be missing something. Because I keep getting a popup dialog box
asking me for the parameter value for [eventcounter].

I put a textbox in Event Header Section. ControlSource=1 with
RunningSum=Over All (I also tried with RunningSum=Over Group). Then I
put a textbox in Yr Footer Section with
ControlSource=sum([eventcounter]). That did not work. Then I changed
the ControlSource to =sum(me![eventcounter]). That did not work. I
tried ControlSource=Count([eventcounter]) and =Count(me![eventcounter]).
They all did not work. That's because it does not recognize the
controlname.

Any other thoughts?
Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

Hmmm. If I created a field called EventCounter and I made it a running
sum with a control source of =1 I might create a field called EventCount
with a control source of =[EventCounter] in the footer band.

[SueB]

Ok ... it's the simple approach that usually wins. THAT DID IT. Thanks
so much.

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***

[Wayne Morgan]

Is the control name and, if it's a bound control, its field name the same?
If so, when you try to reference that control by name you'll get a #Name
error. If that is the case, change the name of the control. If it's a
textbox, you can just put txtNameOfControl, just something to make it
different.

--
Wayne Morgan
MS Access MVP
"SueB" <sl*****@verizon.net> wrote in message
news:e_**************@news.uswest.net...

Wayne,
Thanks for the suggestion. I did, however, try the Running Sum by
defining a textbox in the EventName group header section. I printed the
value in the Event header and the number printed correctly. However,
when I tried to reference that control by name in the Yr Footer Section,
it would not work.

I don't think I would want to put that counter field in the Detail
Section because it would count every record, wouldn't it. That would
give me 32 (the total number of score records).

And your other option ... =IIf([txtOtherTextbox]="Hello", 1, 0) ...
actually I don't think that this will work, because I don't know what
constant I would be comparing to. I want to count the number of events.
Their unique value is only known during processing (eventname or
eventID). If I could reference the other fields in the Yr Section
Footer that would be ok, but I can't.

Please correct my thinking if I am wrong. Obviously I am wrong about
something because I don't have the results I want. So...please fire
away. Thanks.

Sue

Regards,
SueB

*** Sent via Developersdex http://www.developersdex.com ***