Microsoft Access

1. Microsoft Access Database software

2. What is a database? …a database is an organized collection of data. A collection of data of similar information compiled in a table of records consisting of individual fields.

3. Examples….. A Telephone book State and IRS files A file box with cards Your telephone bill Your electric bill Computerized databases Microsoft Access is the database we will study in this class.

4. Database Management Software …provides users with “software tools needed to organize data into a flexible manner…. …It includes facilities to add, modify, or delete data from the database, ask questions (or queries) about the data stored in the database and produce reports summarizing selected contents…. Microsoft Access provides users with one of the simplest and most flexible DBMS solutions… Some other examples of DBMS would be…. MySQL FileMaker Oracle

5. How do databases differ from Excel? • Excel is for heavy “number crunching”. • Output in Access is easier to understand and “digest”. • Data entry is normally by a form. • More Flexible.

6. MS Access • Is known as a relational database. • This means information is joined through common fields in tables. • EXAMPLE: The IRS has a database with every taxpayers information. Each state government also has a database with each taxpayers information. The IRS and the state link up through your Social Security Number to share financial information on citizens.

7. What can Access do? Store Data for easy access. Sort, present, and manipulate data for a specific purpose. Databases perform Mathematical computations on individual records.

8. 3 Main Components of Access 1.) Tables 2.) Queries 3.) Forms

9. Microsoft Access Tables • Tables comprise the fundamental building blocks of any database. If you are familiar with spreadsheets (which you all should be) you’ll find database tables extremely similar.

10. Queries… A database that only stored information would be useless– we need methods to retrieve information as well. Queries – complex request to sort, filter and organize information. Access queries provide the capability to combine data from multiple tables and place specific conditions on the data retrieved.

11. Microsoft Access Forms • User friendly interfaces that allow users to enter information in a graphical form and have that information transparently passed to the database.

12. Why Should I learn MS Access? • More computer skills • Greater understanding of computer applications as applied to existing skills. • Databases provide a tremendous flexibility in storing, accessing, using, and manipulating data.

13. Query • A database object • Lets you ask the database questions about the data it contains. • The condition of the query is a way of telling the query which data you are interested in seeing. • When a condition has 2 parts is called criteria. • Queries are based upon tables.

14. Creating a Query in Design View • Using the query design view window you build and change the query using the query design grid. • Databases often contain more than one table, you can select the table in the Show Table Dialog box that contains the data you wnt to see in the query datasheet. • The query window is divided into 2 parts. The top part if of the window shows the field list, the bottom is the design grid. • You can add fields to the query design grid by double clicking the field name.

15. Moving and Sorting in Design View • You can sort information by using the sort boxes in the design grid . • To sort 2 or more fields, the field you want to sort by first must be to the left of the field you want to sort next. Sorts on more than one field are applied in a left to right order.

16. Adding a condition • To add conditions you must click in the criteria box in the query design field. • Access will automatically place quotations around the data type Text or Memo • Before running a query it is a good idea to save it.

17. Relating Tables • When a database contains more than one table, you can connect data in the separate tables in by creating a relationship. • To create a relationship, you must have tables that contain a matching field. • A matching filed appears in both tables, has the same data type, and contains the same values. • The matching field is also called the common field. • When tables are related in a database, we can create queries and other objects that display information from several tables at once.

18. Common Relationships • One to Many- one record in the first table can match many records in the second table. • First table is the primary table. • Second table is the related table. • Common field in the related table is called the foreign key when used in a relationship.

19. Referential Inegrity • Protects data in the tables to make sure that data is not accidently deleted or changed resulting in inconsistent data. • Choose the Enforce Referential Integrity option when creating relationships

20. Multi table Queries After defining relationships in a database, we can create a query that is based on more than one table. To create a query from more than one table, you must add another table’s field list to the query design. When you add two related tables, a join line shoes the relationship between the tables.