|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-01 : 14:49:29
|
| Aubrey writes "I noticed a difference between the performance of the Access Transform statement and the Crosstab stored procedure. The Transform query will provide a total column that will contain the sum of the values in each row. Also, the count function will correctly count the number of occurrences in the cross tab column, but that count, which corresponds to the row value in the total column, will occur in every cell of the row. The volunteer application I am working on is for a Boy Scout Troop. They wish to keep track of attendance at their events by Scout. This information is used for rank advancement and role assignments.The result of the Transorm statement is precisely what they need, whereas the result of the stored proc would seem to require more logic.If I am not setting this up right, please let me know. You can copy the following text to a separate file, save with a .csv extension and open with Excel to see the source table, and the results of the Access Transform query and the stored procedure:Access Crosstab Query Wizard,,,,TRANSFORM Count(VW_EVENT_ATTENDANCE.EVENT_DATE) AS CountOfEVENT_DATESELECT VW_EVENT_ATTENDANCE.FullName, Count(VW_EVENT_ATTENDANCE.EVENT_DATE) AS [Total Of EVENT_DATE]FROM VW_EVENT_ATTENDANCEGROUP BY VW_EVENT_ATTENDANCE.FullNamePIVOT VW_EVENT_ATTENDANCE.DESCR;FullName,Total Of EVENT_DATE,5 Mile Hike,Buffalo Trails Summer Camp,Car Wash"A,Kevin",1,,1,"B,Ben",3,1,1,1"C,Thomas",3,1,1,1"C,Adrian",1,,1,"G,Josh D.",2,,1,1"G,Aaron",1,,,1"H,Brian",2,,1,1"H,Roland, Jr.",1,,,1"H,James",3,1,1,1"K,Nicholas",2,,1,1"L,Derek",2,,1,1"M,Christian",3,1,1,1"P,Michael",1,,,1"P,Zachary",2,,1,1"P,Joshua",2,,1,1"R,Richie",3,1,1,1"S,Bryan",2,1,1,"S,Alex",3,1,1,1"V,Marc",3,1,1,1"Y,William",2,1,1,,,,,EXECUTE crosstab ,,,,'SELECT FullName FROM VW_EVENT_ATTENDANCE ,,,,GROUP BY FullName',,,,",'Count(FullName)'",,,,",'DESCR'",,,,",'VW_EVENT_ATTENDANCE'",,,,FullName,5 Mile Hike,Buffalo Trails Summer Camp,Car Wash,"A,Kevin",1,1,1,"B,Ben",3,3,3"C,Thomas",3,3,3"C,Adrian",1,1,1"G,Josh D.",2,2,2"G,Aaron",1,1,1"H,Brian",2,2,2"H,Roland, Jr.",1,1,1"H,James",3,3,3"K,Nicholas",2,2,2"L,Derek",2,2,2"M,Christian",3,3,3"P,Michael",1,1,1"P,Zachary",2,2,2"P,Joshua",2,2,2"R,Richie",3,3,3"S,Bryan",2,2,2"S,Alex",3,3,3"V,Marc",3,3,3"Y,William",2,2,2Scource data:,,,SELECT * FROM VW_EVENT_ATTENDANCE ,,,EVENT_DATE,DESCR,FullName,4/16,Car Wash,"S,Alex",4/16,Car Wash,"B,Ben",4/16,Car Wash,"C,Thomas",4/16,Car Wash,"G,Josh D.",4/16,Car Wash,"G,Aaron",4/16,Car Wash,"H,Brian",4/16,Car Wash,"H,James",4/16,Car Wash,"M,Christian",4/16,Car Wash,"P,Joshua",4/16,Car Wash,"K,Nicholas"4/16,Car Wash,"R,Richie"4/16,Car Wash,"L,Derek"4/16,Car Wash,"P,Zachary"4/16,Car Wash,"P,Michael"4/16,Car Wash,"H,Roland, Jr."4/16,Car Wash,"V,Marc"7/9,Buffalo Trails Summer Camp,"S,Alex"7/9,Buffalo Trails Summer Camp,"B,Ben"7/9,Buffalo Trails Summer Camp,"C,Thomas"7/9,Buffalo Trails Summer Camp,"Y,William"7/9,Buffalo Trails Summer Camp,"C,Adrian"7/9,Buffalo Trails Summer Camp,"G,Josh D."7/9,Buffalo Trails Summer Camp,"H,Brian"7/9,Buffalo Trails Summer Camp,"A,Kevin"7/9,Buffalo Trails Summer Camp,"H,James"7/9,Buffalo Trails Summer Camp,"M, Christian"7/9,Buffalo Trails Summer Camp,"P, Joshua"7/9,Buffalo Trails Summer Camp,"K,Nicholas"7/9,Buffalo Trails Summer Camp,"R,Richie"7/9,Buffalo Trails Summer Camp,"L,Derek"7/9,Buffalo Trails Summer Camp,"P,Zachary"7/9,Buffalo Trails Summer Camp,"S,Bryan"7/9,Buffalo Trails Summer Camp,"V,Marc"5/7,5 Mile Hike,"S,Alex"5/7,5 Mile Hike,"B,Ben"5/7,5 Mile Hike,"C,Thomas"5/7,5 Mile Hike,"Y,William"5/7,5 Mile Hike,"H,James"5/7 |
|
Crosstab stored procedure by robvolk