Deprecated: Array and string offset access syntax with curly braces is deprecated in /homepages/12/d802028692/htdocs/forum/Sources/Subs.php on line 3863
Print Page - Awana Games prep

Approved Workman Database

Community Contributions - Legacy => Enhancement Requests => Topic started by: Mainah on February 11, 2010, 09:19:06 AM

Title: Awana Games prep
Post by: Mainah on February 11, 2010, 09:19:06 AM
The Achievement report could be the one stop list for determining who can play Awana Games if:
1) Another column were added listing what grade they are in.
2) Another column were added listing whether they are male or female.

Our club is doing Games for the first time in many years and the requirements are: (as quoted from Reg. Form)

Team Size and makeup: Minimum of 10 (5 boys & 5 Girls) maximum of 14 (7 boys & 7 girls);
--No more than half of the team can be 5th or 6th graders
--Any clubber who has reached their 11th birthday by Sept 1st must be regarded as a 5th grader
--Any clubber who has reached their 13th birthday by Sept. 1st is too old to compete in these events.
--All clubbers must have completed at least 10 sections this club year.
--If short team members, Sparkies (They used to specify Climbers only??) may be moved up to fill out a team in a 3rd grade capacity.

Do any of you using AW have any other tips or thoughts for using the DB as it is now for Awana Games prep?
Title: Re: Awana Games prep
Post by: Rick Leffler on February 11, 2010, 02:35:41 PM
Would simply adding new Grade and Gender columns get the job done?  This would be easy for me to do. 

Regards Grouping... the Group By selection already has an option to group on Date/ClubName/Grade,  should Gender be added in some manner and if so, how?
Title: Re: Awana Games prep
Post by: Mainah on February 12, 2010, 06:54:07 AM
I opened it up to check to answer your question.  When I used this report yesterday to get numbers of sections completed totals, I chose the 2nd report type (because I only need a grand total for the year,) which disables the Group by options.  If this is a glitch, now you know.  If it is not, however, then using the Sort by options is fine for this particular application.  

I chose T&T Boys first, tried out sorting by last name, then I closed it and tried again sorting by sections completed previewing each time to get a quick snapshot.  Ultimately I printed the report sorted by Last name so that when asked for status by a leader, we could quickly find the name.  For yesterdays purpose of establishing general qualifications, that same report was adequate for T&T Boys, T&T Girls, and Sparks.  Our Sparks, however, are all one Club, and not separated into Girls and Boys,so the next step will be more challenging for the Sparks sorting.

The next stage is to sort the qualified Sparks and T&Ters into teams based on Gender and in some cases Grade.  An additional "Sort by" would be Sections completed, Gender, Grade.  (I don't mind choosing the club name from the Selection Criteria box first, and then having Sort by choices be Gender, Sections completed, Grade.)  Possibly a Gender, Grade, Sections completed would be of use, in the case of Sparks but we haven't gotten that far in the planning yet to actually try to make teams out of the lists.  We're only gathering interest right now.

Feb 13th Modification
The roster we hand in also needs to have listed the age of each clubber on September 1st of the current club year.
Title: Re: Awana Games prep
Post by: tapper1 on February 14, 2010, 05:48:35 PM
I have had trouble counting ten sections (> than start zone) in the current club year except doing it manually.
Title: Re: Awana Games prep
Post by: Rick Leffler on February 14, 2010, 06:22:02 PM
Do we need an option to exclude entrance booklet completed sections from this report?
Title: Re: Awana Games prep
Post by: Mainah on February 15, 2010, 06:00:44 AM
Are you saying that the Awana Games entrance requirement of 10 sections excludes the entrance booklet, or are you commenting on that as a "while we're on the subject of counting sections."

I just checked the rule book and it does not exclude Start Zone sections, but there is a difference of opinion in our household!
Title: Re: Awana Games prep
Post by: dave4him on February 15, 2010, 08:12:01 AM
Counting the Start Zone as one of the ten required sections 'may' vary between regions. The Florida Region rule book does not mention the Entrance booklet, just says ten sections are required. At our club, we count Start Zone as a section, awarding points and T&T Trading Cards as we would regular book sections. I guess the option to 'count' or 'not count' is determined from region to region.
Title: Re: Awana Games prep
Post by: Rick Leffler on February 17, 2010, 03:12:00 PM
I'm not going to be able to modify the Achievement Report as quickly as I first thought. I know you were going to use the Achievement Report to help in your Awana Games planning.  Instead of using the existing report that does not include the Gender/Grade groupings you would like, please try the following SQL Statement and then use the result grid's grouping, sorting, filtering, and printing capabilities to create an ad-hoc report for your analysis.  

Following is the SQL statement you can copy/paste into the SQL Workbench:  

Note: you may want to change the date values to reflect a timespan appropriate to your needs.  This statement below is selecting records from 9/1/2009 through 2/1/2010.

select PS.Person_ID, P.Filing_Name, P.Grade, P.Gender, PS.Club_Name, P.Code,
P.Status, PS.Team_Color, PS.Team_Name,  
Sum(PS.Req_Section_Num) as SumReqSections, Sum(PS.Req_Section_Points) as SumReqPoints,
Sum(PS.Extra_Section_Num) as SumExtraSections, Sum(PS.Extra_Section_Points) as SumExtraPoints,
Sum(PS.Points) as SumTotalPoints, Sum(PS.Sections) as SumTotalSections  from Person P, Person_Session PS
where (PS.Session_Date >= '2009-9-1') and (PS.Session_Date <= '2010-2-1') and (PS.Person_ID = P.Person_ID)
and (P.Member_Type = 'Clubber')
Group by Person_ID Order by SumTotalSections desc, P.Filing_Name

Title: Re: Awana Games prep
Post by: tapper1 on February 17, 2010, 05:28:44 PM
In our region (Northern Il.) they have been clear for years that 10 sections excluding Entrance Sections is th erequirement for games qualifications.  We have seen many clubbers put out more effort to get the sections needed for games (we also have our games in March - that might be a difference also).  So my desire would be to have the option (if it is reasonable) to exclude the entrance sections.

Terry P
Title: Re: Awana Games prep
Post by: dave4him on February 18, 2010, 06:01:22 PM
Rick, I ran your SQL query above... will be very handy. I noticed that you accidently used 2009 instead of 2010 in the query.
Title: Re: Awana Games prep
Post by: Rick Leffler on February 18, 2010, 09:28:12 PM
Good catch. Thanks Dave!    (I've modifed the post so it is corrected)
Title: Re: Awana Games prep
Post by: Mainah on February 19, 2010, 09:23:02 AM
oooh...SQL.  I think I'll grab a geek from my household to hold my hand while I do this! 

Thanks for the work on it!

I think we'll stick with using the Start Zone sections as qualified, for this season at least.  No child in any of our current clubs has ever competed and we're having a tough time convincing new to Awana families how much fun it would be to participate.  Also, we didn't get approval to participate from the Pastor soon enough in the season to really lay the recruiting groundwork before our registration deadline which is tomorrow!