M icrosoft Access

1. Microsoft Access

2. Microsoft Access • Microsoft access is a database programs that allows you to store retrieve, analyze and print information. • Companies use databases for many purposes; for managing customer files for tracking orders and inventories, and for marketing purposes.

3. Microsoft Access • A databases allows to users to access and manage thousands, even millions of peaces of data in an organized, efficient and accurate manner. • Tables are the foundation of the database, because they store the data and provide the structure by which it is organized.

4. Microsoft Access • Each table stores a set of related data. • Tables are made up of records that include related information about one object, person, event or transaction. • Records are displayed in rows. Each category of information in a record is known as a field. • Field are displayed in columns; the field name appears in the database table as a column heading.

5. Microsoft Access • To start Microsoft Access; • Start programs find and click Microsoft Access. • Microsoft Access files extension is .mdb • To open a Microsoft Access file • Double click on the file or start Microsoft Access open.

6. Microsoft Access • To rename it • Right click on the file select rename and write new name. • The database window lists all the tables in the database. Object buttons are displayed down the left side of the database windows.

7. Microsoft Access • If you click the Queries object button; the queries button is displayed on the left side of the window along with the other object buttons. • It displayed the only query in the database. A query is used to sort, search, and limit the data to only those record that you need to examine.

8. Create A New Database • When you design a table, you need to determine what field will be included. To answer this question, you should consider what kinds of information should be included in a printed report or what information you want to see if you look at a single record on th screen.

9. Create A New Database • There are six fairly universal rules for deigning tables that were originally proposed by Dr. Edgar F. Codd of IBM in the 1960s, and which steel hold true today. When you apply these rules to your table you are normalizing them.

10. Normalization • Rule 1: field should be atomic; that is, each piece of data should be broking down as much as possible. For example, rather than creating a field” name ”, you would create to fields; one for the first name and the other for the last name.

11. Normalization • Rule 2 and 3: each record should contain a unique identifier so that you have a way of safely identifying the record. • The unique identifier is called a Primary Key. You may select one of the fields in the table as the primary key if that field would never contain duplicate values for different records. For example a social security number is ideal, because no two people have the same one. If none of the field is suitable, the program can add a counter field that will automatically assign a unique number to each record as it is entered.

12. Normalizaion • Rule 4: the primary key should b short, stable and simple. • Rule 5: every other filed in the record should supplied additional information about the person or thing that is uniquely identified by the primary key.

13. Normalization • Rule 6: information in the table should not appeared in more than one place. • You may need to create more than one table in a database and connect them together. Linking more than one table together is a feature of a relational database.

14. Creating a New Database • Start a Microsoft Access; File  New Database. • Access suggest a default name for the new database, however you can assign a different name to database. • After you name the database the first time, you wont to do it again. As you add or edit new records, Access updates the database automatically. • Click Create button.

15. Creating a New Table • After you create your database, you can add tables to it to store your information. • A database is built on one or more tables, each of which holds a distinct set of information. • The table defines the structure of the data, what pieces of data you enter and in what order.

16. Creating a New Table • When you create a new table, you can add any fields you want. Field are the individual pieces if information that together make up a record; for example an address is a field. • To add a field, you type a field name and than select a data type which defines the kind of information you can enter into that field.

17. Creating a New Table • Double click on the Create table in design view option. • To add fields to the table, you must first enter the field name, data type and description. • You can create a field name using up to 64 character.

18. Saving a Table Design and Creating Primary Key • The first time you save the table’s design, you are prompted to assign a name. • To saving new tables you should assign or create a primary key field for each table in your database. • Each record primary key field contains a value that uniquely identifies it; no two record can have the same value in there primary key field.

19. Saving a Table Design and Creating Primary Key • You can use this feature to your advantage when you need to establish a relationship between one table to another. • A relationship connects a field in one table to a filed in a second table.

20. Saving a Table Design and Creating Primary Key • To Save a table design • File Save and type a new name. • Access displayed a reminder that no primary key has been defined. You are not required to use a primary key, but it is a good idea to include one. • An easy way to create a primary key field is to have Access create a counter filed that automatically assigns a different number to each record in your table.

21. Saving a Table Design and Creating Primary Key • Click the Yes button. • Access saves the table and adds a counter field named ID with an AutoNumber data type. Notice the key symbol in the row selector for the ID field. • To make another filed in the database the primary key, click the row selector for the field you want. Then click the Primary Key button on the toolbar.

22. Adding Fields • You can add new fields to store this additional data. However that if you have already added records to the table, any new fields in those existing records will be empty until you type information into them.

23. Editing Fields • As you create your database, you may want to modify the structure. • Changing the field type may have an effect on the data in your table. • In the design view table, position the table on the field and change it.

24. Move and Delete Fields • Move; • Select the field you want to move, and drag and drop to the new location. • Delete; • Select the field which you want to delete click the rows button or press backspace or delete key on the keyboard.

25. Adding Records • In design view, you can make changes to the fields in the tables (change a field name add a fied change the data type and so on) • Than when you wont to work with the data in the table you switch to datasheet view.

26. Adding Record • To add record • Open your database and switch on the datasheet view. And than start to enter your data. • Each of the field name appear along the top of the window.

27. Adding Record • Moving amount rcords • You can move among the record in several ways. • If you can see the record you wont on the screen, you can simply click it to select it. • Or scroll through the records until you can get to the one you wont. • Or you can use the navigation button display along at the bottom of the window. Or te arrow key from t keybord.

28. Adding Record • Editing record • The first step in editing record is to move to the record that you want to change. Next, you have to move to the field that you wont to edit.

29. Adding Record • Inserting and deleting records • You can add a new record t the and of the table at any time. • To copy the record; click in the record selector to select the entire row and click copy button. Then select your new place an click the paste button. • To delete record; select your field and press the delete button.

30. Adding Record • Finding Record • In a table with many records and many fields, it may be time consuming to scroll through the records and fields to find a specific record. • Instead, you can search for a specific field entry in order to find and move quickly to a record.

31. Adding Record • To fing a Record • Clicking anywhere in the field • Cvlick the find button • Here you tell access what you wont to find and where you wont to look. • Click the find next button.

32. Adding Record • Sorting records. • Access sorts the entries alphabetically or numerically, based on the entries in that field. • Click in the field that you wont to sort • Click the sort button, you can also use the Record, Sort command. • To sort by multiple, adjacent field select the field name for the first sort, hold down the shift key, and select the second field to sort. • When you completed a sort if you wish to return records to there original order, chose Records and click Remove Filter / Sort.

33. Querying Your Database • The primary reason you spend time entering data into a database, is so that you can easily find and with the information. • For example, you may want to display all of your contacts in India. To do so, you would create a query.

34. Creating a New Query • To create a query • Open your database, click the Queries object button. • Select Create Query in design view. • Select the tables you want to use in your query and close the Show Table window. • After you open a new query and select a table, you see a window divided in to two parts. The top half of the query window displays a list of the fields from the table you selected. The lower part you see the design grids.

35. Creating a New Query • To create a query • Field: Select the fields that you want to use in Query. • Table: Select your table name • Sort: You can sort your records in ascending or descending order • Show: if it is selected, when your query is running it shows the field in results if it is unselected it doesn’t show the field on the result. • Criteria: if you have any criteria (like equal to 3) you should write here. • OR: if you have more than one criteria for example your criteria is equal to 3 or 5. You should write 3 on criteria part and write 5 to Or part.

36. Creating a New Query • To create a query • After to prepare your query to see the result you have to run your query. • To Run: Query  Run or click a run button on the standard toolbar. • You have to save your queries. Don’t forget !!!!! • To edit your query  Right click on the query and select Design View.

37. Creating and Using Forms • Open your database, click the Forms object button. • Click New button on the standard toolbar. • AutoForm: Select Columnar and select the table or query you want to use in your form. • The form will be created automatically than you must save your form. • You can use forms to add, delete or edit records.

38. Creating and Using Forms • Sometimes the form wizards do not create exactly the form you want. When that happens, you can start a blank form and create a one better suits your needs. The form can include any text, fields, and other controls. • Open your database, click the Forms object button • Select design view and select the table you want to use in your form. • Add the fields you want to use in forms by drag and drop. • You can use Toolbox menu to add picture or text. • You can change the location, color or size.

39. Creating Reports • Open your database, click the Report object button • Click new button on toolbar. • Select Report Wizard and select the table or query you want to use in your report. • Add the fields you want to use in reports by clicking add button and click next button. • You can modify a report design; you can add a style or sort data.