Download as PPTX, PDF, TXT or read online from Scribd
Download as pptx, pdf, or txt
You are on page 1/ 25
Grade - 7 Unit -2
Working With MS-Access OBJECTIVES
ABOUT PRIMARY KEY, FOREIGN KEY,
ABOUT RELATIONSHIPS,
ABOUT QUERIES,
FORMS & REPORTS
RECAP: 1. A database is a collection of data or information of similar set of records. 2. Database management system is collection of interrelated data. 3. MS- Access provides automated methods to create, store and retrieve large amount of data. 4. MS-Access is a relational database management system, it stores information in multiple related tables and treated as single storage area. 5. MS-Access data types are Short text, Long text, number, date/time, currency, auto number, yes/no, hyperlink etc. 6. Table is a database object that stores data in rows and columns format. 7. Creating table using design view is used to create the table manually and it is the most common way of creating a table. PRIMARY KEY A primary key is the column that uniquely identifies each row in a table.
Rules for a primary key:
It uniquely identifies each record. It should not contain a null value. Each table can only have one primary key.
Example: Cust ID [customer ID is that primary key in Customers table]
Steps to Create Primary key for Customers Table Create the Customers table using ‘Table design button’ and apply the Primary key. 1. Click on Create tab on the ribbon. 2. Click on ‘Table Design’ button of ‘Tables’ group. Access opens a new table in Design View, allowing you to add fields to the table’s design. 3. Enter Field names with their Data types. 4. Click on the Cust ID Field name. 5. Click on Design tab and select Primary key button. A small key icon [ ] appears on the left side of the Cust ID field, this shows that the Cust ID is the primary key field in the table. FOREIGN KEY A foreign key is a field in a relational table that matches the primary key column of another table.
Creating a Relationship using Foreign Key
1. Click on Database Tools and select ‘Relationship’ button of Relationships group. 2. Show Table dialog box will appear. 3. Select the table names [Customers, Orders] then click on Add button. 4. Then click close button of Show Table dialog box. 5. Now both the tables will display in Relationship window. 6. Select the Cust ID column in Customers table and drag towards Cust Id column in the Orders table.
Relationship will be created between both the tables.
Queries Query is database object. A Query is like a general question that we ask to get the specific information from the database. It is used to extract specific information from database based on certain condition. Query is used to apply a filter to the table’s data, so that you only get the information that you want. It is used to display a group of records that fulfills a certain condition. The result of query is also displayed in the form of table. Creating queries can be done in two different ways Query using Query Wizard. Query using Design View.
Query using Query Wizard: Query wizard is a step by step
process used to retrieve records from database table. For Example Run a query to retrieve the details i.e., Cust ID, Cust Name, Address from customers table and product name and order date from Orders table. Follow the below steps. 1. Click on create tab. 2. Select Query Wizard button of Queries group. New Query dialog box will appear. 3. Select Simple Query Wizard and click on OK button. Simple Query Wizard dialog box will appear. 4. Select the table names and the columns from the list. [From Customers table select only Cust ID, Cust Name, Address and from orders table select only product name and order date] 5. Click on the next button. 6. Specify the title as Customer Query and click on Finish button. 7. Result will be displayed as shown below. Query using Design view: The Design view allows more control over a query design. Design view makes it easy to find the answers of some very complex questions.
For Example Run a Query to retrieve the order details of Customer whose Cust ID is 101 Steps to create a query using Design View 1. Click on Create tab and select Query Design button of Queries group Show table dialog box will appear. 2. Select the tables [Customers, Orders] and click Add button. The tables will be added to Query window. 3. Specify the table and fields to be extracted as shown in below.
4. Specify the criteria i.e.,
Cust ID 101 as shown below.
5. Click on Run button on Design tab to run a query.
The result of the query will be as
shown below. Saving a Query To save a query. 1. Right click on Query and select save option. 2. ‘Save as’ dialog box will appear. 3. Type the Query name. 4. Click on OK Query will be saved. Forms A form is a database object. Forms are like display cases in stores that make it easier to view or get the items that you want. It is used to edit, enter or display the data from a table or a query. It is easy to interact with database. Forms offer a friendly way of viewing the data, in that they show a single record at a time.
Creating a Form using Form Wizard: The Form Wizard provides step by step process to design a form. Steps to create form using Form Wizard: 1. Click on Create tab and select Form wizard option.
Form wizard dialog box will appear.
2. Select the table/query from the drop down.
3. Select the columns that are to be included and click on Next button. 4. Select the Form Layout and click on Next button.
5. Enter a title to the form and
click on Finish button. To enter a new record into a table from Form.
1. Go to Last record and click on next record button.
2. Enter the new customer’s information in the form and click next record button
3. Newly added customer’s information will be added in the Customers table.
Reports A report is a database object. It is used to display and print data in an organized manner. Creating Report using Report Wizard: Report Wizard provides step by step process to create a report.
Step to create a Report
1. Click on Create tab 2. Select Report Wizard option of Reports group Report Wizard dialog box will appear. Select the fields to display in the report. 3. From Customers table select product name and delivery status. 4. Select ‘By order’ to view the data.
5. Mention the grouping levels,
From, the list select Cust ID and click on Next button.
6. Select the sort order and mention the column to be sorted and click on Next button. 7. Select the layout as tabular and Orientation as Portrait for the report and click on Next button.
