1 / 35

Microsoft Access Overview

Microsoft Access Overview. Lydia Scheer, ITEP. The Database Environment. Access is a relational database Data stored with Access are related to one another in multiple ways Access maintains the data relationships when database is structured correctly

declan-knapp
Download Presentation

Microsoft Access Overview

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Access Overview Lydia Scheer, ITEP

  2. The Database Environment • Access is a relational database • Data stored with Access are related to one another in multiple ways • Access maintains the data relationships when database is structured correctly • MS Excel (or other spreadsheets) are considered “flat” file databases • Rows and Columns of data that may be related • Excel has no way of maintaining relationships automatically

  3. How do Relationships Work? A relationship works by matching data between tables based on values in key fields. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key from another table.

  4. Relationships Window • View, define, and modify relationships • Tools (Menu)Relationships… or right-click on database window (white area) to view • Join lines indicate type of relationship (1=one-side; = many side) • Double-click on a join line to modify relationship • Tables/field lists are moveable/sizeable • A table can be related to multiple tables, but only by ONE field at a time

  5. Types of Relationships • One-to-One • Item (record) in Table A related to only one item in Table B • Not common because usually the information is stored all in one table • Example: Data archive table; Subset of information that would result in too many blank fields in main table (confidential info, instructors, etc.) • One-to-Many or Many-to-One • One item in Table A can be related to multiple items in Table B • Example: One site has multiple (many) monitors; One person has multiple visits to the clinic • Many-to-Many • Combination of two One-to-Many relationships • Example: One person has multiple visits to the clinic; One clinic has multiple people visit. • Some Many-to-Many relationships are not immediately obvious (Example: One site has multiple monitors, but does a monitor have multiple sites?)

  6. Join Lines (Identify Relationships

  7. The Structure of Access • Database File: This is your main file that encompasses the entire database and that is saved to your hard-drive or floppy disk. Example: DMExample.mdb • Table:A table is a collection of data about a specific topic. There can be multiple tables in a database. Example #1: tblFIPSCodesExample #2: tblAQS_Ozone • Field:Fields are the different categories within a Table. Tables usually contain multiple fields. Example #1: FilterID Example #2: Date • Datatypes:Datatypes are the properties of each field. A field only has 1 datatype. FieldName: FilterID Datatype: Number • Value: The Data. Example (FilterID): 7544231

  8. Default view when database is opened • Lists all available database objects • Tables, Queries, Forms, Reports, Pages, Macros, Modules • Lists all available database object groups • A way of organizing shortcuts to objects that are related • Create groups specific to your project/needs (e.g. “Favorites”) • Toolbar across top has commands for database objects • Open, Design, New, Delete • View style (4 buttons on the left) The Database Window

  9. The Database Window

  10. Datasheet View • Tables, Queries, Forms • Standard table view – “spreadsheet style” • Form/Page View • Standard form view or page view • Print Preview • Standard view for Reports • Can also be used with Forms, Tables, Queries & Pages • Design View • Applicable to all database objects • Layout/view is specific to the database object • Make changes to the structure/format/layout of object Object Views

  11. Database Objects-Tables • Used for storing data in rows and columns • Rows = Records; Columns = Fields • Records are UNIQUE collections of data about “something” (e.g. a person, a PM filter, etc.) • Fields are categories of data for a set of records • Tables contain information about one subject only • Primary Key field in a table is a unique identifier for each record—no null values or duplicates • Tables can have up to 255 fields, and can store up to 1 gigabyte (GB) of data per table

  12. Design View Datasheet View

  13. Database Objects-Queries • Retrieve sets of data based on user-defined criteria (text strings, expressions, functions) • Often used to combine data from multiple (usually related) tables • Subsets of data stored in tables; data are not actually stored in queries • Perform calculations on or with data • Can be constructed to perform an “action” • create a new table from query results • append records to an existing table • update records in an existing table (similar to find and replace, but can use calculations, too)

  14. QBE Pane or Query Design View

  15. Append Query Grid: Update Query Grid: Delete Query Grid: Make Table or Append Query Pop-up:

  16. Database Objects-Forms • User-friendly way of displaying data for entry or manipulation • Programmed actions (macros, modules) can be attached to forms • Display data from tables and/or query results • Can also be used as a menu (or switchboard), created as custom dialog boxes, or displayed as a datasheet • Subforms-form within a form that displays data from a different dataset than that of the main form

  17. Data Entry Form (with Tabs) “Switchboard” Form

  18. Database Objects-Reports • Output of information from database (tables or queries) in the form of a printed report • Allows you to group and summarize information • Can be previewed on the screen prior to printing • Can include graphs, data tables, images, and calculations/totals • Reports are for publishing data only, not for data manipulation

  19. Database Objects-Macros & Modules • Macros • Access-specific “mini-programs” • Pre-programmed for ease of use • Modules • Visual Basic for Applications (VBA) programming language • More complicated to use properly, but more options and flexibility than macros • Both are based on event-driven programming • An event (click a button, open/close a form, etc.) triggers an action (macro/module) that carries out a programmed set of commands • Most actions have required arguments (user-specified data that tells the event how/when to carry out the action) • Must act on a certain object or control on the form or report • Can only be attached to forms or reports

  20. Adding a Macro or Module (Event Procedure) to a Form Event

  21. Wizards • Access has a number of wizards built in to make it easier to work with each tool • Wizards walk you through creating db objects • Tables & queries • Forms & reports • Other items inside above (expressions, events, etc.)

  22. Simple Query Wizard asks for a table or query to use for base data Then you have the option of displaying all records/fields or creating a summary query Final query results (grouped by Month as an option) displays Avg, Min, and Max of monthly PM mass

  23. Things to make your life easier… • Naming Conventions • Consistency in naming different types of objects, controls, etc. • Use identifiers in names (e.g. tbl, qry, frm) that make sense to you • NO SPACES or Punctuation in names-harder to deal with in expressions and code procedures • Examples: tblSites, qryAQS_OzoneArizona, cmdClose, dap2003SummaryDataPMFineHighestConc • Document what you are doing…will you remember what you did (or why) in three months time? • Changes made to data are PERMANENT • Backup….BACKUP…BACKUP!

  24. View Object in Design View Delete Current Object View Objects as Small Icons View Objects with Details Open Current Object Create a New Object View Objects as Large Icons View Objects as a List The Database Window Toolbar

  25. Paste or Clipboard Tool Save Print Preview Cut Undo Action Show Object Properties Create a new Database File Analyzer Tool View, Edit, or Create Indexes Format Painter VBA (Code) Window Help Files Spell Checker Open another database file Copy Publish or Merge with another Office Application View Relationships Print The Database Toolbar (Default)

  26. Insert Hyperlink ( to Internet or other network location) Sort Field Data by Descending Values Filter by Form New Object (incl. AutoForm & AutoReport) Switch to Table Design View Delete Record(s) Print Table Find a Value Save Table Design Sort Field Data by Ascending Values Table Print Preview Add/Go To New Record Database Window Apply/ Remove Filter Filter by Selection Table (Datasheet View) Toolbar

  27. Switch to Table Datasheet View Field Builder Wizard Insert a Row (Field) Delete a Row (Field) Print Table Design View Set Field(s) as Primary Key Save Table/ Query/Form Design Table Properties View/Set Indexes Table Design View Toolbar(also for Query and Form Views)

  28. Switch to Query Datasheet View or SQL View Choose or Change Query Type (delete, append, select, etc.) Expression Builder or Calculated Field Wizard Print Query Design View Add a table to the QBE Pane Show Top Values (all, 25, 5%, etc.) New Object (incl. AutoForm & AutoReport) Save Query Query Properties Run Query (select queries display in datasheet view; action queries attempt to run) Create a Totals Query Undo Last Action (only allowed once) Query Design View Toolbar

  29. Toolbox for Form Controls Switch to Form Design or Datasheet View Open VBA Window Field List-shows available fields for use on the form Identifies the control on the form which has the focus Properties for selected control(s) AutoFormat Form Design Font Size Indicator Expression Builder Wizard Fill Color, Font Color, Line Color Font Selection Box for controls with dynamic fonts (text boxes, labels, buttons, etc.) Format Font (Bold, Italicize, Underline) Text Alignment: Left, Center, Right Form Design View Toolbar

  30. Select Control Tool-Use to select a control on a form or report to modify, delete, move, etc. Control Wizard—Selected means wizards will be activated when a tool is chosen to help the user establish the control properly. Deselected means wizards will not be activated when a tool is chosen, and enables the user to design a control with his/her own methods. Textbox Tool Create an Option Group Label (control) Tool Toggle Button Tool Option (radio) button; Check box Create Combo Box Create a List Box Command button Create an Unbound Object Frame Insert Page Break Create a Bound Object Frame Insert Image or Graphic Line Tool Tab Control Rectangle Tool Insert a Subform/ Subreport Click to view more controls for use on forms/reports Form/Report Design Toolbox

  31. AutoFormat Report Layout Properties for selected control(s) Save Report Design Insert Hyperlink Show Field LIst Print Report VBA Window Format Painter Print Preview Switch between Print Preview, Design View and Layout Preview Show Toolbox Sorting/ Grouping Options Expression Builder Wizard Report Design View Toolbar

  32. Show/Hide Macro Names Column Insert a Row Step Through Macro Actions Save Macro Delete a Row Print Preview Macro Definitions (summary of macro actions and arguments Undo last action (can only use once) Show/Hide Macro Conditions Column Expression Builder Wizard Run Macro Macro Design View Toolbar

More Related