Share this:
MANAGEMENT OF DATABASE INFORMATION SYSTEM
Quering database
Queries are the fastest way to search for information in a database. A query is a database feature that enables the user to display records as well to perform calculations on fields from one or multiple tables.
You can analyze a table or tables by using:-
1. Select query or
2. An action query
Action query:-These are queries that are used to make changes in many records at once. They are mostly used to delete, update, add a group of records from one table to another, or create a new table from another table.
Types of action query in Microsoft Access are:-
1. Update-update data in a table.
2. Append query-add data in a table from one or more tables.
3. Make table Query-Creates a new table from a dynaset
4. Delete query-Delete specified records from one or more tables.
Select query
Is a type of query used for searching and analyzing data in one or more tables. It lets the user specify the search criteria and the records that meet those criteria displayed in a dynaset or analyzed depending on the user requirement.
Creating a selected query
1. Ensure that the database you want to create a query for is open
2. Click the query tab, then new
3. In the new query dialog box, choose either to create a query from in Designing view or using Wizard
4. To design from scratch, click design view. The show table dialog box appears from which you can add a table or tables you wish to create in a query form.
5. Click the table from the table/query list and then click add
6. Click close to close the show table dialog box.
7. The query design grid opens. In Microsoft Access it is called query example (Q.BE)
Specifying the search criteria
To search for a particular set of records, you have to enter a conditional statement or statements in the criteria row.
Example:-
If you have a table called employees with one of fields as salary, you can display all the employees earning more than shs.5000 by typing >5000 in the criteria row, salary column.
To define criteria, use either relational or logical operations, Relational operators include
Less than <
Greater than >
Greater than or equal to >=
Less than or equal to <=
Not equal to <>
Equal to =
Logical operators include AND, OR and NOT
AND-Is used to display values in a specific range.
Example:-If you type >4000 AND < 6000 on the criteria row in salary column. All employees who meet this condition will be displayed
OR-Is used to display either one of the two values eg: If you wish to get those employees either in Dar es Salaam or Morogoro.
TO-Display data in a particular range, use the word between.eg in steady of typing >4000 AND <6000, type between 4000 AND 6000.
NOT Is used to display all records except that you do not want to see.
Eg If you type NOT 8000 in salary column of the employees table. All employees’ records will be displayed except those with the salary 8000.
Modifying and updating a query
To delete fields from the query grid.
1. Open the a desired query in design view
2. Select the field column you want to delete
3. Choose delete from the edit menu.
4. Click the save button to save the changes
To adjust the column query in a query
1. Open the desired query in design view
2. Position the mouse pointer at the boundary that separates columns, then drag it to the required size.Alternatively, double click the boundary to auto fit cell content.
3. Click the save button to save the changes.
To modify a criteria statement, select query:-
1. Open the desired query in design view.
2. Modify the criteria statements as desired
3. Click the save button to save changes
4. To test whether the changes have been affected, click the run button to display the results of the query.
Generating Reports
-Report is the flexible means of presenting and displaying your data in a database.
-In a report, data cannot be modified or deleted, you can only view data
-Report is used to summarize and present information from a database while tables are used for identify purpose. Good database software should enable to generate reports and labels.
Creating reports.
Report layout is designed by placing control on the report designer parts of a report.
Report Header-This contain unbound controls that display title of the report
Page Header –Contains heading or tables data items to be displayed in every column.
Detail-Holds bound controls that display data items for the table or query it was created from.
Page footer-Holds the control that is to be displayed on every page such as the page number and date
Report footer-Used to display summary from a report such as the grand total for numerical data in a particular field column.
Creating a Report in a Design View
1. In a database window, click the reports card then new
2. In the new report dialog box, click design view
3. Click the name of the table or query you want to generate a report from
4. Click the Ok button. You will get a report design grid where you can place data controls.
5. From the view menu, click Field list.
6. To design the layout, drag each field from the field list to the layout grid and drop it where you want the data column to appear.
7. Once you finish placing controls, click the save button.
8. In the save as dialog box, enter the name of the report and click ok
9. To view the report click the print preview button alternatively click Print preview from the file menu.
Modify a report layout
To modify header and footers.
1. Open the report in design view
2. Click the report header or footer you want to modify
3. Make the necessary changes and then click modify.
4. Click the print preview button to view the changes
To add more controls onto the report layout
1. Open a report in design view.
2. Display the field list by clicking the field list button or using the view.
3. Select one or more fields in the field list and drag view to the report design view grid.
To resize or move a control.
1. Click the control to select it. Position the mouse pointer on the place holder, until the mouse pointer changes to a double sided arrow.
2. Drag the pointer to resize the control.
3. To move a control, select it and place the mouse pointer on the place holder until it changes to a hand then drag.
Printing the reports
Before printing, you should set the page options i.e. Margins paper size and orientation.
1. Open the database that contains the report you want print
2. Click the reports tab, select the report you want to print, and then click the preview button.
3. On the file menu click print.
4. Set the printer options i.e. the printer type, print range and number of copies.
5. Click Ok to print.
Practical activity:-
You are the database manager Tanesco SACCOS society and you are expected to create a database called Tanesco.
1. Design two tables in the database, Employee and bill with the following fields.
2. Enter the following records in the employees table.
3. Enter the following in the bill table
Explain why duplicate PNO is acceptable in Bills table and not in the bills table.
4. Create a calculated query based on the two tables that will calculate and display the following.
(a)The total bill for Rashid in in the month of January and
(b)The total bill for all the employees
