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
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 eventl. 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.
SueB
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
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 eventl. 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.
SueB
