D1 Session 7 Reports & Parameter Queries

1. D1 Session 7Reports & Parameter Queries

2. Reports Overview • Used for printed output • Can be based on one or more tables or queries • Report design similar to form design • Use Wizard for initial design • Modify in Report Designview

3. Report Sorting & Grouping • Select options in the Wizard • Use Sorting and Grouping dialog box to add or modify existing report • Each group has a header and footer section • Fields in these sections will be displayed once for each group

4. Calculations in Reports • Use unbound text boxes for calculations • (can use same method on forms) • Use the footer sections for summary calculations • Example calculations =[MtValPerBottle]-[CostPrice] =sum([MtValPerBottle]) =count([ProductID])

5. Parameter Queries • Allow the user to enter criteria when the query is run • A [prompt] entered in the criteria row will display a message asking the user for a value for that field • Note: Make sure the prompt is not the same as any of the field names in your query!

6. Expressions in Queries • As criteria • Enter the expression in the criteria row • E.g. <10 • To create calculated fields • Enter the expression in the field row • Add a label • E.g. Profit:[MtValPerBottle]-[CostPrice] • String concatenation (calculated field) • E.g. [ContFname]&” “&[ContSname]

7. IIf function • Displays a value in a calculated field depending on a condition

8. IIf function – single condition Syntax: IIf (test_condition,value_if_true,value_if_false) Example: Origin: IIf([Country]="UK","British","Overseas") Note: Two possible outcomes, one IIf

9. IIf function – multiple conditions The syntax is the same but the value_if_false tests the second condition by using another IIf function Example: Price Range: IIf([MktValPerBottle]<5,"Economy", IIf([MktValPerBottle]<10,"Premium","Quality")) Note: Three possible outcomes, two Iifs What if there were four possible outcomes? Is there another way to write the conditions?