Sorting, Filtering, and Creating Relationships.pptx

Access

1. Microsoft Access Chapter:3 Sorting , Filtering, and Creating Relationships You can sort Access data so you can view records in the order you want to view them, and you can filter data so you only see the records you want to see. This lesson teaches you how to sort and filter an Access table . Access data is stored in multiple tables. Relationships join tables together so you can work with the data from multiple tables. This lesson also teaches you how to create relationships. Department of Computer Science Semester 2 Lecturer Yasir A. Mohamed

2. By sorting, you can put a column of information in alphabetical, numerical, or date order. You can sort in ascending order (alphabetical from A to Z, lowest number to highest number, earliest date to latest date) or descending order (alphabetical from Z to A, highest number to lowest number, latest date to earliest date). You can also sort within a sort. For example, you can sort by state and then sort within each state by city. When sorting within a sort, perform the innermost sort first . For example, if you are sorting by state and then city, sort the city first and then sort by state. Sort a Table

3. Click the column label for the column you want to sort. Activate the Home tab. Click the Ascending or Descending button in the Sort & Filter group. Access sorts the column in ascending or descending order. Steps of sorting table data

4. Activate the Home tab. Click the Clear All Sort s button in the Sort & Filter group. Access clears all of the sorts you have applied. Steps of removing sort table data

5. You can apply a filter to see only the records you want to see. For example, perhaps your database contains students from the semesters of Sem1 , Sem2 , and Sem5 and you only want to see the students from Sem2 . You can filter your data so only Sem2 students display . Filter a Table

6. Each time you apply a filter to a column, it replaces any previous filter you applied to that column. For example, if you apply a filter so you only see students in Sem2 , and later you apply a filter to Sem1 so you only see students in Sem1 , Access clears the Sem2 filter and then applies the Sem1 filter . Cont…

7. Click the column label for the column you want to filter. Activate the Home tab. Click the Filter button. A menu appears . Uncheck the items you do not want to appear, making sure only the items you want are checked. Click OK. Access filters your data and displays the word Filtered at the bottom of the window. Tip : After you apply a filter, you can use the Toggle Filter button to toggle the application of the filter on and off. Steps of applying a filter:

8. There may be times when you may not want to display a certain column or set of columns. In such cases, you can temporarily hide the column or columns from view. Later, if you want to display them column again, you can unhide them. Hide Columns

9. R.Click the column. Choose Hide columns Click ok. Hide columns s teps

10. After you have created a table for each subject in your database, you have to give Access a way to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once. For example, the form shown here includes information drawn from several tables: Access relationships

11. A one-to-many relationship Let’s use an order tracking database that includes a Customers table and an Orders table as an example. A customer can place any number of orders. It follows that for any customer represented in the Customers table, there might be many orders represented in the Orders table. The relationship between the Customers table and the Orders table is a one-to-many relationship. Types of table relationships

12. A many-to-many relationship Now let’s look at the relationship between a Products table and an Orders table. A single order can include more than one product. On the other hand, a single product can appear on many orders. Therefore, for each record in the Orders table, there can be many records in the Products table. In addition, for each record in the Products table, there can be many records in the Orders table. This relationship is called a many-to-many relationship . Note that to detect existing many-to-many relationships between your tables, it is important that you consider both sides of the relationship.

13. A one-to-one relationship In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. This relationship is not common because, most often, the information related in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. When you do identify such a relationship, both tables must share a common field.

14. Close all tables and forms. (Right-click on the tab of any Object. A menu appears. Click Close All .) Steps of creating relationship

15. 2. Activate the Database Tools tab. 3. Click the Relationships button in the Show / Hide group. The Relationships window appears.

16. 4. If anything appears in the relationships window, click the Clear Layout button in the Tools group. If you are prompted, click Yes . 5. Click the Show Table button in the Relationships group. The Show Table dialog box appears.

17. 6. Activate the Tables tab if your relationships will be based on tables, activate the Queries tab if your relationships will be based on queries, or activate the Both tab if your relationships will be based on both . 7. Double-click each table or query you want to use to build a relationship. The tables appear in the Relationships window. 8. Click the Close button to close the Show Table dialog box . 9. Drag the Primary table’s primary key over the related table’s

18. Assignment 1. Explain about relationship of Microsoft access? 2. Define the flowing? Sorting, Filtering, and Creating Relationships 3. List adding and removing sort steps? 4. State steps to create Filter in field ? 5. Create your own database and create tables and also do work wich is different what we do in Microsoft access relationship?

19. You can find me at: [email protected] [email protected] www.cabeeyeitgroup.com Any questions? Thanks!