last updated 7/24/06 4:00 pm
[top of main Blue Ribbon page]

Member Grade	New/Yr in 4-H>1 error	Youth Ldr DV error	County Number
Leader Type	Residence-Leader/Other	Youth Ldr Set Direct	Change Area Code
Club links	Parent/Member links	Race-Leader/Other	Ethnic Member/Ldr
Stray links	Club Delivery Mode	Group Youth	Score
Projects	M/F toUppercase	Group Volunteers	Remove SSN
Schools

SQL Fixes for Problems in the Data Files

SQL fixes permanently change the data in your files, there is no "Undo", so be very careful. Before applying an SQL fix, make sure you have a current backup of your data. Label disk(s) with date, time, and "Before SQL fixes". Write protect the diskettes so you won't accidentally over-write them--move the slider to open the window on 3.5" floppy. If you are doing several SQL fixes, it is a good idea to make a backup copy of the file you updated after each fix on separate diskettes (or a different directory on your hard disk). That way if a fix messes up your file you can recover from the version immediately before it.

Save a copy of this page on your local hard drive with File|Save As. Then you can access it from your browser without being connected to the internet. It is easy to highlight the fix you want to use and copy it with Ctrl-C.

SQL fixes usually have 3 statements which you will copy and paste into the SQL window:
    1. UPDATE filename to update
    2. SET fieldname = "value"
    3. WHERE limits the records to be changed (All are changed if left off)

To Apply an SQL Fix to your data:

Copy the statements from the fix you want to apply (select just the statements, not the box, then Ctrl-C to Copy).
In the Blue Ribbon Youth Enrollment Program, go to Help|Troubleshooting and pick SQL to open the SQL ("Execute SQL Utility") window.
Paste them into the SQL window (Ctrl-V to Paste).
Edit as needed, but be careful to leave the commas and quotes where they were.
File|Execute (F8) to run it
Alt-F4 or Click on x in upper right corner of SQL window to close it
Check that the fix changed what you expected it to change. Use the ES237 verification report, ES237 report, and/or Member Query with a filter to evaluate the changes. You can also go to Utility|File Utilities and double-click on a filename to view the records in the order they were entered.

Member/Leader (Youth2a.db)

Member Grade
Remove Direct Volunteer (DV) if not a Youth Leader error fix
Status = New, but has more than 1 Year in 4-H error -- change to Return
All Delivery Modes error--change to all 1, Organized Club
Volunteer Type - Set Direct Volunteer if Gen/Org, Project or Activity Leader
Volunteer Type - Set Indirect Volunteer if Resource Leader
Volunteer Type - Set Direct Volunteer if Youth Leader
set all who do not already have a Race entry to the same Race
put the Residence code you select on every Member/Leader record that is missing one
make Sex M/F uppercase (m/f not counted ES237, p. 1 top)
remove Social Security numbers

Member Age

If you have a Grade, you can Estimate Age as Grade + 5. If no grade, this fix leaves Age as missing (blank on Member/Query or -1 on Member/Leader List Age report).

UPDATE Youth2a.db
SET Age = Grade+5
WHERE (Age=0 and (Category="M" or Category="C") and Grade>=0)

Member Grade

1) Set Member Grade

Be sure to fix all AGE errors before running this Grade fix.

1) Birthdate error = blank or 20xx (Member Query filter for Age -1000 to 4 on Category=M or C)
2) File|County Information|4-H Age Calculation date 9/1/2001
(for FL FY2002 reporting year, 9/1/2001-8/31/2002)
3) go to Utility|File Utilities|Recalculate Age (Member/Leader File)

This SQL program fills in the Member's estimated grade based on the Age-5, for those who left it blank and who had an Age entered (calculated from Birthdate by program). A 5 yr. old will have grade=0, which displays as Kindergarten on reports. Missing Grades show as Not in School in error on ES237 #10.

Note that Mentally challenged members over 19 years (= "Special" grade) and anyone entered in the member Category = Special would have to be edited manually.

UPDATE Youth2a.db
SET Grade=Age-5
WHERE (Grade=-1 and (Category="M" or Category="C") and Age>0)

Sarasota fixes
1) This will change Grade to Grade - 1 and Year in 4H to (Year in 4H -1) on all Members and Cloverbuds who have Status = Return.

UPDATE Youth2a.db
SET Grade=Grade-1
WHERE (Category="M" or Category="C") and Status="R"

2) This will change Year in 4H to (Year in 4H -1) on all Members, Cloverbuds and Leaders who have Status = Return.

UPDATE Youth2a.db
SET Year4h=Year4h-1
WHERE Status="R"

Go to Utility|File Utilities, click on Youth2a.db, then File|Recreate Indexes to recreate the associated index file.

DUVAL FIX. This will change Grade to Grade - 1 on all Members and Cloverbuds who are in Grade 1 or higher. Everyone must have a valid age first.

UPDATE Youth2a.db
SET Grade=Age-5
WHERE Grade>=1 and (Category="M" or Category="C")

Ethnic

1. Find Ethnic entries that are not N or H (one county had W and Total Hisp+Total Not Hisp didn't equal Total M+Total F)

Select *
From youth2a
Where Ethnic NOT IN ('N','H') and Status IN ('N','R','T')

For Members or Leaders, this sets all the Ethnic codes to Not Hispanic where they are blank or Null.

and Category in ('M','C')
and Category in ('G','P','A','R')

Select and copy the contents of the box Ctrl-C.
Go to Help|Troubleshooting|SQL and paste the following:

Update Youth2a
Set Ethnic = 'N' Where Ethnic = "" or Ethnic is Null

Go File|Execute

Mixed Race v2.3 Conversion to Non-Hispanic Black&White in v2.4

Mixed23: In version 2.3 run this SQL to identify the mixed race individuals so you can put them into the Black&White category instead of just White under the not Hispanic Ethnic group. Go to Help|Troubleshooting|SQL, copy and paste the contents of the box below, then File|Execute to run it.

Update Youth2a
Set Fill4 = "B"
Where Ethnic = "X"

Install or Update to Version 2.4 and make sure you update the data files. You must point to the correct location of the data files for the install/update to succeed. If data files are shared over a network, everyone else must be closed out of the program while updating (converting) the data files or applying SQLs.

Mixed24: In version 2.4, go to Help|Troubleshooting|SQL, copy and paste the contents of the box below, then File|Execute to run it.

Update Youth2a
Set Black = "True"
Where Fill4 = "B"

Go to File|Utility|File Utilities, pick Youth2a, the File Recreate Indexes. Former Mixed Race individuals can now be found by filtering for Black =True and White =True. Update with their actual Races, once verified.

Group Youth errors

Group Youth entry with a Club Delivery Mode (DM < 5). In Florida, all Club enrollment should be entered as individuals on the Member/Leaders screen. Only EFNEP (Expanded Food and Nutrition Education Program) clubs are allowed to be entered as a total record with a Club Delivery mode on the Youth Group Enrollment screen. Copy box contents into Create and View Query, then Run Query.

Select Location, Leader, ID, youth2n."Date", ClubID, GroupType, Unit, Male, Female, TotalH, TotalNH, SUM(TotalH+TotalNH) AS TotalE, SUM(Male+Female) AS TotalMF
From Youth2n
Where GroupType < 5 or GroupType = 9
GROUP BY Location, Leader, ID, youth2n."Date", ClubID, GroupType, Unit, Male, Female, TotalH, TotalNH
ORDER BY GroupType, Location, Leader, youth2n."Date"

When Group Youth records have Grade = Special in error, convert to Not in School.

Update youth2n
Set youth2n.NotInSchool=youth2n.Special
Where Youth2n.Special>0

Group Youth Units=Males+Females error
Run the next 4 fixes in order to reset units and dupUnits for ES237|Group Youth and manually edit 1 unit for 0, and edit Duplicate units to match corrected number of Units.

Update youth2n
Set unit=(male+female)/30
Where male+female>0

Update youth2n
Set dupUnit=(dupMale+dupFemale)/30
Where (dupunit>0 and dupmale+dupfemale>0)

Update youth2n
Set unit=1
Where (unit= 0 and (male+female>0))

Update youth2n
Set dupUnit=1
Where (dupunit=0 and (dupmale+dupfemale>0))

Group Youth Enrollment, Duplicate button doesn't work.
1. Duplicate key only works on first tab, Delivery. ("cannot focus on a diabled or invisible window" error message)
2. On the last tab Ethnic/Race, you must actually type in 0 (zero) in the Total Hispanics box, if you have no Hispanics, or type in a 0 (zero) in the Total Not Hispanic box, if you only have Hispanics.  (" is not a valid integer error means you need 0s or the correct total numbers in the Total Hispanic and Total Not Hispanic fields.

If the TotalH or TotalNH box is empty, when you hit Save, you should have received an error message " is not a valid integer and the record should not save. This message may have been added in the March 18, 2004 update or the May 4, 2004 update, the Nov. 25, 2003 version let you save records with blanks.

Copy the first sql to File|Create and View Query to see if any of your Group Youth Enrollments are missing a 0 entry for Total Hispanic or Total Not Hispanic:

select *
from youth2n
where TotalH is Null or TotalNH is Null

If you are have blanks instead of 0 for TotalH or TotalNH, copy and paste the next 8 update sqls (one at a time) to Help|Troubleshooting|SQL, then execute it. The last one you selected is still highlighted when you come back from the Youth Enrollment Program.

Youth Group --when group is a duplicate doing a different project, the ethnic totals and all race sub-totals for Hispanic and for Not Hispanic are all 0

update youth2n
set TotalH = 0
where TotalH is Null or TotalH =""

update youth2n
set TotalNH = 0
where TotalNH is Null or TotalNH=''"

update youth2n
set unit = 0
where unit is Null or Unit=""

update youth2n
set DupUnit = 0
where DupUnit is Null or DupUnit=""

update youth2n
set Male = 0
where Male is Null or Male=""

update youth2n
set Female = 0
where Female is Null or Female=""

update youth2n
set DupMale = 0
where DupMale is Null or DupMale=""

update youth2n
set DupFemale = 0
where DupFemale is Null or DupFemale=""

Change blanks to 0 for columns on YGBalNH custom report

update youth2n
set NHWB1 = 0
where NHWB1 is Null or NHWB1=''"

update youth2n
set HWI1 = 0
where HWI1 is Null or HWI1=''"

update youth2n
set HBI1 = 0
where HBI1 is Null or HBI1=''"

Group Youth fixes for TotalH or TotalNH blank, won't work if Races also blanks (must be 0 or a number to add together) Be Careful if used

update youth2n
set TotalH = HW+HB+ HI+ HA+ HH+ HWB1+ HWB2+HWI1+ HWI2+ HBI1+HBI2+HWA1+HWA2+HBW+HBB+HBI+ HBA+HBH
where TotalH is Null or TotalH ="" or TotalH=0

update youth2n
set TotalNH =NHW+ NHB+ NHI+ NHA+ NHH+ NHWB1+ NHWB2+NHWI1+ NHWI2+NHBI1+NHBI2+NHWA1+NHWA2+NHBW+NHBB+NHBI+ NHBA+NHBH
where TotalNH is Null or TotalNH=''"

Group Youth fixes for races entered in error when TotalH or TotalNH =0 because all youth are duplicates.

update youth2n
set HW=0, HB=0, HI=0, HA=0, HH=0, HWB1=0, HWB2=0, HWI1=0, HWI2=0, HBI1=0,
HBI2=0, HWA1=0, HWA2=0, HBW=0, HBB=0, HBI=0, HBA=0, HBH=0
where TotalH=0

update youth2n
set NHW=0, NHB=0, NHI=0, NHA=0, NHH=0, NHWB1=0, NHWB2=0, NHWI1=0, NHWI2=0,
NHBI1=0, NHBI2=0, NHWA1=0, NHWA2=0, NHBW=0, NHBB=0, NHBI=0, NHBA=0, NHBH=0
where TotalNH=0

update youth2n
set HW=0, HB=0, HI=0, HA=0, HH=0, HWB1=0, HWB2=0, HWI1=0, HWI2=0, HBI1=0,
HBI2=0, HWA1=0, HWA2=0, HBW=0, HBB=0, HBI=0, HBA=0, HBH=0,NHW=0, NHB=0, NHI=0,
NHA=0, NHH=0, NHWB1=0, NHWB2=0, NHWI1=0, NHWI2=0, NHBI1=0, NHBI2=0, NHWA1=0,
NHWA2=0, NHBW=0, NHBB=0, NHBI=0, NHBA=0, NHBH=0
where TotalH=0 and TotalNH=0

update youth2n
set IntegrateGroup="True", RacialMix="True"
where (TotalH >0 and TotalNH >0) or (nhw>0 and nhb>0)

2) Remove Direct Volunteer (DV) if not a Youth Leader

Before running this fix, use the ES237 Verification report for Members (by ID or Alpha) to identify those who would be affected. If they really are supposed to be Youth Leaders, edit them in Member/Query (filter for JrLead=False and Category=M). You need to check the Youth Leader box and uncheck and then recheck the Direct Volunteer for them both to show up. The Youth Enrollment program removes the DV mark if you only check the Youth Leader box when editing in Member Query.

This setnoYLDV.txt SQL program removes the DirectVol (DV) mark on every member (Category="M") that is not marked as a Jr. Leader (Youth Leader). They were showing up on ES237 #14 as Youth Leaders in error.
We don't know what triggered this error, but this is how you fix it. This does not check or fix Cloverbud= C or Special = S members.

UPDATE Youth2a.db
SET DirectVol=False
WHERE JrLead=False and Category="M"

See a related problem fix Add Direct Volunteer Type to all Youth Leaders

3) Status = New, but has more than 1 Year in 4-H error -- change to Return

This setReturn.txt SQL program assumes that the Year in 4-H is correct and that the New Status is a mistake. It changes everyone with more than one Year in 4-H who had New Status, to Return Status (ignores Alumni and Inactive Status records). If there is no entry in Year in 4-H, there may still be "New" status errors in your Member/Leader records.

Fix any Year in 4-H errors = 1999, 2000, 9900 before running this SQL fix. In Member/Query, filter for Year4H range Start = 80 and End = 10000 to find any member or leader with excessive years.

UPDATE youth2a
SET Status = "R"
WHERE Status="N" and Year4H>1;

Volunteer Type

5) This will set all General/Org, Project, and Activity Leaders to Direct Volunteer Type (DV)

While General/Org, Project, and Activity Leaders are always considered to be Direct Volunteers, they may also be Indirect Volunteers (ex. serve on a 4-H committee) and/or Middle Managers (ex. coordinates/supervises other volunteers). You would need to fill in these other roles manually for each leader.

UPDATE youth2a
SET DirectVol=True
WHERE Category IN ('G','P','A') and DirectVol= False

6) This will set ALL Resource Leaders to InDirect Volunteer Type (IV)

While Resource Leaders are often considered to be Indirect Volunteers (ex. serve on a 4-H committee), they may also be Direct Volunteers (work face-to-face with youth) and/or Middle Managers (ex. coordinates/supervises other volunteers). You would need to fill in these other roles manually for each Resource Leader and remove the Indirect Volunteer Type if it doesn't actually apply.

UPDATE youth2a
SET InDirectVol=True
WHERE Category ='R';

7) This will set all Youth Leaders to Direct Volunteer Type

A BUG in the November 15, 1999 ver. 2.0 Youth Enrollment program caused Youth Leaders to display on the Member/Leader screen with Direct Volunteer Type marked (by default when Youth Leader box was checked) but on the ES237 Verification Report for Members, the DV column showed False and they were skipped on the ES237 report itself.

While Youth Leaders are almost always Direct Volunteers, they may also be Indirect Volunteers (ex. serve on a 4-H committee) and/or Middle Managers (ex. coordinates/supervises other volunteers). You would need to fill in these other roles manually for each Youth Leader and remove the Direct Volunteer mark if it really does not apply to a particular Youth Leader.

UPDATE youth2a
SET DirectVol=True
WHERE JrLead=True and Category="M" ;

Go to Utility|File Utilities, click on Youth2a.db, then File|Recreate Indexes to recreate the associated index file.

Ethnic - Leaders and Other

?? 8) This will set all who do not already have an Ethnic entry to Not Hispanic and Race = White. Use to set an Ethnicity so that they will be included on Reports (any that offer "Ethnic Stats" option must have an Ethnicity and a Race).

UPDATE Youth2a
SET Ethnic='N',
White = True
WHERE Category IN ('G','P','A','R','O') AND Ethnic=NULL

Residence - Leaders and Other

9) This will put the Residence code you select on every Member/Leader record that is missing one. Substitute the number most appropriate for your county for the '3':

1= farm
2= town <10,000, rural
3= town/city 10-50,000
4= suburbs of cities >50,000
5= central cities >50,000

?? Use to set a residence so that they will be included on Reports. This should only affect Leaders and Other, as Members, Cloverbuds and Special are required to have a Residence.

UPDATE Youth2a
SET Residence='3'
WHERE Residence<'1'

10) Make ALL sex entries in the Member/Leader file Uppercase.

UPDATE Youth2a.db
SET Sex=UPPER(Sex)

Group Volunteers

In version 2.4, Group Volunteer records don't automatically fill in zeros on empty fields, a test shows empty fields are Null. SQLs to fix Group Vols :

!Update youth2M
!Set AtotalH = 0,AtotalNH=0,AMDir=0,AFDir=0,AMInd=0,AFInd=0
!Where AtotalH is Null and AtotalNH is Null

Adult Group Volunteer fixes:

Update youth2M
Set AtotalH = 0
Where AtotalH is Null

Update youth2M
Set AtotalNH = 0
Where AtotalNH is Null

Update youth2M
Set AMDir = 0
Where AMDir is Null

Update youth2M
Set AFDir = 0
Where AFDir is Null

Update youth2M
Set AMInd = 0
Where AMInd is Null

Update youth2M
Set AFInd = 0
Where AFInd is Null

Update youth2M
Set AMMan = 0
Where AMMan is Null

Update youth2M
Set AFMan = 0
Where AFMan is Null

Update youth2M
Set AMDup = 0
Where AMDup is Null

Update youth2M
Set AFDup = 0
Where AFDup is Null

Update youth2M
Set AHW = 0
Where AHW is Null

Update youth2M
Set AHB = 0
Where AHB is Null

Update youth2M
Set ANHW = 0
Where ANHW is Null

Update youth2M
Set ANHB = 0
Where ANHB is Null

Youth Group Volunteer fix

Update Youth2m

Set YtotalH = 0

Where YtotalH is Null

Update Youth2m

Set YtotalNH = 0

Where YtotalNH is Null

Update youth2M
Set YMDir = 0
Where YMDir is Null

Update youth2M
Set YFDir = 0
Where YFDir is Null

Update youth2M
Set YMInd = 0
Where YMInd is Null

Update youth2M
Set YFInd = 0
Where YFInd is Null

Update youth2M
Set YMMan = 0
Where YMMan is Null

Update youth2M
Set YFMan = 0
Where YFMan is Null

Update youth2M
Set YMDup = 0
Where YMDup is Null

Update youth2M
Set YFDup = 0
Where YFDup is Null

Parent/Family-Member Links

Sometimes a Parent/Family link gets broken. These 2 steps will (1) remove parent records that do not have a Parent link and (2) remove Parent links that do not have a Member/Leader record. Back up your data before attempting this SQL.

first step:

DELETE FROM "Youth2b.db" d
WHERE
NOT (d.ParentID in (SELECT DISTINCT d2.ParentID FROM "Youth2bl.db" d2))

second step:

DELETE FROM "Youth2bl.db" d
WHERE
NOT (d.ParentID in (SELECT DISTINCT d2.ParentID FROM "Youth2b.db" d2))

Club Links

10/27/04 Members with Invalid Club Links
When the Primary Club code at the top of the member screen is different from the Primary Club code on the Clubs tab, the Youth will not be included in the ES237 top of p. 1, or bottom of p. 2.

Select Distinct Youth2a.MemberID, Youth2a.LastName, Youth2a.FirstName,Youth2a.ClubID,Youth2a.Category,Youth2a.Status

From "Youth2a.db" Youth2a
Inner Join "Youth2xl.db" Youth2xl
ON (Youth2xl.MemberID = Youth2a.MemberID)
AND (Youth2xl.ClubID <> Youth2a.ClubID)
AND Youth2a.Category IN ('M', 'C', 'S')
AND Youth2a.Status IN ('N', 'R', 'T')
Order by Youth2a.ClubID,Youth2a.LastName,Youth2a.FirstName

Prior to the November 14, 2000 Update, the Youth Enrollment program allowed you to delete Club records from the master Club file and didn't check if there were Members or Leaders left with links to that Club--"stray links". Starting with the November 14, 2000 Update, Member ID & Parent ID assignments were separated and lower numbers could be re-used. Stray links to Clubs started re-appearing as "extra" clubs on new Members.

Symptoms:
1. If a Club was deleted from the master club file before all of its Members and Leaders were changed to a new club, the Members and Leaders continue to print out on the Member/Leader by Club report in this "non-existent" club.

2. If a Member ID was re-used, any left-over, stray links to Clubs from that old Member ID would be added, along with the highlighted Club when "Add as Primary" was picked from the M/L Clubs tab.

Caution: Member/Query (MQ) misses some club members, it shows only the Primary Club entry for a Member or Leader. MQ lets you sort by Club, filter for a club code, select All Members (of the filtered club), add a new club code, remove the old club code, and set the club as primary. If a Member is in 2 or 3 Clubs, you can't see the records for the 2nd or 3rd Club, so you can't edit or delete them from the regular Member/Query screen.

New with the November 14, 2000 update, File|Additional Queries|Club Member Query lets you see all Club records for all Members/Leaders (but displays fewer fields than regular Member/Query).

You have Club link problems if you have:
1. Blanks in the Name and/or in the Club ID fields
2. Club IDs are listed that don't appear in the Master Club file

Fix missing Primary Club code problems BEFORE running club SQLs.
** Every Member/Leader record must have a Primary Club that is in the master Club file.

First check Member/Query, set the Index to Club (ID), and look at the top for any blanks in the Club ID field--that means that no Primary Club has been assigned to that Member or Leader record.

If the First Name, Last Name and Address are all blanks, it means a blank record was added with only your Preference settings automatically filled-in. Select all the "blank" records and Delete them.

If there is a Name and Address, and Status is N, R, or T, check enrollment records for the correct primary club. Double-click the record in Member Query to open the Member Leader screen, click on the Clubs tab, pick Add Club, select the club from the list of Clubs, and pick "Add as Primary". Click on Save to save your changes and then Exit to close Member Leader screen and return to Member Query.

Back up your data before attempting any SQL.

DELETE FROM "Youth2xl.db" d
WHERE
NOT (d.clubID in (SELECT DISTINCT d2.clubID FROM "Youth2x.db" d2))

Club Delivery Mode

?? When all units are entered in Group Youth records, the Club file entries for Schools (DM=8), DayCamps (DM= 7), Overnight Camp (DM=6) and for Special Interest (DM=5) add an extra unit each.

Update youth2x
Set DeliverMode='12'
Where (DeliverMode='8' or DeliverMode='7' or DeliverMode='6' or DeliverMode='5')

County Number

How to Change County Code if Missing or Incorrect:

In Florida, your County ID Code = (zero) 0 + 2 digit Plan of Work number for your unit. County Codes are listed on p. 4 of the Florida 4-H Blue Ribbon Youth Enrollment Supplemental Programming Guide, in the Blue Ribbon Youth Enrollment Manual. Because the County code is used extensively, you will need to change it in your setup file (Youth2i), Preferences, and in a number of files. Be very careful when editing SQL to enter the correct filename and to retain all punctuation.

1. Go to Utility|File Utilities.
Double-click on Youth2i.db.
Edit the Code on that screen to be your correct County ID code.

2. Go to Utility|Preferences, and correct the County Code there too.

3. Your members probably still have the wrong county code. It will take too long to change each one separately, so we will change them all at once using SQL. SQL is unforgiving, if you make a mistake, you will need to start over again after restoring the backup.

Make a backup of your data files NOW, before using SQL.

Once you have a good backup of your data files,

Copy the statements from the fix you want to apply (select them, then Ctrl-C to Copy).
In the Blue Ribbon Youth Enrollment Program, go to Help|Troubleshooting and pick SQL to open the SQL ("Execute SQL Utility") window.
Paste them into the SQL window (Ctrl-V to Paste).
Edit as needed, but be careful to leave the commas and quotes where they were.
File|Execute (F8) to run it

UPDATE Youth2A
SET CountyID = "060"

(edit to put your correct county code inside the " ", not 060)

You will have to repeat the SQL, editing the filename in turn to:

Youth2B Parent file

Youth2BL Parent Link file

Youth2X Club file

Youth2XL Club Link file

Youth2N Youth Group Entries

Youth2M Group Volunteers

To check to make sure the SQL "took", stay in File Utilities, and DOUBLE-click on Youth2b or Youth2bl. Use the "triangle" buttons to scroll through your records and make sure that the correct CountyID number is entered into that field in each record.

Change Area Code in Phone Numbers (from Iowa)

How to Change Area Code for ALL Exchanges

SQL statements for changing the area code on all phone numbers in the parent database (HomePhone, WorkPhone, SpouseWorkPhone, Fax.). This assumes that ENTIRE COUNTY (EVERY member) is changing area codes (ex. to 641). Backup before you begin, these SQL statements change the area code on all phone numbers without checking whether the exchange should be switched. Use Change Specific Exchanges if you need to be more careful (like when work numbers are commonly in a different area code).

Choose Help|Troubleshooting|SQL. Copy and paste the following statements into the SQL text box, then edit to substitute your new area code for 641.

UPDATE "Youth2b.db"
SET HomePhone = '(641' + substring(HomePhone FROM 5 FOR 9)

File|Execute. Consider recreating indexes and checking the records after doing each one of the phone fields--that way you'll see if there were any problems and you can start over from the backup before you've wasted too much effort. Go to Utility|File Utilities|Select, Select All, File, Recreate Indexes to recreate your indexes. Click OK when it finishes.

To check to make sure the SQL "took", stay in File Utilities, and DOUBLE-click on Youth2b. Use the "triangle" buttons to scroll through your records and make sure that the new area code is entered into the phone number fields that you changed in each record (only HomePhone, so far).

When ready to continue, go back to Help|Troubleshooting|SQL and copy & paste:

UPDATE "Youth2b.db"
SET WorkPhone = '(641' + substring(WorkPhone FROM 5 FOR 9)

File|Execute, then copy & paste:

UPDATE "Youth2b.db"
SET SpouseWorkPhone = '(641' + substring(SpouseWorkPhone FROM 5 FOR 9)

File|Execute, then copy & paste:

UPDATE "Youth2b.db"
SET Fax = '(641' + substring(Fax FROM 5 FOR 9)

When you're done, go to Utility|File Utilities|Select, Select All, File, Recreate Indexes to recreate your indexes and then check some records to see if the correct thing happened. It's crucial that you have the backup in case something didn't work correctly.

How to Change Area Code for SPECIFIC Exchanges

Use this set of SQL statements to change your area codes when only some of your exchanges are switching (ex. 515 to new area code 641). DON'T do this until the change is actually made!

The basic premise is that you would know which exchanges are switching over in your county. What do I mean by "exchanges"?--In Hamilton Co, Blairsburg has a 325 prefix (exchange). Webster City has 832, etc etc etc. In some counties, ALL phone numbers will change to the new area code, but in many counties, only specific exchanges will change over.

Here you go. Basic instructions are the same. Do a backup. This WON'T be "fixable" if something goes wrong. Copy and paste my instructions. It's much too hard to re-type them accurately.

You have to change these four fields: HomePhone, WorkPhone, SpouseWorkPhone, and Fax. They are all found in Youth2b.db, the parent file. Consider recreating indexes and checking the records after doing each one of the phone fields--that way you'll see if there were any problems and you can start over from the backup before you've wasted too much effort.

Start with HomePhone. Copy and paste the following SQL statement into the text box. CHANGE the xxx to the first exchange (prefix) that will be changed in your county. File|Execute, then change the first prefix to the next one and Execute again, on & on until you're done with all prefixes that are changing. Be sure to leave the single quotes when you change the xxx.

UPDATE "Youth2b.db"
SET HomePhone = '(641' + substring(HomePhone FROM 5 FOR 9)
WHERE substring(HomePhone FROM 6 FOR 3) = 'xxx'

When you've changed all the ones you need to for HomePhone, move on to WorkPhone:

UPDATE "Youth2b.db"
SET WorkPhone = '(641' + substring(WorkPhone FROM 5 FOR 9)
WHERE substring(WorkPhone FROM 6 FOR 3) = 'xxx'

Then SpouseWorkPhone:

UPDATE "Youth2b.db"
SET SpouseWorkPhone = '(641' + substring(SpouseWorkPhone FROM 5 FOR 9) WHERE substring(SpouseWorkPhone FROM 6 FOR 3) = 'xxx'

Then Fax:

UPDATE "Youth2b.db"
SET Fax = '(641' + substring(Fax FROM 5 FOR 9)
WHERE substring(Fax FROM 6 FOR 3) = 'xxx'

When you're done, recreate your indexes and check some records to see if the correct thing happened. It's crucial that you have the backup in case something didn't work correctly.

Your only other option, besides using these SQL statements, is to manually change each record. Fastest place to do that might be in Utility|File Utilities, double-click on Youth2b, and change each record as you scroll through them. Your choice--I'm just trying to save you a little time and work!

To recreate indexes, go to Utility|File Utilities, highlight Youth2b.db, then CTL-click to also highlight Youth2bl.db. Choose File|Recreate Indexes. You'll get a message, click OK.

Projects

A project must be Active to be included in the ES237 Curriculum Category counts. If you added projects through Member Query and forgot to mark the box for "Active", they will be skipped on the p. 4-7 of the ES237. Back up your data before attempting this SQL.

1. Reset projects to Active on all Members and Leaders who were active this year = have Member Status of N (new), R (return), or T (Terminate)

select just the statements, not the box, then Ctrl-C to Copy (or pick Edit|Copy).
In the Blue Ribbon Youth Enrollment Program, go to Help|Troubleshooting and pick SQL to open the SQL ("Execute SQL Utility") window.
Paste them into the SQL window--use Ctrl-V to Paste (or Edit|Paste).

Update "Youth2d.db" d
Set "Youth2d.db"."Active" = True
Where d.MemberID IN (SELECT DISTINCT d2.MemberID
From "Youth2a.db" d2
Where "Youth2a.db"."Status" = "N")

Then go to File|Execute.
then simply change the N (by Status) to R and go to File|Execute,
then change to T and go to File|Execute.

2. Make ALL project codes in the Member Project file Uppercase. Curriculum Category pages of the ES237 and most Reports do not include Projects that are in lowercase or mixed case.

UPDATE Youth2d.db
SET ProjectID=UPPER(ProjectID)

3. Make ALL project codes in the Youth Group file Uppercase. Curriculum Category pages of the ES237 do not include Projects that are in lowercase or mixed case.

UPDATE Youth2n.db
SET ID=UPPER(ID)

?? 4. Reset ALL projects on all Members and Leaders to first year in the project (YearProj = 1). Do not use if you have Members marked with 2nd or 3rd year in project.

UPDATE Youth2d.db
SET YearProj=1

Score

Delete Member Projects under Utility|File Utilities|Utilities caused error "score is not found" when opening Member/Leader screen.

If you get an error going into your member/leader file saying the score is not found, and your project file has no fields, there is an error. Reason - By using Delete Member Projects under Utility|File Utilities|Utilities|Delete member projects, it doesn’t re-add the Score field back in the member project file. There are a few options to avoid/correct this: Empty table instead of deleting member projects under Utility|File Utilties|Utilities. OR

Do an sql statement to add the Y2dScore field back into the Project table: Go to Help|Troubleshooting|SQL and paste the following statement:

ALTER TABLE Youth2d
Add Y2dScore Char (2)

Go to File|Execute

Remove Social Security Numbers (SSN)

This will remove all Social Security Numbers from the Member/Leader file, youth2a.db.

Update youth2a
Set SocSec=' '
Where SocSec>'111-11-1111'

Schools

List of School entries on active Members (from youth2a Member/Leader screen)
File|Create and View Query copy the contents of this box into Query text box then click on Run Query

lists school, count of members with that school code sorted in school order:

Select school, count(school)
From youth2a
Where status in ('N','R') and category in ('M','C') and Not School=""
group by school
order by school

Select distinct school
From youth2a
Where status in ('N','R') and category in ('M','C') and Not School=""
order by school

List Schools in youth2xx.db (school drop-down list choices on Member/Leader screen)
File|Create and View Query copy the contents of this box into Query text box then click on Run Query

select *
From youth2xx
order by code

Change entries in youth2a.db for variations of "home school"

change entries of home, home school, home schooled, and homeschooled to HOME

update youth2a.db
set school="HOME"
where school between 'home' and 'homeschooled'

change entries of HOME, HOME SCHOOL, HOME SCHOOLED, and HOMESCHOOLED to HOME

update youth2a.db
set school="HOME"
where school between 'HOME' and 'HOMESCHOOLED

[top of page]
[top of main Blue Ribbon page]

Member Grade New/Yr in 4-H>1 error Youth Ldr DV error County Number

Leader Type Residence-Leader/Other Youth Ldr Set Direct all DMs error

Club links Parent/Member links Race-Leader/Other Fed Initiative fix

Stray links Club Delivery Mode Group Youth Score

Projects M/F toUppercase Change Area Code Remove SSN

Youth2B	Parent file
Youth2BL	Parent Link file
Youth2X	Club file
Youth2XL	Club Link file
Youth2N	Youth Group Entries
Youth2M	Group Volunteers