Intro to Access 2007

1. Intro to Access 2007 MIS 2502 | Summer1 2010

2. Navigation • The Ribbon • Standard to all Office 2007 applications • Organized by feature and functionality • Navigation Pane • Access to all objects in the current database Intro to Microsoft Access 2007 | MIS 2502

3. Tables • Grids that store information in a database • Much like an Excel spreadsheet • Must have at least ONE primary key • Differentiates each record • Example: TUID Primary Key Button Intro to Microsoft Access 2007 | MIS 2502

4. Creating Tables CreateTable 1 2 Save Switch to Design View 3 Intro to Microsoft Access 2007 | MIS 2502

5. Creating Tables • Design View • Field Name: name of a table’s fields • First Name, Address, Telephone… • Data Type: type of value allowed in the field • Text, Number, Date/Time… • Field Properties: attributes of each field • Set from the bottom pane • Field size, format, default value… Intro to Microsoft Access 2007 | MIS 2502

6. Creating Tables Data Type Field Name Field Properties Intro to Microsoft Access 2007 | MIS 2502

7. Field Properties: Examples • Input Mask • For TU-ID: ###-##-#### • Validation Rule • For student standing: “Freshman” Or “Sophomore” Or “Junior” Or “Senior” The slashes will be added automatically! Intro to Microsoft Access 2007 | MIS 2502

8. Adding Records • Select Datasheet View • Enter your records into the table Insert New Record (same as TAB) Intro to Microsoft Access 2007 | MIS 2502

9. Relationships Foreign Keys REFERENCE Primary Keys • Primary keys should match foreign keys in corresponding tables • MUST be same data type (i.e. Text, Number…) • Every relationship includes ONE primary key and ONE foreign key Intro to Microsoft Access 2007 | MIS 2502

10. Relationships This is your ERD! Intro to Microsoft Access 2007 | MIS 2502

11. …but I don’t see relationships • You can manually create the relationships • Click on foreign key • Drag to its corresponding primary key PK FK Make sure these 3 boxes are checked! Intro to Microsoft Access 2007 | MIS 2502

12. Capturing your ERD • Viewing your ERD (Entity Relationship Diagram) in Access… • Make sure all of your tables are fully expanded • No scroll bars should be visible! Intro to Microsoft Access 2007 | MIS 2502

13. Capturing your ERD • Open your ERD • Press PRINT SCREEN • Paste screen into MS Word (CTRL+V) • Cropunnecessary portions • Page Layout to LANDSCAPE • Resizeusing handlers Intro to Microsoft Access 2007 | MIS 2502

14. Queries You can run queries on queries! • Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. • Stored as separate database objects for future use • Options found on the CREATE tab Intro to Microsoft Access 2007 | MIS 2502

15. Types of Queries • Select: most common; retrieves data • Parameter: prompts user for criteria • Crosstab: calculates totals • Action: makes changes to records • Delete • Update • Append • Make-Table Intro to Microsoft Access 2007 | MIS 2502

16. Forms Same effect as entering through datasheet • Alternate way for users to enter data into a database table • User friendly Intro to Microsoft Access 2007 | MIS 2502

17. Creating Forms • Form Wizard • Build through dialog boxes • Form Design • Start from scratch Intro to Microsoft Access 2007 | MIS 2502

18. Reports • Organize and group information found in a table or query • Printable • Types • Parameterized: uses input values • Ad hoc: fully customized • Canned: runs on command; no customization Intro to Microsoft Access 2007 | MIS 2502

19. Creating Reports • Report Wizard • Build through dialog boxes • Report Design • Start from scratch Intro to Microsoft Access 2007 | MIS 2502

20. Switchboards • Command menu & user interface • Used for navigation and initialization of database tasks • Includes buttons and images that open forms, reports, and queries Intro to Microsoft Access 2007 | MIS 2502