Ohio 4-H Blue Ribbon Youth Enrollment Support

SQL to generate a "master enrollment sheet" for each club. Data collected includes: Club Id, Club Name, Member/Leader ID, Member/Leader Last Name, Member/Leader First Name, Address, City, State, Zip, Phone, Age, Birthdate, Years in 4-H, Parent Last, Parent First, School, Grade, Project ID &
Project Description - Instructions, click here

****What do I do if the 237 report - lines 9, 10 & 11 do not balance? - Those of you who were not able to balance the 237 report on line 9, 10 & 11 probably have members or leaders with a invalid primary club code. To find out if this is the case run the following SQL:

Go in the youth program to File - Create and View Query

type in or copy and paste the following statements:

select distinct youth2a.memberid, youth2a.lastname, youth2a.firstname, youth2a.clubid, youth2a.memberid + youth2a.clubid memberclub

from "youth2a.db"youth2a

where (youth2a.memberid + youth2a.clubid not in (select distinct youth2xl.memberid + youth2xl.clubid

from "youth2xl.db"youth2xl))

and youth2a.category in ('M', 'C', 'S')

and youth2a.status in ('N', 'R', 'T')

order by youth2a.memberid

Click on Run Query
If a member shows up in the list, they do not have a valid primary club code that is listed in the club membership list. Go to their record, make sure that the code listed at the top of their record is also listed under the club / units tab. Make the changes to their file and you should be good to go for 2007. If you have questions feel free to give me a call or send a email.

*****Is there a query for awards? There is an awards query - File/Additional Queries/Member Award Query

****Are there examples of Certificates use by counties for members and advisors? (These are listed at this web site: 4-H Member Certificate Examples )

****How do I export records into access / excel? - If you want to dump data into Access or Excel, you should use the member query or member project query to get the information you want, and export it to either an ASCII file (for Excel) or a dBASE file (for Access) Directions to arrive shortly.

****On the special interest training areas are they strictly used for 4-H members / kids or adults also? (They need to be used for youth and adult groups associated with the 4H program).

*****How do I get a condensed report for a specific club? This report will help you to create a condensed report by club, which includes projects, etc. This report is usually 1-2 pages in length compared to 10-12 pages. Click here to access instructions

****In the Group Enrollment Form how do I know what information goes where and what information gets reported?

****In an office where the Blue Ribbon Youth Enrollment program is networked what needs to happen when doing the rollover?

The rollover needs to be done on the main computer. You will then need to set the activity year on the other computers.

****Can a county pull up the email address of parents whose children are enrolled in 4-H foods and nutrition projects?

There are directions for using these Queries in the User's Manual.

****The following are two SQL's that counties have requested using the view and create query feature in Blue Ribbon Youth Enrollment program.

First query is to create a list of females between the ages of 8-15 in projects 139 & 140. In the Blue Ribbon Youth Enrollment program go to file, view and create query, delete the select and from the middle of the white box and copy and paste this SQL and select run query. This query will list females ages 8-15 in projects 139 & 140.

Select Distinct Youth.firstname, Youth.lastname, Youth.address, Youth.city, Youth.state, Youth.zip
From "Youth2a.db" Youth
Inner Join "Youth2d.db" Youthproj
On (Youth.MemberID = Youthproj.MemberID)
Where ((Youthproj.projectId = "139" or Youthproj.projectId = "140")
And (Youth.Age >= "8"
And Youth.Age <="13")
And Youth.Category = "M"
And Youth.Status = "R"
And Youth.sex = "F")
Order by Youth.Lastname, Youth.Firstname

The second query is to create a list of members that do not have projects attached to their names. In the Blue Ribbon Youth Enrollment program go to file, view and create query, delete the select and from the middle of the white box and copy and paste this SQL and select run query.

****Is there a SQL to pull members with no projects assigned?

Select Distinct Youth2a.MemberID, Youth2a.LastName, Youth2a.FirstName, Youth2a.Category,
Youth2a.Status
From "Youth2a.db" Youth2a
WHERE (Youth2a.MemberID NOT IN (Select
Distinct Youth2d.MemberID
From "Youth2d.db" Youth2d))
        AND Youth2a.Category IN ('M', 'C')
        AND Youth2a.Status IN ('N', 'R')
           Order by Youth2a.MemberID

Judy Martin form Martech created these two SQL's.

****Other SQL formulas can be found in the documents file of the Blue Ribbon Youth Enrollment program. Go to your main program file, select Youth Enrollment, documentation, Youth Enrollment 2.4 Manual, open the file and scroll down to the Appendix H 12- 18.

****How can I create a mailing list of kids from the activity file?

It is pulling members enrolled in a Creative Arts activity by the ES-237 code.

Select Distinct Youth.firstname, Youth.lastname, Youth.address, Youth.city, Youth.state, Youth.zip
From "Youth2a.db" Youth
Inner Join "Youth2h.db" YouthAct
On (Youth.MemberID = YouthAct.MemberID)
Inner Join "Youth2g.db" Activity
On (YouthAct.ActivityID = Activity.ActivityID)
Where Activity.ES237 >= "B"
And Activity.ES237 <= "BCD"
And Youth.Age >= "12"
And Youth.Category = "M"
And Youth.Status = 'R'
Order by Youth.Lastname, Youth.firstname

****We went back and put 2005 on all of last year's projects and when we rolled everything over, the 2005 after last year's projects changed to 2006. How do I change it back?

To reset the Year in Project from 2006 to 2005:

In Youth Enrollment go to Help and then Troubleshooting and click on SQL.

Type in the following statements:

Update youth2d.db

Set YearProj = ‘2005'

Where YearProj = ‘2006'

Then Click on File and then Execute.

When SQL has Executed - Click OK.

The Year in Project should now be 2005 where it was 2006.

****Do we have to use the Youth Group Enrollment form on the web site or can we use the one we already have been using?

You can either use the ones on the web site or one that you currently using.

****Ohio Leadership Training Form - This form is used to record leadership training sessions for both adult and youth leaders. All participants of the training sessions need not be members or leaders of organized 4-H clubs/units.

****Ohio 4-H Youth Group Enrollment Form - This form is used to record School Enrichment, Special Interest, Day Camp, Overnight Camp, Individual Study, School Age Child Care & Instructional TV programs that are taught by Extension Professionals. These can be programs taught to youth by 4-H, FCS, FNP, Agriculture & etc. professionals.

This information is available on the web at: http://www.ohio4h.org/blueribbon/