Access Patterns

1. Access Patterns we have seen

2. We review some of the common patterns we have used. • IMPORTANT NOTE: SQL is given for informational purpose only. We have not covered SQL but used the Query Builder instead.

3. Table • ID A B C • 1 a1 b1 5 • 2 a1 b1 8 • 3 a1 b2 7 • 4 a1 b2 2 • 5 a1 b2 1 • 6 a2 b1 7 • 7 a2 b1 8 • 8 a3 b2 0

4. What we want: summing for distinguished column of another column • A SumOfC • a1 23 • a2 15 • a3 0

5. SQL query SELECT Table1.A, Sum(Table1.C) AS SumOfC FROM Table1 GROUP BY Table1.A;

6. Query Builder Manipulation • Create Query • Choose Table1 • Select column A • Select column C • Totals (GroupBy default) • Sum for column C

7. Table • ID A B C • 1 a1 b1 5 • 2 a1 b1 8 • 3 a1 b2 7 • 4 a1 b2 2 • 5 a1 b2 1 • 6 a2 b1 7 • 7 a2 b1 8 • 8 a3 b2 0

8. What we want: Counting for distinguished column • B CountOfB • b1 4 • b2 4

9. SQL SELECT Table1.B, Count(Table1.B) AS CountOfB FROM Table1 GROUP BY Table1.B;

10. Query Builder Manipulation • Create Query • Select Table1 • Select column B • Select column B • Totals (GroupBy) • For second column B choose count

11. Table2 (DebateResults) • ID winner loser forced • 1 1 2 1 • 2 2 1 2 • 3 1 3 3 • 4 3 2 2 • 5 4 1 4 • 6 1 4 4 • 7 1 5 0

12. What we want: count wins for each debater • winner CountOfwinner • 1 4 • 2 1 • 3 1 • 4 1

13. SQL • SELECT Table2.winner, Count(Table2.winner) AS CountOfwinner • FROM Table2 • GROUP BY Table2.winner;

14. Query Builder Manipulation • Create Query • Choose Table2 • Select winner column twice • Totals (GroupBy) • For second winner column: Count

15. Table2 (DebateResults) • ID winner loser forced • 1 1 2 1 • 2 2 1 2 • 3 1 3 3 • 4 3 2 2 • 5 4 1 4 • 6 1 4 4 • 7 1 5 0

16. What we want: count faults • FaulterCountOfFaulter • 1 2 • 2 1 • 5 1

17. Add calculated field for Faulters: subquery • What do we want for the subquery?

18. Create Faulter column from Loser column • Faulter forced • 2 1 • 1 2 • 1 4 • 5 0

19. SQL • SELECT Table2.loser AS Faulter, Table2.forced • FROM Table2 • WHERE (((Table2.loser)<>[forced]));

20. Query Builder Manipulation • Create Query • select loser column; rename to Faulter; condition <>[forced] • select forced column (for checking result) • name subquery: Faults

21. Reminder: What we want • FaulterCountOfFaulter • 1 2 • 2 1 • 5 1

22. SQL • SELECT Faults.Faulter, Count(Faults.Faulter) AS CountOfFaulter • FROM Faults • GROUP BY Faults.Faulter;

23. Query Builder Manipulation • Create Query • Choose subquery Faults • Select Faulter column twice • Totals (GroupBy) • select Count for second