Microsoft Access 2003 - Intro

1. Microsoft Access 2003 - Intro Class 1 Database Concepts

2. Paper vs. Database With a paper filing system, you have to cross out, erase, or redo a record to make corrections. They also take up a lot of space. With an Access database, you have more room and can easily add, move, edit, find, and delete records. A little trivia: One 500 MB CD-ROM can hold the same data as 650,000 sheets of paper can hold.

3. Comparing Excel to Access

4. Database Design • Flat file database • All data stored in a single table • RDBMS • Manages, stores, retrieves large amounts of information • Reduces redundancy (duplication of data) by linking tables • Good database design reduces redundancy by Normalizing the data

5. Introduction to Database Management Systems • Database Management Systems (DBMS) • Used to store, retrieve and order large amounts of information • Access • Stores all information in one file (up to 2GB file size) • Is a relational database management system (RDBMS) • Link tables through a common field • Combine data in new objects and minimize data duplication

6. The Access 2003 Interface • Database window toolbar • Tables • Queries • Forms • Reports • Pages • Macros • Modules • Groups Common to all databases

7. Table Foundation of the database Store your data Forms Data input View Records Reports Printing Presentations Queries Sort, search, limit data Perform calculations Macros Automate existing commands Modules ProgrammingNew commands The Database Structure

8. Microsoft Access Database Specifications

9. Getting Help • Office Assistant – Ask him a question and get a list of possible solutions. Also access the Contents, Answer Wizard, and Index help text. • What’s This? – Learn more about a particular object in an Access window. • Ask a Question – Get help without using the Office Assistant character • Online Help – Access Microsoft’s Web page for more help.

10. Tables • Tables • Made up of columns (called Fields) and rows (called Records) • Provides a spreadsheet-like view • Used to generate all the other objects in the database • Table properties allow you to streamline the data entry process and validate data as it is entered • Can switch between Datasheet View and Design View by clicking the View button or clicking the View menu.

11. First record Previous record Next record Last record Navigation Buttons The navigation buttons in a table or form work like the ones on a CD-player.

12. Keyboard Shortcuts

13. Queries • Used to • Find records based on specific criteria • Filter data • Group data with totals • Combine fields from multiple tables • Update or delete data • Append data • Create a new table • Query results are called a recordset • Queries also have a Datasheet View and a Design View

14. Forms • Used to display, edit and enter data on the screen • More friendly user interface • Data is linked back to source table • Changes made to data in form are reflected in source table and vice versa • To change structure or layout of form, switch to Design View

15. Reports • Used to print records • Based on a table, query or SQL statement • Sorting and grouping • Formatting • Contain calculated expressions • Contain headers and footers, page numbers etc.

16. Pages, Macros & Modules • Pages (Introduced in Access 2000) • Data Access Pages can be published to the internet or intranet • Live link to the data (data can be updated from anywhere) • Macros • Used with forms and reports to automate database operations • Easy to learn • Can be converted to VBA • Slower and being phased out by Microsoft although still supported in Access XP • Modules • Small program written in VBA • More flexible and powerful than a macro • Better choice to automate applications, runs faster, allows trapping of errors

17. Planning the Database The more time you spend planning a database, the less time you spend correcting bad structure, much like planning to build a house. A blueprint is a valuable tool. Take your time and plan it well.

18. Steps in Planning a Database • Determine the purpose of the database and give it a meaningful name • Determine what reports/queries you want the database to produce • Collect the raw data that will be stored in the database • Sketch the structure of each table, including fields, names and data types

19. Creating a Database in Access • Click File menu, then click New… • Choose where to save the database, then type a name for it • Click the Create button

20. Creating a Table Using the Table Wizard • In Database window, double-click the option to create a new table using a Wizard • Follow the instructions on each screen of the wizard • Warning: Wizard may make assumptions based on U.S. settings (e.g. Interprets Postal Code as Zip Code) • Better to design table using Design View

21. Primary and Foreign Keys • Primary Key • Uniquely identifies a record in a table • Data in table is sorted by primary key by default • When entering records, cannot be left blank • Composite key – primary key that consists of two or more fields • Foreign Key • The join field in the secondary table in the relationship

22. Good Choices Social Security Number Employee ID Number Account Number User ID Email Address Bad Choices Last Name Address Department ID Job Title Any item that could be duplicated Choosing a Primary Key A primary key field must be unique.

23. This work is licensed under a Creative Commons Attribution-ShareAlike 2.5 License.