Introduction to Query Part 3

1. Introduction to QueryPart 3

2. Query Topics • Report Breaks • Totals and subtotals • Forms of Output • Detail verses summary • Output Types • Data base file

3. Report Breaks • Totals and Subtotals • 0 – Final Total • 1 – 6 Total Levels

4. Report Breaks • 1100 John Smith $ Amount Purchased Pig Feed 1290 Break Pig Feed 300 Total 1590 Level 2 Chemicals 2000 Chemicals 2750 Total 4750 Level 2 Customer Total 6340 Level 1 • 1300 Jimmy Jones Pig Feed 505 Pig Feed 900 Total 1405 Level 2 Chemicals 900 Total 900 Level 2 Customer Total 2305 Level 1 Final Totals 8645 Level 0

5. Report Breaks Define Report Breaks Type break level (1-6) for up to 9 field names, press Enter. (Use as many fields as needed for each break level.) Break Sort Level Prty Field Text Len Dec 1 10 T02.TDCNO Customer Number 7 0 T03.CSLKNM Customer Lookup Name 25 2 20 T02.TDITEM Item Number 14 T02.TDDESC Description 35 T02.TDQTY Quantity 9/4 11 4 TOTAL tdamt*-1 9 2 Bottom F3=Exit F5=Report F10=Process/previous F11=Display names only F12=Cancel F13=Layout F18=Files F23=Long comment

6. Report Breaks Format Report Break Break level . . . . . . . : 0 Type choices, press Enter. (Type &field in text to have break values inserted.) Suppress summaries . . . . N Y=Yes, N=No Break text . . . . . . . . FINAL TOTALS Level Field 1 T02.TDCNO 2 T02.TDITEM F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files F23=Long comment

7. Report Breaks Format Report Break Break level . . . . . . . : 1 Type choices, press Enter. (Type &field in text to have break values inserted.) Skip to new page . . . . . N Y=Yes, N=No Suppress summaries . . . . N Y=Yes, N=No Break text . . . . . . . . &t02.tdcno Level Field 1 T02.TDCNO 2 T02.TDITEM F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files F23=Long comment

8. Report Breaks Format Report Break Break level . . . . . . . : 2 Type choices, press Enter. (Type &field in text to have break values inserted.) Skip to new page . . . . . N Y=Yes, N=No Suppress summaries . . . . N Y=Yes, N=No Break text . . . . . . . . &t02.tditem Level Field 1 T02.TDCNO 2 T02.TDITEM F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files F23=Long comment

9. Report BreaksTips • Break levels will normally match the sort fields • Suppress summaries – N • Will print totals • Suppress summaries – Y • Will NOT print totals

10. Forms of Output • Detail • Prints all detail lines • Summary • Summarizes and prints ONLY fields with a break level or fields that are totaled using the report summary functions.

11. Forms of Output Select Output Type and Output Form Type choices, press Enter. Output type . . . . . . . . . . . 2 1=Display 2=Printer 3=Database file Form of output . . . . . . . . . .2 1=Detail 2=Summary only Line wrapping . . . . . . . . . . N Y=Yes, N=No Wrapping width . . . . . . . . . Blank, 1-378 Record on one page . . . . . . . N Y=Yes, N=No F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files

12. Forms of Output • 1100 John Smith $ Amount Purchased Total Feed 1590 Level 2 Total Chemicals 4750 Level 2 Customer Total 6340 Level 1 • 1300 Jimmy Jones Total Feed 1405 Level 2 Total Chemicals 900 Level 2 Customer Total 2305 Level 1 Final Totals 8645 Level 0

13. Forms of Output Define Report Breaks Type break level (1-6) for up to 9 field names, press Enter. (Use as many fields as needed for each break level.) Break Sort Level Prty Field Text Len Dec 1 10 T02.TDCNO Customer Number 7 0 T03.CSLKNM Customer Lookup Name 25 2 20 T02.TDITEM Item Number 14 T02.TDDESC Description 35 T02.TDQTY Quantity 9/4 11 4 TOTAL tdamt*-1 9 2 Bottom F3=Exit F5=Report F10=Process/previous F11=Display names only F12=Cancel F13=Layout F18=Files F23=Long comment

14. Forms of Output2-Summary Tips • Fields that are totaled with the summary function will automatically print • Quantities and Dollar Amounts • Use the break levels to print other fields • Ex. if the item no needs to print on the report then select it as a break level

15. Output Types Select Output Type and Output Form Type choices, press Enter. Output type . . . . . . . . . . . 3 1=Display 2=Printer 3=Database file Form of output . . . . . . . . . . 1 1=Detail 2=Summary only Line wrapping . . . . . . . . . . N Y=Yes, N=No Wrapping width . . . . . . . . . Blank, 1-378 Record on one page . . . . . . . N Y=Yes, N=No F3=Exit F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files

16. Output Types Define Database File Output Type choices, press Enter. (The printed definition shows the output file record layout.) File . . . . . . . . . mthlysales Name, F4 for list Library . . . . . . karenqry Name, F4 for list Member . . . . . . . . *FILE Name, *FIRST, *FILE, F4 for list Data in file . . . . . 1 1=New file, 2=Replace file 3=New member, 4=Replace member 5=Add to member For a new file: Authority . . . . . *LIBCRTAUT *LIBCRTAUT, authorization list name, *CHANGE, *ALL, *EXCLUDE, *USE Text . . . . . . . . Print definition . . . N Y=Yes, N=No F3=Exit F4=Prompt F5=Report F10=Process/previous F12=Cancel F13=Layout F18=Files

17. Output TypesTips • Never save the data file to your AgVantage data library COxDATA • Create the query using option 1 – New file then change query to use option 2 – Replace File

18. Exercises 4 & 5 & 6 See Handouts

19. Thank You for attending!