Greetings. I'm having trouble wrapping my head around this one.
The database is for a Dog Competition Organization. The basic structure of the data is:
Dogs compete in a Class.
Dogs get a Score for entry in a Class.
If the Score is above a certain threshold the score is designated as 'qualified' (or a Q).
A Judge judges a Class.
There are three Classes in a Trial.
There may be many Trials in an Event (let's say 2 trials on Saturday and 2 trials on Sunday)
[trialnbr] is a value assigned to each trial within an Event. The value starts at 1. So, "Event A" may have "Trial 1" and "Trial 2". "Event B" may have "Trial 1" and "Trial 2". [trialnbr] is NOT the trial record key, [trialID] is the autonumber record key.
I have a report based on the following (Ugly) query:
SELECT [tblPeople].[lname] & (" "+[tblPeople].[suffix]+".") & ", " & [tblPeople].[fname] & (" "+[tblPeople].[midinit]+".") & " - " & [tblPeople].[city] & ", " & [tblPeople].[state] AS judgeinfo, tblJudges.n_qualdt, tblJudges.o_qualdt, tblJudges.u_qualdt, tblJudges.n_licdt, tblJudges.o_licdt, tblJudges.u_licdt, Year([tblTrials].[trialdt]) AS Yr, [tblEvents].[eventname] & " (" & [tblEvents].[eventhost] & " - " & [tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] & ")" AS eventinfo, tblTrials.trialnbr, tblTrials.trialdt, tblClasses.classID, tblClasses.class, tblScores.score, 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 [tblPeople].[lname] & (" "+[tblPeople].[suffix]+".") & ", " & [tblPeople].[fname] & (" "+[tblPeople].[midinit]+".") & " - " & [tblPeople].[city] & ", " & [tblPeople].[state], Year([tblTrials].[trialdt]), [tblEvents].[eventname] & " (" & [tblEvents].[eventhost] & " - " & [tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] & ")", tblTrials.trialnbr, tblClasses.classID;
I have the 'Sorting and Grouping' setup as follows:
Yr (ascending)
judgeinfo (ascending)
eventinfo (ascending)
trialnbr (ascending)
classid (ascending)
I want to list all Events per Judge. Then for each event list all Trials. Then for each Trial list all Classes that the judge judged and some statistics from each Class (# of entries, # of Qs, Q Rate).
Then I want to total up the number of Events, Trials, and Classes for each Judge. That's the part that is not working quite right. I've attached a sample TEST report (using test data). The first page judge totals are correct. However, after the 1st judge the number of classes doesn't reset and start recounting.
WOW, this is getting to be long winded.
I get the Judges Class Total by doing the following:
1. Defined a textbox [classcounter] in the [classID] footer section
2. [classcounter].ControlSource = 1
3. [classcounter].runningsum = Over All (it, also, doesn't work if = Over Group)
4. Defined textbox in [judgeinfo] footer section with ControlSource = [classcounter]
I tried something similar, in a prior version of the report - before I gave up, to get [trialcounter] for the judge, but it wouldn't work, either. I put the [trialcounter] in the [trialnbr] header section with RunningSum Over Group, but judges Trial Total only reflected the number of trials from the last event, not all the events.
Ok ... if you've hung in there 'till now maybe you can help me get this report to work the way I want it to.
I don't seem to be able to attach files (I'm at work and there's tight security here). Sorry about that.
TIA,
SueB
The database is for a Dog Competition Organization. The basic structure of the data is:
Dogs compete in a Class.
Dogs get a Score for entry in a Class.
If the Score is above a certain threshold the score is designated as 'qualified' (or a Q).
A Judge judges a Class.
There are three Classes in a Trial.
There may be many Trials in an Event (let's say 2 trials on Saturday and 2 trials on Sunday)
[trialnbr] is a value assigned to each trial within an Event. The value starts at 1. So, "Event A" may have "Trial 1" and "Trial 2". "Event B" may have "Trial 1" and "Trial 2". [trialnbr] is NOT the trial record key, [trialID] is the autonumber record key.
I have a report based on the following (Ugly) query:
SELECT [tblPeople].[lname] & (" "+[tblPeople].[suffix]+".") & ", " & [tblPeople].[fname] & (" "+[tblPeople].[midinit]+".") & " - " & [tblPeople].[city] & ", " & [tblPeople].[state] AS judgeinfo, tblJudges.n_qualdt, tblJudges.o_qualdt, tblJudges.u_qualdt, tblJudges.n_licdt, tblJudges.o_licdt, tblJudges.u_licdt, Year([tblTrials].[trialdt]) AS Yr, [tblEvents].[eventname] & " (" & [tblEvents].[eventhost] & " - " & [tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] & ")" AS eventinfo, tblTrials.trialnbr, tblTrials.trialdt, tblClasses.classID, tblClasses.class, tblScores.score, 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 [tblPeople].[lname] & (" "+[tblPeople].[suffix]+".") & ", " & [tblPeople].[fname] & (" "+[tblPeople].[midinit]+".") & " - " & [tblPeople].[city] & ", " & [tblPeople].[state], Year([tblTrials].[trialdt]), [tblEvents].[eventname] & " (" & [tblEvents].[eventhost] & " - " & [tblEvents].[eventcity] & ", " & [tblEvents].[eventstate] & ")", tblTrials.trialnbr, tblClasses.classID;
I have the 'Sorting and Grouping' setup as follows:
Yr (ascending)
judgeinfo (ascending)
eventinfo (ascending)
trialnbr (ascending)
classid (ascending)
I want to list all Events per Judge. Then for each event list all Trials. Then for each Trial list all Classes that the judge judged and some statistics from each Class (# of entries, # of Qs, Q Rate).
Then I want to total up the number of Events, Trials, and Classes for each Judge. That's the part that is not working quite right. I've attached a sample TEST report (using test data). The first page judge totals are correct. However, after the 1st judge the number of classes doesn't reset and start recounting.
WOW, this is getting to be long winded.
I get the Judges Class Total by doing the following:
1. Defined a textbox [classcounter] in the [classID] footer section
2. [classcounter].ControlSource = 1
3. [classcounter].runningsum = Over All (it, also, doesn't work if = Over Group)
4. Defined textbox in [judgeinfo] footer section with ControlSource = [classcounter]
I tried something similar, in a prior version of the report - before I gave up, to get [trialcounter] for the judge, but it wouldn't work, either. I put the [trialcounter] in the [trialnbr] header section with RunningSum Over Group, but judges Trial Total only reflected the number of trials from the last event, not all the events.
Ok ... if you've hung in there 'till now maybe you can help me get this report to work the way I want it to.
I don't seem to be able to attach files (I'm at work and there's tight security here). Sorry about that.
TIA,
SueB
