Transcript Welcome To
Welcome To... ReportSmith & ADP PC/Payroll For Windows Meeting of the Minds 1999 Welcome To... Database Grouping CheckView Tables & SUM(DISTINCT ) Meeting of the Minds 1999 Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting [email protected] Meeting of the Minds 1999 Challenge CheckView Tables Many Records For Each Employee Check One Code Per Record Can Not Add Up Most Amounts Meeting of the Minds 1999 CheckView Tables Architecture One Record Per Employee Check Code Meeting of the Minds 1999 CheckView Tables Architecture One Record Per Employee –CompanyCode –File# –SocialSecurity# Check Code Meeting of the Minds 1999 CheckView Tables Architecture One Record Per Employee Check –CheckViewChkSeq# –CheckViewPayroll# –CheckViewWeek# –CheckViewYear# Code Meeting of the Minds 1999 CheckView Tables Architecture One Record Per Employee Check Code –CheckViewDedCode –CheckViewDedAmt Meeting of the Minds 1999 CheckView Tables Vertical Data Each Code And Amount Pair Is On A Separate Record Meeting of the Minds 1999 CheckView Tables Vertical Data COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CODE FILE# SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDCODE DEDAMT MOTM 3 178341321 193 1 50 1998 C $2.00 MOTM 3 178341321 193 1 50 1998 D $93.00 MOTM 3 178341321 193 1 50 1998 E $36.70 MOTM 3 178341321 193 1 50 1998 J $18.31 MOTM 3 178341321 193 1 50 1998 K $0.77 MOTM 3 178341321 193 1 50 1998 Y $105.00 Meeting of the Minds 1999 CheckView Tables Horizontal Data All The Other Columns Have The Same Values On Each Record Meeting of the Minds 1999 CheckView Tables Horizontal Data COMPANY CODE MOTM MOTM MOTM MOTM MOTM MOTM FILE# 3 3 3 3 3 3 SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDCODE DEDAMT NETPAYAMT GROSSPAYA 178341321 193 1 50 1998 C $2.00 $326.15 $747.20 178341321 193 1 50 1998 D $93.00 $326.15 $747.20 178341321 193 1 50 1998 E $36.70 $326.15 $747.20 178341321 193 1 50 1998 J $18.31 $326.15 $747.20 178341321 193 1 50 1998 K $0.77 $326.15 $747.20 178341321 193 1 50 1998 Y $105.00 $326.15 $747.20 Meeting of the Minds 1999 CheckView Tables COMPANY CODE MOTM MOTM MOTM MOTM MOTM MOTM FILE# 3 3 3 3 3 3 SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDCODE DEDAMT NETPAYAMT GROSSPAYA 178341321 193 1 50 1998 C $2.00 $326.15 $747.20 178341321 193 1 50 1998 D $93.00 $326.15 $747.20 178341321 193 1 50 1998 E $36.70 $326.15 $747.20 178341321 193 1 50 1998 J $18.31 $326.15 $747.20 178341321 193 1 50 1998 K $0.77 $326.15 $747.20 178341321 193 1 50 1998 Y $105.00 $326.15 $747.20 Meeting of the Minds 1999 Solution Database Grouping Records Are Compressed Data is Extracted And/Or Summarized Using Derived Fields And Summary Functions Meeting of the Minds 1999 Database Grouping Records Are Compressed Using Unique Values Of Fields Include In Report Value Only Meeting of the Minds 1999 Database Grouping Always Include In Report CompanyCode File# SocialSecurity# CheckViewChkSeq# CheckViewPayroll# CheckViewWeek# CheckViewYear# Meeting of the Minds 1999 Database Grouping Always Include In Report This Will Result In One Record Per Employee Check Meeting of the Minds 1999 Database Grouping Always Include In Report This Will Result In One Record Per Employee –CompanyCode –File# –SocialSecurity# Check Meeting of the Minds 1999 Database Grouping Always Include In Report This Will Result In One Record Per Employee Check –CheckViewChkSeq# –CheckViewPayroll# –CheckViewWeek# –CheckViewYear# Meeting of the Minds 1999 Database Grouping Always Include In Report Before Database Grouping COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CODE PAYROLL# WEEK# YEAR# FILE# SECURITY# CHKSEQ# MOTM 3 178341321 193 1 50 1998 MOTM 3 178341321 193 1 50 1998 MOTM 3 178341321 193 1 50 1998 MOTM 3 178341321 193 1 50 1998 MOTM 3 178341321 193 1 50 1998 MOTM 3 178341321 193 1 50 1998 Meeting of the Minds 1999 Database Grouping Always Include In Report After Database Grouping COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CODE FILE# SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# MOTM 3 178341321 193 1 50 1998 Meeting of the Minds 1999 Database Grouping Code And Amount Must Be Query Only Or The Values Will Change How The Records Are Compressed Only Used In SQL Derived Fields Meeting of the Minds 1999 Database Grouping All Other Amounts Should Be Query Only Only Used In SQL Derived Fields Meeting of the Minds 1999 Database Grouping Any Other Fields Can Be Include In Report Or Query Only Each Record Will Have The Same Value And Will Not Effect The Database Grouping Meeting of the Minds 1999 Solution Summarized Data Using Derived Fields And Summary Functions Add Data Up Compressed Vertical –Sum All Values SUM(Column Name) Meeting of the Minds 1999 Data is Summarized Summarized Data Add Up All Deduction Amounts SUM(CheckViewDedAmt) COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW TOTAL CODE FILE# SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDUCTIONS MOTM 3 178341321 193 1 50 1998 $255.78 Meeting of the Minds 1999 Solution Extracted Data Using Derived Fields And Summary Functions Extract One Occurrence Of The Duplicated Values –Sum Only Unique Values SUM(DISTINCT Column Name) Meeting of the Minds 1999 Data is Summarized Extracted Data Add Up Only Unique Gross Pay Amounts SUM(DISTINCT CheckViewGrossPayA) COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW TOTAL GROSSPAY CODE FILE# SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDUCTIONS AMOUNT MOTM 3 178341321 193 1 50 1998 $255.78 $747.20 Meeting of the Minds 1999 Data is Summarized Summary Functions COMPANY SOCIAL CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CHECKVIEW CODE FILE# SECURITY# CHKSEQ# PAYROLL# WEEK# YEAR# DEDCODE DEDAMT GROSSPAYA MOTM 3 178341321 193 1 50 1998 C $2.00 $747.20 MOTM 3 178341321 193 1 50 1998 D $93.00 $747.20 MOTM 3 178341321 193 1 50 1998 E $36.70 $747.20 MOTM 3 178341321 193 1 50 1998 J $18.31 $747.20 MOTM 3 178341321 193 1 50 1998 K $0.77 $747.20 MOTM 3 178341321 193 1 50 1998 Y $105.00 $747.20 T ota ls $255.78 $747.20 Meeting of the Minds 1999 Conclusion Use Database Grouping To Get One Record Per Check Remember What You Include In Your Report Will Be Used To Compress The Records Meeting of the Minds 1999 Conclusion Always Include In Report CompanyCode File# SocialSecurity# CheckViewChkSeq# CheckViewPayroll# CheckViewWeek# CheckViewYear# Meeting of the Minds 1999 Conclusion Summarize Vertical Data Use Sum( ) To Add Up Compressed Vertical Data Meeting of the Minds 1999 Conclusion Extract Horizontal Data Use Sum(DISTINCT ) To Extract One Occurrence Of The Duplicated Values Meeting of the Minds 1999 Presented By CharlesCook.com Specializing In ReportSmith Training & Consulting [email protected] Meeting of the Minds 1999