Mastering Advanced Queries in MS Access

1. Hele-Mai Haav: CSC230-Spring*03 Advanced Queries in MS Access

2. Criteria This is a constraint based on Boolean Algebra Truth Table A B A and B A or B T T T T T F F T F T F T F F F F

3. Boolean Algebra • The Database System uses Boolean algebra to evaluate conditions that consist of multiple clauses. • The clauses are connected by these operators: AND, OR, NOT

4. Examples a= 3, b=-1,c=2 (a > 4) and (b < 0) (a > 4) or (b < 0) F T F T F T Not (b < 0) T F

5. QBE (Category= “Dog”) AND (Color Like “*Yellow*”) AND (DateBorn>#6/1/98#)

6. Complex Queries • All dogs that are yellow or white and born after June 1 • ? • (dogs AND yellow) OR (white AND born after June 1) • (dogs) AND (yellow OR white) AND (born after June 1)

7. Totals and subtotals • Aggregation functions (Sum, Avg; Min..) • They operate across several rows of data and return one value

8. Totals and subtotals • Combining row-by-row calculations with aggregation • Use Where to limit the rows used for the total (e.g. Sum). Only the final total will be displayed in the result • Subtotals for each category can be created using Group By

9. Cross Tabulation • Crosstab (in MS Access) is Group By operation that works with two groups