MS Access
Ø PRACTICAL 1
CREATE A NEW DATABASE
1. Click on the File menu from the menu bar.
2. Choose New from the File menu to display New dialog box
3. Choose Database icon on General tab from the tab list.
4. Click on the OK button to display File New Database dialog box.
5. Type the file name in File name combo box.
6. Click on Create button to finish process.
Ø PRACTICAL 2
CREATE A NEW TABLE
To create a Table using Design View:
1. Click the Tables icon in the Database Window.
2. Click on the New Button.
3. In the New Table dialog box, select DesignView and click on the OK button.
Ø PRACTICAL 3
ADD A FIELD NAME: To add a field name:
1. Enter the name of the required field into the empty cell immediately below the Field Name Header. For instance you could add a field name called Roll no.
2. Press the Tab key to move to the next cell.
3. In the New Table dialog box, select Design View and click on the OK button.
Ø PRACTICAL 4: SET THE DATA TYPE: To set the Data type:
1. The next is Data Type. This allows you to control what type of data is to be entered into the field. Click on the down arrow to display a list of available options.
2. Select any data types for instance select Number for numeric data. Select Text for text data, and so on.
ADD AFIELD DESCRIPTION:Adding field description:
1. Press Tab key to Click on Description cell to enter description of desired field. It is an optional field property that allows you to explain the function of the field.
2. Later when a user clicks within a field that contains a description will be displayed within the Status Bar.
Ø PRACTICAL 5:
ADD MORE FIELDS: To Add More Fields:
1. Press the Tab key to add further field names, properties and descriptions, as required.
SET A PRIMARY KEY: Setting a Primary key:
1. In Table Design View, select the field you want to assign as the primary key. You can click somewhere in the field or click on the field selector to the left of the field.
2. Choose Primary Key from the Edit menu or Click on the Primary Key icon on the Standard toolbaror right click on the Field Name column and choose Primary Key from the menu.
Ø PRACTICAL 6: SAVE A NEW TABLE: Saving the new table without setting a primary key:
1. Click on Close icon at the top-right of the table design window and a dialog window will be displayed asking if you wish to save your changes.
2. Click on Yes button.
3. After clicking Yes button, you will be then asked to supply the table name.
4. Enter the name required, and then click on the OK button. The system will then offer to supply a Primary Key.
5. Click on the Yes button to create a primary key now. Access will add another field to your table. This is called a counter field that will act as a unique id for each record in the table but it better to specify primary key yourself before saving the table design.
Ø PRACTICAL 6 SAVE A NEW TABLE: Saving the new Table with setting the Primary key:
1. Click on Close icon at the top-right of the table design window and a dialog window will be displayed asking if you wish to save your changes.
2. Click on Yes button.
3. Enter the name required, and then click on the Ok button.
Ø PRACTICAL 7 HOW WOULD YOU FIND AND REPLACE DATA IN A TABLE: Find data:
1. To find data in a specific field, click on the field.
2. Click on Edit menu from the menu bar.
3. Choose Find from Edit menu or press Ctrl+F. The Find and Replace dialog box will open.
4. Enter the data you want to search for in the Find What text box. The field on which you clicked will be displayed by default in Look In list box.
5. If you want to change the search range from one field to entire table, click on the arrow on the right of the Look In list box and choose the name of the table.
6. The Match list box gives you options for the way in which the given string is to be matched. You have three choices shown in figure below.
Ø PRACTICAL 7: Any Part of Field:This locates strings that have the search string you gave as a sub-string. If you typed in ‘12’ in the Find what text box it will match any string containing ‘12’ as a sub-string e.g. 1234, 121, 3412, but not 12 or 132.
- Whole field: This locates only those entries that exactly match your search string.
- Start of Field: This locates strings that begin with the search string you typed in. The search string ‘12’ will match 1234, 124, 12 but not 1123, 3412.
1. Click in the field where you want to search.2. Choose Replace from the Edit menu or press Ctrl+H.
3. In the Find What box, enter the text you want to replace.
4. In Replace With box, enter the text you want to substitute.
5. Change as options as necessary and click on the Find next button.
6. Click the Replace button
7. Click on Replace All button to replace all the matching data in one step.
Ø PRACTICAL 8: Choose Relationships from the Tools menu to display Relationships window and show Table window to add tables to the Relationships window.
ADD TABLES TO THE RELATIONSHIP WINDOW:
To add Tables to the Relationship window:
1. Select tables from table’s list box under Tables tab using the Ctrl key or Shift key.
2. Click on the Add button to add tables to the Relationships window.
Ø PRACTICAL 8CREATE A RELATIONSHIP BETWEEN TWO WINDOWS?
To create a Relationship between two Tables:
1. Drag the field name from the first table to the second table. If the field in one Table is primary key (it will appear in bold), drag the primary key to the corresponding key in the other table.
2. The table from which you drag the field is called the Primary table; the table in which you drop it is called the related table. The Relationships dialog box will appear as shown below.
3. Select the Enforce Referential Integrity box to enforce referential integrity between the two tables.
4. Click on the Create button to create the relationship. Access will show the relationship between the two tables as a join line connecting the related fields.
Ø PRACTICAL 9HOW WOULD YOU CREATE A QUERY USING SIMPLE QUERY WIZARD?
2. Click the New button to display a New Query dialog box.
3. Select the Simple Query Wizard option.
4. Click on the OK button to move to the next Wizard page.
5. Click on the down arrow in the Tables/Queries text box to illustrate available queries and tables from which you can select the fields.
6. Select the field and click on the right-pointing arrow to add a field.
7. In the example below, we have added roll no#, Studentname and Date of Birth.8. When you have finished adding fields, click on the Next button, and another dialog box will be displayed.
9. Click on the Next button to continue.
10. Give the query name, using the dialog box showed below.
11. Click on the Finish button to complete the query. The results are displayed as showed below.
Ø PRACTICAL 10
HOW WOULD YOU CREATE A SIMPLE QUERY USING DESIGN VEIW?
To create a simple query using Design view:
1. Open the database and click the Query tab in the Database window.
2. Click the New button to display a New Query dialog box.
3. Select the Design View option.
4. Click on the OK button.Access then open the Select Query window and displays the Show Table window on top. The Show Table window allows you to select the table (or tables) upon which you wish to perform a query.
5. Select tables from the tables list box under Tables.
6. Click on the Add button to add tables to the Query window.
7. After adding table, click on the Close button to close ShowTable window.
8. Now you have to specify the name of the fields in Query Grid.
There are three ways to select field:
· Select and drag the field name from table and drop it to the first field cell of the first column of the Query Grid, which is where the field name must be placed. Select and drag the next field name you want from the table and drop it to the first field cell of the second column. Repeat the procedure for each field you want to display.
· Double click on the field name in the table, the field name will be added to the first field cell of the field column. Double click on the second field you want to add. Repeat this procedure for each field you want to display.
· Click on down arrow of Field cell, a drop down list will be displayed, select field name from list. Click on down arrow on the second column of the field cell, select the field name. Repeat this procedure on each column for each field you want to display.
9. If you want to change sort order of any field, click on down arrow of Sort cell of desired field, a drop down list box will be displayed.
10. Select Ascending or Descending order to set Sort order.
11. In the Criteria row, enter the expression; Access should use to evaluate whether or should include or exclude a record.
12. Click on Close icon button on the top-right of the Query window. Access will ask you to save changes.
13. Click on the Yes button to save the query. Access will ask you to enter query name.
14. Enter the Query name and click on OK button to complete the query.
15. Double click on the query name to run query.
Ø PRACTICAL 11HOW WOULD YOU CREATE A QUERY USING MULTIPLE TABLES
To create a query using multiple tables:
1. Open the database and click the Query tab in the Database window.
2. Click the New button to display a New Query dialog box.
3. Select the Design View option.
4. Click on the OK button. Access then open the Select Query window and displays the Show Table window on the top. The Show Table window allows you to select the table (or tables) upon which you wish to perform a query.
5. Select tables list from table’s list box under Tables tab by using the Ctrl key or Shift key.
6. Click on the Add button to add tables to the Select Query window.
7. After adding tables, click on the Close button to close Show Table window.
8. Now you have to specify the name of fields in Query Grid. There are three ways to select field:
- Select and drag the field name from table and drop it to the first field cell of the first column of the Query Grid, which is where the field name must be placed. Select and drag the next field name you want from the table and drop it to the first field cell of the second column. Repeat the procedure for each field you want to display.
- Double click on the field name in the table, the field name will be added to the first field cell of the field column. Double click on the second field you want to add. Repeat this procedure for each field and each table you want to display.
- Click on down arrow of Field cell, a drop down list will be displayed, select field name from list. Click on down arrow on the second column of the field cell, select the field name. If you want to add field name from second table, click a down arrow on the cell of Table row, select the table then select the field name again. Repeat this procedure on each column for each field you want to display.
OPEN THE RELATIONSHIP WINDOW:
- To open Relationship window:
Click on Tools menu from the menu bar.
- To create a query using Simple Query Wizard:
- Open the database and click the Query tab in the Database window.
- If you have selected relationship between the tables, Access will automatically match the records by using primary key and foreign key.
- If you want to change sort order of any field, click on down arrow of Sort cell of desired field, a drop down list box will be displayed.
- Select Ascending or Descending order to set Sort order.
- In the Criteria row, enter the expression; Access should use to evaluate whether or should include or exclude a record.
- Click on Close icon button on the top-right of the Query window. Access will ask you to save changes.
- Click on the Yes button to save the query. Access will ask you to enter query name.
- Enter the Query name and click on OK button to complete the query.
- Double click on the query name to run query.
Ø PRACTICAL 12 HOW WOULD YOU CREATE A FORM USING FORM WIZARD?
To create a Form using Form Wizard:1. Click on the Forms icon in the Database Window.2. Choose the New button, which will display the New Form dialog box.
3. Select the Form Wizard.
4. Choose the table or query where the objects data will come from.
5. Click on the OK button to display the next page of the Form Wizard.
OR6. Click on the Forms icon in the Database Window.
7. Double click on Create form by using Wizard, which will display the Form Wizard.
8. Select the table Student from the Table/Queries drop-down list if it is not already selected there by default. This would make its entire field appear in the Available Fields window below.
Ø PRACTICAL 9. Click on the field that you want to add to the form, and then click on the right pointing arrow button to add it to the Selected Fields section of the dialog box.10. Repeat this procedure so that all the required fields added.
11. Click on the Next button when you have added the required fields. The next dialog allows you to define the layout of the form.
12. Choose the desired option and then click on the Next button.
13. Choose Standard option form the list box for background (style) that you want for your form.
Ø PRACTICAL 1214. Click on the Next button to continue. This next page of the Form Wizard allows you to name the form and set final options for using the form.
15. Type a name of form in the text box.
16. Click on the Finish button to complete the wizard.
17. Double click on the form name (student) in the Database Window to open the form. The form will be displayed as illustrated.
Ø PRACTICAL 13 HOW WOULD YOU CREATE A FORM USING DESIGN VIEW?
To create a Form using Design view:
1. Click on the Forms icon in the Database Window.
2. Choose the New button, which will display the New Form dialog box.
3. Select the Design view.
4. Choose the table or query where the objects data will come from.
5. Click on the OK button to display Design form.
6. Select and drop the field name from table and drop it to the Design form. Select and drag the next field name from the table and drop it toDesign form. Repeat this procedure for each field you want to display.
7. Click on the Close button on the top-right of the Designform. A save as dialog box will be displayed.
8. Type the name in Form Name text box.
9. Click on the OK button to save form.
Ø PRACTICAL 14 HOW WOULD YOUADD, EDIT RECORDS THROUGH FORM?
To add new record through form:
1. Click on the Forms icon in the Database Window.
2. Double click on the Form name to open.
3. Click on the New Record button to move to an empty record.
4. Fill all fields with the data of students.
To edit Record through Form:1. Click on the Forms icon in the Database Window.
2. Double click on the Form name to open.
3. Change the student Name from”Amna” to “Areej”.
4. Click on the top-right button on the form to save and close.
Ø PRACTICAL 15
HOW WOULD YOU CREATE A SINGLE TABLE REPORT USING REPORT WIZARD?
To create a report using report wizard:
1. Click on the Reports icon in the Database Window.
2. Choose the New button, which will display the report dialog box.
3. Select the Design view.
4. Choose the table or query where the objects data will come from.
5. Click on the OK button to display Report Wizard dialog box.
OR
6. Click on the Form icon in the Database Window.
7. Double click on Create Formby usingWizard, which will display the Form Wizard.
8. Select the table Student from the Tables/Queries drop-down list if it in not already selected there by default. This would make its entire field appear in the Available Fields window below.Ø PRACTICAL 15
9. Click on double right pointing arrow to move all the Available Fields to the Selected Fields.
10. Click on the Next button to continue.
11. The next page that is displayed allows you to add grouping levels.
12. Click on the Next button to continue to the next page of the Report Wizard.
13. Click on the first drop-down list box; choose the Roll no field for sort order of records.
14. Click on the Next button to continue to the next page. The next page of the Report Wizard allows you to determine the layout of the report.
15. Selected the report layout Tabular form the Layout frame box.
16. Select the report orientation Portrait from the Orientation frame box.
17. Click on theNext button to continue. The next page of the Report Wizard allows you to select from a range of styles.
Ø PRACTICAL 15
18. Select the report style Corporate from the list box.
19. Click on the Next button to continue. The next page of the Report Wizard is the final page, which allows you to give a name to the report.
20. Enter the report name in the top text box.
21. Select the Preview this report button.
22. Click on the Finish button to generate and preview this report.
Ø PRACTICAL 16
HOW WOULD YOU CREATE A REPORT USING DESIGN VIEW?
To create a Report using Design View:
1. Click on the Reports icon in the Database Window
.2. Choose the New button, which will display the NewReport dialog box
.3. Select the Design View.
4. Choose the table or query where the objects data will come from.
5. Click on the OK button to display blank report template.
6. Click on View from the menu bar. A pull down menu appears.
7. Choose Toolbox from the View menu. The standard toolbox will be displayed.
Ø PRACTICAL 16
8. Click the right mouse button on the Form. A pull down menu will be displayed
.9. Choose the option Report Header/Footer from the menu. The report Header/Footer bar will be appeared on the Form.
10. Click on the Label tool from the Toolbox .The label tool will be selected.
11. Move the mouse pointer into the Report Headersection of the report temple.
12. Press the left mouse button and draw a rectangle (do not leave the button until you draw the rectangle).
13. After draw the rectangle, an I-beam cursor will be displayed. Type the page header student Record.
14. Click the Label tool from the Toolbox. The label tool will be selected.
15. Move the mouse pointer into the Page Header section of the report template. Press the left mouse button and draw a rectangle (do not leave the button until you draw the rectangle).
Ø PRACTICAL 16
16. After drawing the rectangle, an I-beam cursor will be displayed. Type the column name Roll no. Repeat this procedure for each column.
17. Select the columns by holding Shift key and click the mouse button.
18. Change font name to Times New Roman and font size to 11 from Formatting toolbar.
19. Select and drag the field name Roll no from Student Table and drop it to Detail section under the column name Roll no. When you drop the field name, you have two objects, field heading table and field name text box.
20. Click on the field heading label and press the Delete button to delete it.
21. Select the Roll no text box and align left to the column name Roll no.
22. Repeat above procedure for each field name.
23. Click on each field name and set the width according to column name width
Ø PRACTICAL 16
24. Click on Close icon on the top-right corner of the Report window. Access will display the dialog box for report saving.
25. Click on the Yes button to save changes. A save as dialog box will be displayed.
26. Type the report name Student in the Report Name text box.
27. Click on the OK button to finish the report.
28. Double click on the report name in student in the Database Window to open and display the report.
Ø PRACTICAL 17
HOW WOULD YOU OPEN, VIEW AND PRINT EXISTING REPORT? To open, view an existing Report:
1. Open the database and click on the Reports icon in the Database window.
2. Double click on the report that you want to view. The report window will be displayed.To print an existing Report:
1. Open the database and click on the Reports icon in the Database window.
2. Double click on the report that you want to view.
3. Click on the File from the menu bar.
4. Select Print from the File menu.
OR
5. Press Ctrl+P to open the print dialog box.
6. Click on the Setup button. The Page Setup dialog box will be displayed. Set margins, columns then click on the OK button to close Page Setup window.
7. Click on the OK button to print the report.
No comments:
Post a Comment