4HPlus – Retrieving Information

1. 4HPlus – Retrieving Information March 2008

2. Retrieving Information The real value of any information based software is in the data and reports that can be produced.

3. Today’s Program • 4HPlus Data Retrieval Options • Standard Reports • Using SQL for Specialized Reports • Creating Newsletter Labels • Using SQL to Create Mailing Labels • User Hints and Tips • Resources Available

4. Data Retrieval OptionsStandard Reports • Standard reports result in a printed report, with no preview option. • Member and leader reports can be accessed under (Reports/Utilities) on the member and leader registration screens. • Print list buttons for support data (such as achievements and clubs) are located on the screen for that specific support data category.

5. Data Retrieval OptionsStandard Reports • Many report options are available: • Achievements • Clubs • Groups • Leaders • Members • Projects • ES-237 • And more…

6. Data Retrieval OptionsSQL • SQL stands for Structured Query Language. It is a language that can be used to create a wide variety of data requests. • 4HPlus uses check boxes and radio buttons to build an SQL request for the user. • Complex searches are possible in a relatively simple manner.

7. Data Retrieval OptionsSQL • SQL – specialized requests that result in data output to the screen, printer, or file. • Literally endless options for retrieving sub-sets of data.

8. Data Retrieval OptionsSQL • The foundational assumption with SQL is that there is a desire to select out a group of Members or Leaders, and related data about them. • SQL is accessed from the main Member and Leader enrollment screens. • Members and Leaders cannot be combined in an SQL.

9. Data Retrieval OptionsSQL • Count - displays the number of records that match the request. • List - displays the total number of matches and a listing of Members or Leaders that match the request. • Individual records can be directly accessed by clicking on the name from the list. • It is possible to move through the list of records by using the Next/Prior buttons on main Member and Leader registration screens. • The list can be printed.

10. Data Retrieval OptionsSQL • Labels – produces mailing labels for the matching records. • Email – takes advantage of an internal e-mailing capability to send e-mails to the group of Members or Leaders selected. • Missing Email Labels – counterpart to the e-mail option. Creates mailing labels matching the request criteria for those without e-mail.

11. Data Retrieval OptionsSQL • Report – generates a preview, print report, or file of the selected fields in the order chosen for the records that match the request. • Mail Merge – works similar to the report option. Produces a data file that can be used for mail merging or importing to a spreadsheet or database.

12. Data Retrieval OptionsSQL • Queue – displays a list of saved SQL requests. Selecting one of the request list buttons will reprocess the request as if you had entered it manually. • Clear – Erases all stored requests.

13. Data Retrieval OptionsSQL Get to know SQL as your new best friend!

14. Data Retrieval OptionsNewsletter Labels • Newsletter labels are accessed from the Utilities screen. From the main page: Goto/Utilities. • Use this feature for labels when you want to create labels for all members, and/or leaders, and/or parents in your 4HPlus data.

15. Data Retrieval OptionsNewsletter Labels • Options are available: • One label per Id, family, or address. • Possible to remove labels for e-mail accessible members, leaders, and/or parents. • There are choices for the label first line, such as “4-H News for Everyone at, or To the 4-H Family at.”

16. Data Retrieval OptionsNewsletter Labels • Options are available: • There are choices for the label casing. • Bar codes can be printed on the labels. • Labels can be sorted by zip code, last name, or Id.

17. Data Retrieval OptionsNewsletter Labels • Options are available: • Label size options are available – Avery 5160 is the default. • The data output can be in a number of forms: • Count • Screen • Printer • Mail merge file

18. Data Retrieval OptionsSQL Labels • SQL is accessed from the Member or Leader screens. • Members and Leaders cannot be combined in an SQL. • Select Labels from the SQL Request drop down.

19. Data Retrieval OptionsSQL Labels • Make selections using the check boxes and radio buttons. • Select Process in the Connector box and the label options will appear.

20. Data Retrieval OptionsSQL Labels • Again, there are options: • Label casing • Label sorting • One label per member, family, or address • Output to screen, printer, or file • Label size options • Printing bar codes on labels • Partial label sheets can be used.

21. Hints and Tips • Special notes for those using Peer to Peer set-up: • Read and Write computers: remember to select Write Data before closing down 4HPlus if changes were made to the 4HPlus data. This will assure the Read Only users have access to the most current data. • Read Only computers: select Read Data every time 4HPlus is opened, so the most current dataset is accessed from the shared data pool.

22. Hints and Tips • Practice using SQL .. Be brave! It gets easier every time. • Remember to select the Process button to perform desired functions. • If information is entered into the target string box, press the tab or enter key to continue to the connector box.

23. Hints and Tips Get to know SQL as your new best friend!

24. Data Retrieval Resources • New support materials have been added to the 4HPlus webpage: http://www.uwex.edu/ces/4h/resources/mgt/4hplus.cfm • 4HPlus Retrieving Information PowerPoint • Reports • SQL Tutorial • Creating Labels

25. For Assistance … • State 4HPlus Support Team • Nancy Eisele, Information Processing Consultant, nancy.eisele@ces.uwex.edu, 715.362.5655 • Jane Marquardt, Office Manager, jane.marquardt@ces.uwex.edu, 608.262.2391 • Donna Menart, Associate State Program Director, donna.menart@ces.uwex.edu, 920.565.4638