1 / 124

Mastering Queries 2007

Mastering Queries 2007. By Jim Elder. Queries can be used for a number of reasons: * To sort and filter to find information quickly. * To add or delete information by filtering a group of people to quickly make a change. * To quickly check information by a certain grouping.

casper
Download Presentation

Mastering Queries 2007

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Mastering Queries2007 By Jim Elder

  2. Queries can be used for a number of reasons: * To sort and filter to find information quickly. * To add or delete information by filtering a group of people to quickly make a change. * To quickly check information by a certain grouping. * To export information into another program.

  3. To make filtering the queries easier, review and print the Code Identification (Report under Reports|Print Data Files|Code Identification Report). This information gives you the letters that you need to place in the field value space.

  4. Code Identification Report Windows Youth Enrollment Category ID Category • A Activity Leader • C Cloverbud./Mini • G General/Org Ldr • M Members • 0 Other • P Project Leader • R Resource Leader • S Special

  5. Reserved Categories • S - Special (Special interest/school enrichment only) • O- Other (not counted in year end statistical)

  6. Ethnic Code Ethnicity • H Hispanic • N Not Hispanic

  7. Grade ID Grade Description • ‑1 None • 0 Kindergarten • 1 1 st Grade • 2 2nd Grade • 3 3rd Grade • 4 4th Grade • 5 5th Grade • 6 6th Grade • 7 7th Grade • 8 8th Grade • 9 9th Grade • 10 10th Grade • 11 11th Grade • 12 12th Grade • 13 Post H.S. Edu. • 14 Not in School • 15 Special

  8. Group Type Code Group Type (Delivery Mode) • 1 4‑H Community Clubs • 2 4‑H In‑School Clubs • 3 4‑H After‑School Clubs • 4 Military 4‑H Clubs • 5 4‑H Special Interest/Short‑Term Programs • 6 4‑H Overnight Camping Programs • 7 4‑H Day Camping Programs • 8 4‑H School Enrichment Programs • 9 Individual Study/Mentoring/Family Programs • 10 School‑Age Child Care Education Programs • 11 Instructional TVvideo Programs • 12 Not a Youth Group Unit

  9. Residence ID Residence • 1 Farm • 2 Rural • 3 10,000 ‑ 50,000 • 4 50,000 ‑ Suburb • 5 50,000 ‑ City

  10. Status Code Status • A Alumni • I Inactive • N New • R Return • T Terminate

  11. Race Code Race • WHT White WHB White and Black • BLK Black or African American • WAI White and Al or AN • AMI American Indian (AI) or Alaska Native (AN) • BAI Black and AI or AN • ASN Asian • WAS White and Asian • NHP Native Hawaiian or other Pacific Island • BAL Balance (Other Combination)

  12. True/False Codes • T =True or • F =False

  13. To filter by age: Fields – age / starting range-lowest age that you want to filter, ending range – upper age that you want to filter / OK, The list you generate will be all members that are between those ages.

  14. This member query would give you email address for all members, project advisors, activity leaders, general/organizational advisors and resource advisors. From here you can export the list to Excel.

  15. Open the program, select file/ member query / file / filter - The member query box pops up, from here you select the field that you want to filter. The information that you are looking at includes all the member information for all members and advisors.

  16. To filter email addresses: • Fields – category, the status or field value would be – A for activity advisor, C for Cloverbud member, • G for General/Org Leader, M for Members, O for Other, P for Project Advisor, R for Resource • Advisor, second filter would be Field – e-Mail / Category would be by range with the starting range being a and the ending range would be z

  17. Creating Multiple queries at once. • 1. Go to File|Member Query • 2. File|Filter • 3. In Fields All tab (on the left) Choose first filter option i.e. Status • 4. In Status Field Value By Value tab (on the right) Type in N or R or T (n=new, r= • returned, t= terminated)

  18. Go back under Fields All tab and choose Second filter option Category • 6. In Category Field Value By Value tab (on right) type in A or P or G or R (A= activity advisor, P= project advisor, G= general/organ. advisor, R= resource advisor

  19. Click OK. • 8. Your filter sequence is complete and has returned your results

  20. Fields Field Value Results • Last Name Null Members without a last name • First Name Null Members without a first name • Club Name Null Members without a club

  21. Category • A = Activity Advisor List of all Activity Advisors • C = Cloverbud member List of all Cloverbud members G = General / List of all General / Organizational Advisors • M = Member List of all Members • O = Other List of all Others • P = Project Advisor List of all Project Advisors • R = Resource Advisor List of all Resource Advisor • S = Special List of all Special

  22. Status • A = Alumni List of all Alumni Members • N = New List of all New Members • R = Returned List of all Returned Members • T = Terminate List of all Terminated Members

  23. Address, City, Zip Null Members without Address, City, Zip • School Null Members without School

  24. Gender • F = Female List of all Female advisors and members • M = Male List of all Male advisors and members

  25. Youth Leader • True List of all Youth leaders Birthday • Null Members without birthday date. Grade • put in grade List of all in a specific grade

  26. Age • put in age List of all that are a specific age Ethnic • Null Members without Ethnic data Race • Null Members without race data Residence • Null Members without Residence data

  27. Direct Volunteer • True List of all Direct Volunteers In Direct Volunteer • True List of all In Direct Volunteers Middle Manager • True List of all Middle Managers

  28. Phone • Null Members without Phone # Email • By range (1 – z, or @) List of all members and advisors with email addresses

  29. NOTE: The Member Query contains live data. Before making mass changes to the database, ALWAYS make a backup of the current data files in the Youth Enrollment Data folder. This can save you much time if a mistake is made in the mass edit process. The file can be restored and the mass edit re-applied correctly. Backups are always your best insurance against database corruption.

  30. Also: If you wish to export this information, you can go to Select all and go to File|Export|Ascii

  31. Use the DualListForm to select the fields to be exported. From the Source (on left) highlight the item i.e. MemberID and click on the >. This puts the item under Destination (on right). Only the items on the left are Exported. Note: If you wish to export All items can use the >> key to put all the items on the right under Destination.

  32. A Save as box comes up. Note: where the file will be Saved or Browse where you want it saved. Also give it a FileName. It will be saved as a *.txt file. Click Save.

  33. If you wish to export the information as a dbase file go to Select all and File|Export|Dbase and a DualListForm window comes up. Choose your Fields you wish to export by moving them to the Destination side. I.e. Highlight the item from the Source (on left) i.e. MemberID and click on the >. This puts the item under the Destination (on right). Only items on the left are Exported.

  34. A Save as box comes up. Note: where the file will be Saved. Give it a File Name. It will be saved as a *.dbf file. Click Save.

  35. Sample of Query 5th grade Females • 1. Go to File|Member Query • 2. File|Filter • 3. In Fields All tab (left) Choose 1st filter option i.e. Status • 4. In Status Field Value By Value tab (right) Type in N or R • 5. In Fields All tab (left) Choose 2nd filter option i.e. Gender

  36. 6. In Status Field Value By Value tab (right) Type in F • 7. In Fields All tab (left) Choose 3rd filter option i.e. Grade • 8. In Status Field Value By Value tab (right) Type in 5 • 9. By viewing the View Summary it verifies your filters:

  37. Click OK in the Summary and OK in the filter. Your return filter sequence is complete and has returned your results:

  38. Go to Select|Select all • 12. You can choose mailing labels right from here by going to Action|Mailing Labels. Choose your parameters and Preview and/or Print your labels. • 13. OR If you wish to export, go to File|Save As Dbase and save your file as shown before.

  39. To Further Filter after you had already created a filter • 1. If you have created a filter and want to filter further. Simply go back to File|Filter and your current Filter will be there (As Searched tab). • 2. Click on the Fields All tab and choose a field i.e. Category

  40. Click on the Fields All tab and choose a field i.e. Category In the ClubID Field Value By Value type in M • ***** Any field that is displayed on the Member Query Screen as a checkmark is considered a True/False field. Use the word True or a T to retrieve the

More Related