Microsoft Access 2002

1. Microsoft Access 2002 Part 1 + 2 King George Instructional Technology Dan Hopper

2. Types of Databases • Flat Database- One table holds all the data in the database • Microsoft Works Database • Microsoft Excel • Relational Database • Microsoft Access 97 - 2003 • Oracle

3. Access 2002 • Used to generate forms, queries, and reports from the data it stores in tables • A form is used for entering and viewing data • A query is used for displaying specific data • A report is used for formatting and organizing data for printing.

4. Data – a piece of information • Must be in digital format • Can include • Text • Numerical data • Photos and graphics • Sound fields • Email addresses • Hyperlinks

5. Field – stores data • Field Names • Must be unique • Keep them short but descriptive • Use complete words not abbreviations • Avoid special characters !@#$%^&*()?>< • No spaces between words ex. FirstName

6. Types – classified by the type of data they store • Text – letters, symbols, words, combo of letters and numbers (max of 256) • Number – store only numeric values • Date/Time – store only a date or time • Currency – store dollar amounts • Memo – store several lines of text (256 and more) • Auto-number – Automatically store a numeric value • Yes/No – either a true or false/1 or 2 value • OLE Object –Word, Picture, HTML, Hyperlink

7. Formats – determine how the data is to be displayed • General- displays the number exactly as entered • Fixed – specified number of decimal places • Percent – displays with a % sign • Standard – uses a thousands separator, usually a comma

8. Tables – group related fields • Contains rows of records and columns of fields • Can have many tables in a single database A Field A Record

9. Primary Key - a field designated to contain unique data • Every table must have one Primary Key

10. Database Design Process • Determine what information should be stored. • Break it down into small pieces • Divide information into named tables • Define the fields and determine the primary key for each table. • Field Name, Type, Sizes, Formats, P-Key • Use the Worksheet database to help you

11. Create the Table • Create the table in design view • Type the field name • Select the field type • Enter desired rules, sizes, validations

12. Formatting Datasheet Views • Resize Columns (Fields) • Resize Rows (Records) • Move Columns • Copy and Paste data

13. Filtering • Query – records that meet certain criteria, asking a question • Filters- faster way to query a datasheet • Criteria – what must the record contain to be shown

14. Types of Filters • Filter by form – allows multiple criteria • Filter by selection- filters by what you select in the datasheet

15. Forms • A window that contains the fields of a table and is used for • Entering Data • Viewing records • Makes entering data less error-prone • Subforms – forms within a form

16. Modifying a table and updating a form • Modify tables in Design View • Make desired changes • Add a field • Delete a field • Rename a field • Create a new form or update it from the table you just changed.

17. Relationships • 2 tables are related when a field in one table corresponds to a field in another table. • Every table in a relational database must be related to at least one other table in the database.

18. Queries • Standard – Displays the selected fields • Select – Can include fields from any table and uses relationships between tables to determine which data to display. • Available choices • Sorting • Criteria

19. Range Queries • Relational Operators • = equal to • < Less than • >Greater than • <= Less than or equal to • >= Greater than or equal to • <> is not equal to

20. Other criteria operators • And • Or • Between • Like • Is Null

21. Reporting • Plain Reports • Queried Reports • Report Summaries

22. Exporting Data to another application or format • CSV, TXT, XLS • Analyze with Excel • Export to Word

23. Importing Data from another application • Can be from another Access Database • Formats – CSV, TXT, XLS • Almost any file can be imported

24. Switchboards • Speed up tasks • Can be customized • Can be changed anytime

25. On your own • Create a database with the following: • 2 tables • 1 form to enter data in both tables • 2 Standard queries • 1 Select query that uses 2 tables of data • 2 reports • 1 Simple • 1 Grouped

26. Before you leave! • Fill out the evaluation online on the Professional Development Page • Sign the attendance sheet • Sign-up for a follow-up class • Enjoy the rest of your weekend!