Microsoft Access 2003

1. Microsoft Access 2003 ICS 014 – 042 Yazdani

2. A Few Terms • Database - a collection of related information. • Object - an item in the database such as a table, query, form, or macro. • Table - a grouping of related data organized in fields (columns) and records (rows) on a datasheet.

3. A Few Terms • Field - a column on a datasheet and defines a data type for a set of values in a table. • Record - a row on a datasheet and is a set of values defined by fields. • Design View - provides the tools for creating fields in a table. • Datasheet View - allows you to update, edit, and delete in formation from a table.

4. Creating a New Database To create a new database, click on Create a new file…

5. New File Selections

6. To Create a New Database • Option 1 – Blank Access Database • Creates a new blank database. • After selecting "Blank Access database", you will first be prompted to specify a location and name for the database. • You must save an Access database before you start working on it.

7. Find the folder where the database should reside in the Save in drop-down menu. • Type the name of the database in the File name line and click the Create button.

8. To Create a New Database • Option 2 – Blank Data Access Page • Creates a new blank Data Access Page. • A data access page is a special type of Web page designed for viewing and working with data from the Internet or an intranet that is stored in a Microsoft Access database. • The data access page may also include data from other sources, such as Microsoft Excel.

9. Data sources can be other Access databases, other types of databases, Excel spreadsheets, or a Microsoft Data Link.

10. Data Access Pages • Data access pages have the following advantages over printed reports: • Pages bound to data display current data because they are connected to a database. • Pages are interactive. Users can filter, sort, and view just the records that they want. • Pages can be distributed electronically by using e-mail. Recipients will see current data each time they open the message.

11. To Create a New Database • Option 3: Project using existing data… • Creates a database from an existing Microsoft Office Access Project. • Browse to an existing Microsoft Access Project and click OK. • Name the database on the next screen.

13. Microsoft Access Projects • A Microsoft Access project (.adp) is an Access data file that provides native-mode access to a Microsoft SQL Server database through the OLE DB component architecture. • Using an Access project, you can create a client/server application based on forms and reports, or a Web-based solution based on data access pages, or a combination of both. • You can connect the Access project to a remote SQL Server database, a local SQL Server database, or a local installation of SQL Server 2000 Desktop engine.

14. To Create a New Database • Option 4: Project using new data… • Creates a new Microsoft Office Access Project to base a new Access Database on. • Browse to an existing data source or click on the create button.

16. To Create a New Database • Option 5: From existing file… • Creates a new database from an existing Access database, a Microsoft Access Project, or a web page. • Browse to the file you wish to use.

18. To Create a New Database • Option 6: From a template • Creates a new database from an existing template on your computer or from Microsoft Online. • Quickest way to create a database. • This method works best if you can find and use a template that very closely matches your requirements.

20. Opening anexisting database • If the database was opened recently on the computer, it will be listed in the right window. • Highlight the database name and click OK.

21. Opening anexisting database • Otherwise, highlight "More..." • From the subsequent window, click the "Look In:" drop-down menu to find the folder where the database is located, highlight the database name in the listing and click OK.

22. Opening an existing database

23. Database Window • The Database Window organizes all of the objects in the database. • The default tables listing provides links for creating tables and will list all of the tables in the database when they have been added.

24. Menu Bar Office Links Relationships Analyze Properties New Object

25. Design View • Design View customizes the fields in the database so that data can be entered.

26. Indexes Primary Key Design Sheet/View Toggle Field Builder Primary Key Marker Insert/Delete Rows Database Window

27. Datasheet View • The datasheet allows you to enter data into the database

28. Sorting Filtering Find Design Sheet/View Toggle Record Selector Navigation Buttons & Record Number New Record Delete Record

29. Introduction to Tables • Tables are grids that store information in a database similar to the way an Excel worksheet stores information in a workbook. • Access provides three ways to create a table for which there are icons in the Database Window. • Double-click on the icons to create a table.

31. Introduction to Tables • Create table in Design view will allow you to create the fields of the table. • This is the most common way of creating a table.

32. Create a Table in Design View • Design View will allow you to define the fields in the table before adding any data to the datasheet. • The window is divided into two parts: • a top pane for entering the field name, data type, and an option description of the field • a bottom pane for specifying field properties.

33. Indexes Primary Key Design Sheet/View Toggle Field Builder Primary Key Marker Insert/Delete Rows Database Window

34. Introduction to Tables • Create table using wizard will step you through the creation of a table.

35. Introduction to Tables • Create table by entering data will give you a blank datasheet with unlabelled columns that looks like an Excel worksheet.

36. Primary Key • Every record in a table must have a primary key that differentiates it from every other record in the table. • Can designate an existing field as the primary key if you are certain that every record in the table will have a different value for that particular field. • A social security number is an example of a record whose values will only appear once in a database table.

37. Primary Key • Designate the primary key field by right-clicking on the record and selection Primary Key from the shortcut menu or select Edit|Primary Key from the menu bar. • The primary key field will be noted with a key image to the left. • To remove a primary key, repeat one of these steps.

38. Primary Key • If none of the existing fields in the table will produce unique values for every record, a separate field must be added. • Access will prompt you to create this type of field the first time you save the table and a primary key field has not been assigned. • The field is named "ID" and the data type is “AutoNumber".

39. Primary Key • The AutoNumber data type automatically updates whenever a record is added so there is no extra work on your part. • This field serves no purpose to the user, so you may also choose to hide this column in the datasheet.

40. Datasheet Records • Adding Records • Add new records to the table in datasheet view by typing in the record beside the asterisk (*) that marks the new record. • You can also click the new record button at the bottom of the datasheet to skip to the last empty record.

41. Sorting Filtering Find Design Sheet/View Toggle Record Selector Navigation Buttons & Record Number New Record Delete Record

42. Editing Records • To edit records, simply place the cursor in the record that is to be edited and make the necessary changes. • Use the arrow keys to move through the record grid. • The previous, next, first, and last record buttons at the bottom of the datasheet are helpful in maneuvering through the datasheet.

43. Deleting Records • Delete a record on a datasheet by placing the cursor in any field of the record row and select Edit|Delete Record from the menu bar or click the Delete Record button on the datasheet toolbar.

44. Introduction to Queries • Queries select records from one or more tables in a database so they can be viewed, analyzed, and sorted on a common datasheet. • The resulting collection of records, called a dynaset (short for dynamic subset), is saved as a database object and can therefore be easily used in the future.

45. Introduction to Queries • The query will be updated whenever the original tables are updated. • Types of queries are • select queries that extract data from tables based on specified values, • find duplicate queries that display records with duplicate values for one or more of the specified fields • find unmatched queries display records from one table that do not have corresponding values in a second table.

46. Query Wizard • Access' Query Wizard will easily assist you to begin creating a select query. • Click the Create query by using wizard icon in the database window to have Access step you through the process of creating a query.

48. Query Wizard • From the first window, select fields that will be included in the query by first selecting the table from the drop-down Tables/Queries menu. • Select the fields by clicking the > button to move the field from the Available Fields list to Selected Fields.

49. Query Wizard • Click the double arrow button >> to move all of the fields to Selected Fields. • Select another table or query to choose from more fields and repeat the process of moving them to the Selected Fields box. • Click Next > when all of the fields have been selected.