INTERMEDIATE ACCESS QUERIES, FORMS AND REPORTS Student Manual

1. INTERMEDIATE ACCESSQUERIES, FORMS AND REPORTSStudent Manual Created by Tim Battle & Robin McGrath 2007

2. Student Information Table Fields in the Student Information Table:

3. Student Grades Table Fields in the Student Grades Table:

4. Vendors Table Fields in the Vendors Table:

5. Relationships • Using your mouse, click on the Relationship button. • The tables appear side by side but are not connected. (If they don’t show, right click and “add table.”) • To build a relationship between the 2 student tables click on the Student ID field in the Students table and drag to the student ID field in the grades table.

6. The edit relationships table appears Note one-to-one on Relationship Type. 1 primary # to 1 primary #.Click on the 3 choices. Enforce Referential Integrity – enforce integrity of all fields. Cascade is the ripple effect – all fields are updated or deleted. Click on Create and save the changes. After joining tables, you can now query.

7. FORMS • Forms are used when you are adding new records in a database and the same information is needed for each record. • You don’t have to tab through your database to look for fields to update. • The form does this for you.

8. Forms Open the Vendors table and choose the wizardChoose the Vendors table to pull information from

9. Choose Purchase Order #, Vendor Name, Vendor G#, Item Ordered and Cost of Order fields

10. There are 4 available layouts Columnar – each field is listed down the page. Tabular – similar to a spreadsheet with no lines and each field is in its own column.

11. Available Layouts • Datasheet – just likeExcel, lines and all. Justified – more “form like” so you can lay out a form that fills the page. Choose the Columnar style and click on the Next button.

12. Review styles and Choose oneName the form

13. Choose Vendors formStart entering data (3) Describe Navigation keys Go back to the first record in the table. Go back to the previous record. Which record number is currently selected Go to the next record. Go to the last record. Create a new record.

14. Include arithmetic operators, for example: +, -, *, or / logical comparisons operators for example: <,=, >, <>, Null, Not Null Function: Access has numerous built-in functions, such as, Avg for averaging, Min which finds the smallest value, Max which finds the largest value. Criteria Expressions Include:

15. Commonly used Operators • = Equal to • <> Not Equal • Like Like • Not Like Not like • In (MI, TX) • or Admissions or Orientation • Between Between 2/2/2005 And 7/2/206 • > Greater than (ex: >5,000 • < Less than (ex: <5,000) • >= Greater than or equal to • <= Less than or equal to • Is Null Field is empty • Is Not Null Field is not empty

16. Example :( 1) In the Criteria Row using the = operative • Open: Vendor Query in Design View • In the first criteria row of the Cost of Order field, type “=10” • Run the query. • You should have two records showing $10.00

17. Example:(2)Using a single Criteria Row Match text by using = operative • In the first criteria row of the Vendor Name field type, =Steelcase Furniture • Run the query • All of your records in the Vendor Name field should have the name Steelcase Furniture • Text is not case sensitive

18. Example :( 3) In the Criteria Row using the < Less than operative • In the first Criteria row of the Cost of Order field, type “<300” • Run the query • You should have records showing amounts less than 300

19. Example :( 4) In the Criteria Row using the > greater than operative • In the first Criteria row of the Cost of Order field, type “>300” • Run the query • You should have records showing amounts greater than 300

20. Example:(5) Using a single Criteria Row Not Equal <> operative. • In the first Criteria row of the Cost of Order field, type “<>500”. • Run the query. • You should have not have records showing amounts of 500. • Operative is great for eliminating specified text or numbers

21. Example:(6)In the Criteria Row using the Equal to and less than operative <= • In the first Criteria row of the Cost of Order field, type “<=500”. • Run the query. • You should have records showing amounts of 500 and amounts less than 500.

22. Example:(7)Using a single Criteria Row(Between: looking for a range) • In the first criteria row of the Cost of Order field, type “between 100 and 300”. • Run the query. • You should have records showing amounts between 100 and 300

23. Example:(8)Using a single Criteria Row(Not: an excluding operator) • In the first criteria row of the Vendor Name field, type “not Steelcase Furniture” • Run the query. • You should have records that exclude the name Steelcase Furniture

24. Example:(9) Using a single Criteria Row (In: an inclusive operator) Matches any one of the values in a list • In the first Criteria row of the Vendor Name field, type In (Steelcase Furniture, Dell Computers) • Your selections are incased in parenthesis • Run the query. • You should have records that include the names Steelcase Furniture and Dell Computers

25. Example:(10)Using a single Criteria Row(Like: Matches exact values) • In the first criteria row of the Vendor Name, type “like Steelcase Furniture” • Run the query. • You should have 5 records showing the name Steelcase Furniture

26. Example:(11) Using a single Criteria Row Like with * : Matches any value that starts with your selection • In the first Criteria row of the Vendor Name field, type “like S*” • Run the query. • You should have all records that start with the letter S.

27. Mathematical operators • Mathematical operators are used to perform calculations on numerical fields. • There are four mathematical operators: + (Addition), - (Subtraction), / (Division), * (Multiplication) • Calculations can be performed in a query within an expression, or by applying an operator to an existing field. • For example: Expression Name: [field] + 2

28. Rules for Creating an equation: • Name the new field in your expression or Access will name it. Example: Expr1 • The new field is followed by a colon. • Add the field that you want to use in the expression • Fields used in an expression, must be encased within square brackets. • The expression is followed by a mathematic operator • Percentages must be written in decimal form.

29. Demonstration: Math Operators performing Calculations

30. Calculating Fields

31. Syntax for IIf function • The general form of an IIf statement has 3 parts.  IIf([expression], “truepart”, “falsepart”) • Where  expression contains the criteria that you want to evaluate. It must be either True or False. • Truepart is what you want to see when the expression is True. • Falsepart  is what you want to see when the expression is False

32. Example: IIf Statement

33. Format • Access has functions that allow a user to change a calculated value • The Format function also allows you to take a standard pattern and create your own pattern • An example of a Format function:Format (value, pattern)

34. Rules for using Format in an equation: • Name the new field or Access will name it for you • The new field name is followed by a colon. • Add a space after the colon and type the word: Format • Your expression will be encased in parenthesis • Add the field that you want to use in the expression • Fields used in the expression, must be encased within square brackets. • The expression is followed by a mathematic operator • Percentages must be written in decimal form. • Add a comma after the value that you are factoring • Patterns must be encased within quotes, choose your pattern • Add your closing parenthesis

35. Format Example • New field name: Format([Cost of Order], “Currency”) takes a value like 35.6 and formats it into $35.60

36. Formatting a new Field

37. Using Now() • Returns the current date and time • Same process for creating an expression • Example: Date and Time: Now() Run query

38. Format Pattern Examples:

39. Parameter Query • Microsoft Access allows you to restrict the set of records that a query returns. A query that prompts you for a criteria is called a parameter query • A parameter query displays its own dialog box that prompts you for information • You can use parameter query as the basis for forms or reports

40. How to Create a Query with one Parameter • Type the following line in the Criteria cell under the Vendors Name field: [Company Name] • Note that the expression that you enter must be enclosed in square brackets. • On the Query menu, click Run. When you are prompted, type the company name, and then click OK to view the results of the query. (Company names: Steelcase Furniture, Dell Computers, Rolm and Staples Office Supplies)

41. Single Parameter Query Parameter box: Type in Company Name Run query

42. How to Create a Query with Two or More Parameters • Type the following line in the Criteria cell for the Cost of Order field. • Between [Lowest Amount] And [Highest Amount] • On the Query menu, click Run. When you are prompted for the Lowest Paid, type 200, and then click OK. When you are prompted for the Highest Paid, type 500, and then click OK to view the results of the query. Note that the query returns only records amounts paid between 200 and 500.

43. Two Parameters Insert lowest amount Insert highest amount Run query

44. Totals Row • The Totals button reveals the Totals row in the Query Design View.  A select query can use one of several functions in the Totals row to analyze data. • Group By - to group the results on the uniqueness of the selected field • Sum - add up the values in this field • Avg  - average the values • Min  - find the smallest value • Max  - find the largest value • Count - how many records are the same for this field • StDev, Var - Statistical functions (Unlikely to use)  • First, Last - to find the first or last record in the datasheet after the query is run. Useful for large datasheets when Min or Max are not appropriate. • Expression - when the field is calculated using another field that is an aggregate function • Where - for fields that have criteria for restricting which records are included • Generally, Sum, Avg, Min, and Max are used with values that are expressed in numerical terms. Count and Group By are basically used with text

45. Rules for using the Total Button in a Query: • Include the field that you are "totaling" in the design grid • Include the fields you need to group, sort, or filter. • Check the box for Show only for the totaled field and for the fields you want to group on. • Do not include any other fields. • You can not use the * to include all fields. • The order of the fields is important for grouping and sorting

46. Totals Row Click on the Totals button to add a Total line Click on the drop down box Run query

47. Use the Report Wizard and then customize the report Choose the Vendor table and these Fields. Choose Reports and the Wizard

48. Grouping, Sorting, Summary Options Select one grouping level and Next Choose another level of sorting and Summary Options

49. Summary Options Choose Sum and Details and Summary options. Click on OK and Next. In your office you can play with the average, minimum and maximum features. If you choose summary only, no detail will be shown except the summary amount

50. Choose Layout and Style