Presentation Transcript

1. _____________________________ _____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
2. _____________________________ _____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

3. Introduction to Microsoft Access

   Training Program By Alan Meister
4. Course Objectives To present a general overview of Microsoft Access. How to create a new database with relational tables. How to build queries to extract desired data. How to create reports to reflect desired objective. To permit a “hands on” experience with Microsoft Access.
5. Introduction to Access Distinguishing Features of Access Display Features of Access User Interface Development of Databases in Access Design Process of Access Database Objects
6. I. DistinguishingFeatures of Access
7. Advantages of Databases What is a Database? A database is an electronic means of collecting related digital data. A database is made up of one or more tables where each row in the table is called a record. A record lists certain characteristics of something. Each column in the table is called a field. A field is a single item of information appearing in a record with the same type of information about that item.
8. What is Microsoft Access Access is a specialized self contained relational database management system. Access has its own user interface and data storage program. Access is a tool that can transform data into actionable information. Most databases are just a file or folder that data is dumped in ad then special programs are used to retrieve the data. Data by itself does not automatically produce information.
9. Create or Template Access provides “prepared" database templates for use in part or whole by the user. With the prepared database templates you are limited with the self contained characteristics of that database template. Modifications are possible but takes time to "recast" the template into your design needs Time taken for modifications may take longer than creating from scratch. Template programs may be better for study and borrowing objects than using whole program.
10. Access or Excel Use Access if your need to organize data in a relational data structure. If not use Excel. Access will answer questions faster and more precise from a relational structure. Access gives more accuracy in presentation and management of data. Use Excel if you do not need a relationship to any other data from one file to another. A spreadsheet relies on some sort of order of columns. Databases do not. Navigating large spreadsheet is difficult with the increase of columns. Calculations and computations are difficult to process. Spreadsheets are great for small amounts of material and for one view.
11. Main Features of Access Allows you to design Tables Allows you to design Queries Allows you to design Forms Allows you to design Reports Allows you to design Macros Allows you to design Modules
12. II. Display Features of the Access Interface
13. The User Interface is the work area of an application. Main Parts Office Button Navigation Pane Status Bar Quick Access Toolbar Mini Toolbar Context Command Tabs Galleries Tabbed Documents Access Ribbon
14. Office Button The office button is primarily used to change databases into various year formats as seen. The office button is found in the upper left hand corner It contains multiple functions Displays Access databases opened Displays formats that databases may be saved Displays print functions Displays database management functions Displays internet and network management functions
15. Navigation Pane Components of an access database are known as objects. Primarily used to display objects of a database. Objects may be sorted or displayed in alphabetical order or different ways. The navigation pane displays all the objects that are contained in the database.
16. Navigation Pane Sorts Custom groups Object type Table and related views Created date Modified date
17. Objects Tables: Collects Data Queries: Extracts Data Forms: Views Data Reports: Displays Data Macros: Automates groups of Access Commands Modules: Creates automated applications
18. Status Bar Found at the bottom of the window. Contains a View/Window switch and Zoom feature. Can be disabled or enabled in the Access Options Dialog box.
19. Quick Access Toolbar Contains one click access to commands frequently used. Can be customized for user needs.
20. Mini Toolbar Used for formatting text.
21. Context Command Tabs Depending on what object you are working with, one or more contextual command tabs will appear with the standard command tabs. These command tabs contain features specific to that command.
22. Galleries Galleries are dropdown type boxes giving a visual picture to assist with the choice desired.
23. Tabbed Documents When a database object is opened a tab at the top of the object is seen. Allows quick movement from one object to another without having to close object(s).
24. Interface Ribbon The interface is the area that the program platform that the application runs. The ribbon contains command tabs where related commands and tasks are consolidated in one place. These command tabs are: Home Create External Data Database Tools
25. Home Command Tab Views: Datasheet; Pivot Table; Pivot Chart: Design; SQl Clipboard: Cut; Copy; Paste; Format Painter. Font: Choose family, size, justification, Bold, Italic, Color, Gridlines, Background Color. Font Alignment and text formatting. Text: increase and decrease level; direction; bullets; Numbering; Highlight. Records: Refresh; New; Save; Delete; Total; Spelling. Sort and Filter: A-Z; Z-A; Clear Sort; Filter. Find records: Find; Replace; Go to & Replace.
26. Create Tab Tables: New Table; Table Template; SharePoint List; Table Design. Forms: Simple Form; Split Form; Multiple Items Form; pivot Chart; Blank Form; More Forms; Form Design. Reports: Simple Report; Labels, Blank Report Grid; Report Wizard; Report Design. Other: Query Wizard; Query Design; Macro.
27. External Data Tab Import/Link: Saved imports; Access; Excel; SharePoint; Text File; XML. Export: Saved exports; Excel; SharePoint; PDF; XPS; Word; Text file. Collect Data: Create Email; Manage Replies.
28. Database Tools Macro: VB Editor; Run macro; Create shortcut menu; Convert Macros to VB. Show/Hide: table relationships; properties; dependencies. Analyze: documents; performance; tables. Move data: SQL; Access Database. Tools: Linked Table Manager; Switchboard. Create or Edit VB Application Module.
29. Views Datasheet Design Form Layout Pivot Primary Views Utilized Datasheet Design View
30. III. Database Development Guidelines To move forward in Access plan backwards
31. Essential Elements in Databases Integrity of Data Mobility Scalability Predictability Compatibility
32. Database Development Guidelines Correctness and completeness is essential to good database structure. Good database structure: Puts info into subject related tables. Provides information to join tables together. Permits accuracy. Processes data in reports as desired.
33. Database Development Guidelines Determine the purpose or mission of the database. If possible, develop a mission statement in a single sentence for the database. Determine the objectives for the end users. Determine the goals of the objects in the database.
34. Database Development Guidelines Determine the required types of information for the database. List the types of information into logical units or fields. Each field should relate to the subject of the table. Name the fields with no more than 64 characters. Keep names short and simple.
35. Database Development Guidelines Combine the fields into tables of common characteristics or subjects. Designate a primary key that uniquely identifies a record. Identify all the tables needed for the database. Identify the relationships between tables.
36. Database Development Guidelines Build Table Relationships Developing table relationships permits you to link tables into forms, searches or queries and reports. When you have created separate subject based tables you are able to build relationships of the tables to associate items together as needed.
37. Database Development Guidelines In each table specify a Primary Key A primary key is found in tables where there is a column that uniquely identifies each row stored in the table. The unique identification element is used to associate data as a link from the other tables. The column will have values that will always be different for each record. The primary key must have a value that will not change for the record. Auto numbering is a good possible choice for the P.K.
38. Database Development Guidelines Identify the table properties for each field Classify Field Types Clarify Field Size Determine if a Field Description is needed Determine if a Field Caption is needed Determine Format of data Determine an Input Mask if necessary Set a Default Value if necessary Determine Validation rule and text if necessary Determined if value is required Determine if zero length is needed Determine if there is to be an index Determine if a smart tag is to be issued.
39. Database Development Guidelines Refine the design of the database as needed In tables: Tables; Queries; Forms; Reports Things to check Is there sufficient tables and columns for each table Are any columns unnecessary? Are you duplicating information in one or more tables? Are there tables with many fields, limited records and empty fields in records? Has information been broken down into the smallest useful parts? Does each column contain a fact about the table? How are the relationships represented?
40. Data Dictionary A data dictionary is a record documentation of the entities that make up the tables of the database. A data dictionary describes the types of relationships created which the database is joined together. A data dictionary explains the queries within the database. A data dictionary describes the created forms and reports and the processes in which they were made. A data dictionary is updated to the latest changes to all objects. The built in Database documenter builds this automatically for the administrator of the database.

41. IV. Database Design

    A. Design of Initial Database
42. Table Design Tables are the central components for the storage of data. There are three ways to create tables: Datasheet Template Original created in the Design View The design view is the preferred choice because it gives the most control for defining the data types for each field in a table, and it provides a way of adding descriptions for fields.
43. Creating the Initial Database in Datasheet View Launch access application. Click blankdatabase. When you click on blank database you are asked to give a name and save the document. Database opens in datasheet view. Enter data, Name field, Classify type of Data.
44. Creating the Initial Database in Datasheet View Create new field if desired by entering data in next column. When finished click save, enter name click ok. The name will appear at the same time in the navigation pane under the heading of tables. You will be able to change the name of the table when the table is closed.
45. Table Design in Template Mode Select category and template desired. Save template database under desired name. Modify table and field names and properties as desired. Enter data immediately.

46. IV. Database Design

    B. Design of Tables and Fields
47. Design of Database in Design View Create Tables Add Fields Choose Primary Key Establish properties
48. Adding Tables To add new tables to an existing database click the TABLE Icon in the create tab on the ribbon. When clicked, the table will open in the Datasheet view which allows you to type field names in immediately across the top and save table. The name will appear at the same time in the navigation pane under the heading of tables. You will be able to change the name of the table when the table is closed. You can then enter the Design View and detail the table.
49. Table Fields in the Design View In field name column, enter the field names for the desired columns. In the data type column, select the type of data to be collected from the drop down box. Text, Memo, Numbers, Date/Time, Currency, Auto Number, Yes/No, OLE Object, Hyperlink, Attachment, or Lookup Wizard Set field properties.
50. Means of Data Retrieval Through Importing the data Through Appending data Through Linking data
51. Importing You may import data from: Other access databases Excel spreadsheets SharePoint list Text, Xml, Odbc, Html file formats Outlook folders Dbase, Paradox or Lotus 123 program files
52. Importing Data Tables can be created by importing data from outside the existing database by utilizing the Import Group on the External Data Tab. It allows you to utilize data that already exists. Process differs slightly depending on the data source.
53. Importing Excel Spreadsheets Check to make sure that the data entered into each column is consistent with field names and types in Access before importing. If different, specific function for that column or field may become inoperable. You cannot import multiple columns with identical names. Each column header cannot be more than 64 characters. Do not have leading spaces or symbols as headers.
54. Importing from Access Databases Click on the import tab and select Access Database Icon. Follow the prompts. Select the objects desired from that database
55. Linking Excel & Access Tables The name and location of the file must remain the same. Access stores the name and the location of the file. If file is moved the file must be linked again as if it were the first time. Once linked, table order and number of columns cannot be changed in the original linked tables.
56. Linking Linking external files permits the tables to be modified and then connected to Access without making it a permanent part of the database.
57. Appending Tables Appending a table is through creating an append query. This query adds records from existing tables or queries to the end of another table. You choose the fields you want to append from one or more tables or queries. Selected data remains in the original table, but added to the table to be updated.
58. Entering Data & Editing Existing Tables After defining the field names, data types, field properties, indexes and primary keys you can enter data in the table in the datasheet view. This is like entering data in Excel Spreadsheets Data to be entered into the fields must be compatible with the data type and field property settings of a field. Editing existing tables may be done at any time. Copying and pasting is done just like Excel.
59. Relationship Design Process Relationship Table Window Query design grid
60. The Relationship Table Location of the Relationship Table: Click the database tools tab. Click Relationships in the show/hide section. Click Show Table. Select the tables, queries, or both double clicking on the items or select Add you want to build relationships. Click close.
61. Relationships: Preliminary Thoughts Relationships in databases permit queries, forms and reports to display information in a clear relational manner. The coordination is done by matching data in key fields. Types of Relationships: One to many One to one Many to many
62. Relationships: Preliminary Thoughts Relationships are defined between two tables where the two tables have a common field Relationships between two or more tables eliminate duplication of material. Building relationships early in the creation of the database assist in the creation of queries, forms and reports Relationships impact the manner in which reports display data.
63. Relationship “Join” When you use a common field to relate one table to another , the process is called “performing a join” With this join, you will be able to extract data from each table due to the link between them from a common field relationship There are two basic types of Joins Inner Join Outer Join
64. Relationships in Tables There are three main types of relationship joins that can be created in Access. One to One Join This exists when there are identical primary keys between two tables. One to Many Join This exists when values in the field used to create the join appears one time and appears in the second table several times
65. Creating the Relationships Select database tools>Table Relationship Window> Click Show Table. To add Tables to Relationship Window select the tables desired to create join. Then close Show Table. Create Joins>Edit relationships
66. Creating the Relationship Select the Query Design Window. Select the tables you desire to join together. Click and drag the field in the first table over the field in the second table and release.
67. Information Retrieval and Presentation Sorting: a specific selection of data Filtering: temporary removal of selected data Indexing: a specific sequential ordering of data
68. Sorting and Filtering Data Data can be analyzed by sort and filter to make initial assessments on the data entered. Sorting does not provide significant analytical power because only one field or column can be used to set the sequence. Sorting in Access is not the same as in Excel. Filters are used to temporarily remove records from a table based on a defined set of parameters. To remove a filter, click the toggle filter icon.
69. Difference between Indexing & Sorting Sorting will change the appearance of data sequenced in a table. Indexing refers to the way data is stored in a table. You cannot change the sequence of data as it appears in a table through indexing, but is done through the sorting command.
70. Indexing Used to determine how data is organized or sequenced in a table. Indexing makes the extraction from large tables easier, and more efficient. It is important to determine which fields are likely to be used to search for data, when setting the index for a table. An index can be established with up to 10 fields. Clicking on the index icon allows an index to use multiple fields and can be done in ascending or descending order.

71. IV. Database Design

    C. Design of Queries
72. Queries A query is a question regarding the data stored with specific records and fields chosen to give the answer through the search process. A query is the tool used to extract, manipulate, and calculate data from a table based on a specific predetermined criteria.
73. Queries Before using a table for analytical work you must : Identify the purpose of the table Identify the definition of each field Identify any indexing of the table Identify the field that makes up the primary key
74. Queries Purpose of Queries: Supplies data for a report or form Assembles the data to be utilized in a form or report Can be used as a data entry source in forms Performs calculations Updates table data Retrieves data
75. Types of Queries Select: Simple most fundamental. Action: Adds, changes or deletes data. Nested: Queries that select data from existing queries.
76. Terms in Queries Record set: resulting group of records that may or may not be editable. Dynaset: Those that can be edited. Snapshot: not editable usually with those that have calculations.
77. Built in Wizard Queries Simple select queries Crosstab queries Parameter queries Duplicate queries Unmatched queries
78. Query Design Window Upper Pane: shows tables and all fields in those table. Lower Pane: is a design grid for specific query criteria and calculation settings for each field. Items can be hid, from result by removing the check mark
79. Query Criteria Is used to express specific conditions, calculations or characteristics that must be met. Expressions are combinations of symbols or characters to operate to the desired result.
80. Criteria Characters Symbols: specific symbols representing acts. Values: literal values, constants, function results Identifiers: values of fields and controls. Operators: symbols of an operation in math, comparison or logical computations. Wild cards.
81. Creating the Select Query Select: Create Tab>Query Design Query Design is the most versatile when designing a new query. In Query Design view the Show Table box appears as well with listing of tables, queries and both. Select by double clicking on the table desired to be used for this query. Select Close the Show Table. Expand the selected table to see all the fields. Double click on the field you and want in the first field of the query and so on until finished. Press the Red Exclamation Point to run query. After query has run, to save select the floppy disk at top or select the save as and give the query a name that will demonstrate what the query does. Query will be stored in the Navigation Pane.
82. Creating a Parameter Query A parameter query is one that lists the data desired between one point and another point. To do this, set the system into the query design mode. Select the tables or table desired in the search. Choose the field that you want to use to define the search parameters. Type a phrase in [ ] that ask the type of data you desire to use as the criteria.
83. Duplicates Query with Wizard A “Find Duplicates Query” Select query wizard command in create tab. Select Find Duplicates Query. Choose the specified table containing information. Select the field from list that contains information. Select the fields that you want to show in addition to the field in which duplicates might occur. Name the query to designate the action or activity.
84. A Find Unmatched Query Select Find Unmatched Query in Wizard. Select table whose records are to be display. Select queries and select the table you want to match. Specify a joining field, from the other list. Click the< = > button to join them. Select the field desired to be seen. Name the query to demonstrate the query action.
85. Crosstab Query A crosstab query is a special type of summary query that correlates summary values between two or more sets of field values. One set of facts is listed vertically as row headers at the left; the other is listed as column headings across the top. Summarized values are in the body of the query. A minimum of 3 output fields of row headings, columns headings and values are needed to create. Results cannot be changed are act as a snapshot.
86. Creating Crosstab Query Click crosstab query in wizard. Select table as the basis of query. Double click the field to use as the row heading. Select the column heading. Select the value field. Uncheck check mark to not include row of sums Name query as to action performed.
87. Special Purpose Queries Queries are the means of retrieving information in Access. The user can select specific records to extract at time of running query and automatically filling in the field data during data entry Queries can be run to add, update or delete data. Special purpose queries to select a specific group of data a parameter query is created based on the field desired to be used. Auto Lookup Queries automatically fills in the data from one table to fields in related tables.
88. Parameter Query Click query design command. Select show tables dialog box and select. Select the parameter field for the first column and then the desired fields for the query body. Type in brackets [Enter Criteria Name] for a single parameter query. Press run query. For a range of data criteria type: Between [Criteria name] and [Criteria name] as the end of range. Useful for a range of dates or specific values.
89. Action Queries Update Make table Append Delete Action queries cannot be used as a record source for forms or reports. Make a backup of any table that is used for action queries. Utilize the datasheet view showing progress.
90. Update Query Update queries are used to change one or more field values in many records at once. On the design tab in query type click update query command. Select the tables to be used. Select the constant field and the fields to be changed onto the grid. Enter the expression that will select the desired records to be updated On the update to row, put the names of the fields in brackets and the desired function expressions and the new variables. Right click query tab and choose datasheet view and see which data is chosen. Right click again returning to design view then click save as NEW NAME, then click run query. Respond yes to message warning or no to abandon the update. Compare updated table and backup table.
91. Append Query Used to add/remove records from one or more source tables to another table or tables Process: Select the desired tables and make copies renaming them to show development. Select copy structure only Start a new query adding the main source Select Append Query command Type in name of copied table in dialog box Drag the * to the grid for all fields or select fields Use the criteria of “Lost” in cell Remove the name of the source field and then switch to datasheet view Return to design view and save query. Run query to display results.
92. Make-Table Query Creates a new table from records from one table and one or more existing tables copying the data to a target table. Source tables and queries are unaffected. Create a new select query. Select the fields desired. On the design tab in the query type group click Make-Table command and enter name. Check results in the datasheet view before saving.
93. Nestled Queries Queries from within queries
94. Components of Expressions Identifiers: Operators: Functions and Arguments: Constants: Values:
95. Expressions Defined as a combination of some or all built in or user defined functions, identifiers, operators, and constants to perform calculations, manipulate characters or test data. They are used in tables, queries, forms reports and macros. Expressions can be complex or very simple.
96. Use of Expressions Expressions are used to calculate values for queries and controls on forms and reports. They are used to set a default value for a table field or a control on a form or report. Used to create a validation rule, controlling what values users can enter in a field. The most common is to calculate values that don’t exist in the data directly, when you create a column in a query that results from such a calculation and calculat5es the line item.
97. Use of Expressions Expressions can be used to provide a default value for a field in a table. Expressions can be used to set criteria for a query. Expressions run well when there is at least one identifier, one function, one operator, one constant and one constant. When you add criteria to the query and then run the query, it returns only those values that match the specified data requested.
98. Comparison of Access Expressions to Excel Formulas Access expressions resemble Excel formulas in that the both use similar elements to produce a result. Access expressions may be used in more than one place where in Excel they can only be used in one cell. Access expressions allow you to calculate in fields, forms and reports; group data, and validate data.
99. Expression Component: Identifiers An identifier is the name of the object and the name of the field. Between the object name and the field name you use !. This tells access that what follows is a field or element of an object. Surrounding the entire each identifier use [ ].
100. Expression Component: Functions & Arguments Functions are procedures that perform an action of some sort. Arguments are expanded explanations of the functions. There is a massive list of functions based on the type of operations desired, just as in Excel. Arguments are also listed in the explanation of the functions list.
101. Expression Component: Operators Operators are the symbols that indicate a mathematical or logical relationship between the other elements of the expression. Three types of operators: Computation: Math operations Comparison: compare values Combining : combine two values into one
102. Expression Component: Constants A constant is a known value that does not change.
103. Locations for Expressions In the source control of a property. In the default value property for a control. In the validation rule property for a table field. The field or criteria row of a query.
104. Selection criteria in Queries Symbols: quotation marks, colons, asterisks and other characters are used in expressions. Values: literal values, constants, results of a function or identifiers. Identifiers: The values of a field, controls in a form or report or properties. Operators: symbols or words that indicate an operation that is to be performed on one or more elements in the expression.
105. Wildcard Elements For math elements: addition+, subtraction -, multiplication*, division /. Comparison: equals =, greater than >, less than <, and Like. Logical: And, Or, Not.
106. Performing calculations in a Query Two types of calculations: Aggregate calculations: predefined operations that are performed on groups of records and provide totals, counts, averages and other information about field values in all records or in groups of records. Calculated Fields: Actually creates new fields in a record by combining the values in other fields in the record producing a horizontal computation.
107. Calculated Fields To add a new field that displays calculation results on other fields in the grid: First select an empty field and enter an expression. The field names in the expression must be enclosed in brackets. Followed by the math function, then add another field name also in brackets. Add the name desired in the space that has the word expression in it. Can be used with math or date actions.
108. Applying Calculations to Data Formulas Special field formulas: One that is added to a table in the design grid. No field name is entered in this special field. May be used for calculations, where math expressions will be formulated. Problem to watch for is the order that formulas are entered in proper sequential order. Order of operations is ^ : power or exponent * or / : multiply or divide + or -: add or subtract Functions. Financial Functions.
109. Parts of the Expression Builder Expression Box: top half contains an expression box where you construct the expression. It can be typed in manually or you may select from the three columns in the lower section of the builder and add them to the expression. Operator buttons: are the buttons for math functions and logical operations. Expression element: three columns Left: folders that list objects of database Center: specific category elements for the selected folder Right: values for the elements that were selected.
110. Expression Builder The expression builder is a tool that allows you to point and click in the creation of formulas. Click in the first row of an empty column and then click on builder icon in the design tab. Click on upper window of builder and type in the name of the new field. In the left hand bottom column select the tables folder and select table and click table field to show a list of field names. Double click on the field in table and will put it at the top automatically.
111. Construction of Expressions in Builder Click an item in the left column. With the selection of different items in the left column, the information in the middle and right column changes, giving you all the options with the selection of the object chosen. Once the expression is completed press ok.
112. Functions IIF stands for immediate IF Three arguments: Expr: used for entering a logical test and is identical to the logical test argument of excel if function. Used to evaluate data in a field based on a test that one defines. Truepart: used for defining the output of the function if the results of the logical test are true. Can be defined with a field name, formula, number or words. Falsepart: used for defining the output of the function if the results of the logical test are false.
113. Future Value Function Symbol is FV Used to evaluate the return on investments with a constant interest rate of specific time Arguments of FV. Rate: percentage rate earned Nper: number of periods money is invested into an account or time investment is measured, corresponding to rate of interest. Pmt: amounts of money added to an investment. Pv: present value. Value on a one time investment. Due: defines when money is invested.
114. Payment Function Symbol is PMT Calculating mortgage payments for a house or lease payments of a car. Similar to Future Value function. Arguments: Rate: interest charged by lender Nper: number of payments Pv: present value defined as principle of loan Fv: future value. Value when paid off Due: value of th argument determines if payments are made at the beginning or end of period

115. IV. Database Design

     D. Design of Reports
116. Design and Development of Reports Reports may be created using a query or by tables. Reports created by a query permit the selection of data based on a criteria due to the large size of many tables. Reports are found in the create tab on the ribbon.
117. Design and Development of Reports Reports may be created through the Report Wizard or the Report Design Option. Reports developed with the Report Wizard or the Report icon option will still have to be tweaked to ones liking. Creating reports through the Design Option take a longer period of time.

118. IV. Database Design

     E. Design of Forms
119. Forms in Access Created using Design View Created using Form Wizard
120. Formatting Tab for Design and Layout View Font Gridlines Controls Tools Adding Existing Fields Auto Format
121. Arrange Tab AutoFormat Control Layout Tabular; stacked; remove Margins; padding; snap to grid; tab order Control Alignment Left; Right; Top; Bottom Control Size Anchoring Control Position Size to fit; bring to front; send to back; property; show/hide.
122. Forms Simple form Split form Multiple items form Form design grid window Form Wizard Form layout view
123. Forms A form is an object in a database used to maintain, view and print records from a database in a customized way. Forms can be created by a form wizard, the auto-form wizard or manually. The form wizard is broken down by design view, auto column, table, datasheet, pivot table/chart, chart wizard and pivot table wizard.

124. IV. Database Design

     F. Design of Reports
125. Reports in Access A report is a formatted printout of one or more tables appearing in tables, queries, forms and other reports. Reports are created with the wizard, design mode and auto-report mode.
126. Parts of the Report Format Report Headers: Contains Title, Logo Report Footers: Contains totals Page Footer: Contains current date and page # Group Header: Contains titles of data fields Group Footer: Contains totals from Group
127. Reports in Access There are multiple ways to create reports: Simple reports Report Wizard Create on blank page Keys to creating reports: Know what you want to accomplish Know how you want it to look Know what you want to put where on the report Know what tables you want to utilize Make a sketch of the report in advance.
128. Reports in Access Decide how to arrange the detail data In Access there is great flexibility Types of Data Layout in Access Table Layout: spreadsheet Column Layout: single column report Justified Layout: for large amts of data spread across page
129. Parts of the Report Format Report Headers: Contains Title, Logo Report Footers: Contains totals Page Footer: Contains current date and page # Group Header: Contains titles of data fields Group Footer: Contains totals from Group
130. Report Types Grouped Summary Simple
131. Design and Development of Reports Reports may be created using a query or by tables. Reports created by a query permit the selection of data based on a criteria due to the large size of many tables. Reports are found in the create tab on the ribbon.
132. Arrange Tab AutoFormat Control Layout: tabular; stacked; remove Margins; padding; snap to grid; tab order Control Alignment Left; right; top; bottom Control Size Anchoring; Control Position Size to fit; bring to front; send to back; property sheet Control Show/Hide
133. Formatting Tab in Layout View Views Formatting Gridlines Controls Add existing fields AutoFormat
134. Page Setup Tab Layout Size Portrait Landscape Margins Show margins Print data only Page setup
135. Print Preview Print Print Layout Zoom Data Close Preview
136. _____________________________ _____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
137. _____________________________ _____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________
138. Bibliography Microsoft Office Access 2007. Kenneth Hess. Wiley Publishing, Inc. 2007. Microsoft Access 2003. Joseph J. Adamski, Kathleen T. Finnegan. Thomson Course Technology. 2004. Using Microsoft Access 2000: Special Edition. Roger Jennings. Que Publishing. 1999. How to Do Everything with Microsoft Office Access 2007. Virginia Anderson. McGraw-Hill Books. 2007. Microsoft Office Access 2007 In Business. Joseph M Manzo. Pearson Prentice Hall Publishing. 2008. Microsoft Access 2007. Online Help Files. Various Authors.