Database & MS Access Queries writing

Defining Queries

When data has to be reviewed, added, changed or deleted from the database, a query is used. Queries are also used to answer very specific questions about the data that would be difficult to answer directly by just looking at the table data. Queries can be used to perform calculations on the data, to filter data and to summarize the data.

1.      On the Create tab, in the query group, click query design. A show Table dialog box appears.

2.      In the Show Table dialog box, choose the table on which query has to be based and close the show Table dialog       box.

3.      In the selected Table, double click the fields that have to be added to the query

     4.      On the Design Tab, in the Results group, click Run.

The query will be executed and the result is displayed in the grid as shown below.

Reports

A Report consists of information from tables and queries that is stored with a particular report design such as labels, headings and graphics.

1.      In the Navigation pane, select the query or Table on which the report has to be based.

2.      On the Create Tab, in the Reports group, click Report

3.      The Report will be created and displayed

Once the report is created, its layout can be modified in the Report design view.

Reports can be created using the Report wizard tool also.

  A Small Example in Access 2007

We wil create a small database for employees in an organization using MS Access 2007. Make two tables T_Employee_dtls and T_Dept_master database tables with the following structure:

T_Employee_Dtls

Define Emp_Code and Dept_code as the Primary keys for the table

 T_Employee_dtls and T_dept_master respectively. Then define the relationship between the two tables as explained above in section 5.3.7. 

Add data to the table and then define the following queries as explained above:

SELECT T_Employee_dtls.Emp_Name, T_Employee_dtls.Emp_Address

FROM T_Employee_dtls;

SELECT T_Employee_dtls.Emp_Name, T_Employee_dtls.Emp_Age,

T_Dept_Master.Dept_Name

FROM T_Dept_Master INNER JOIN T_Employee_dtls ON

T_Dept_Master.Dept_Code = T_Employee_dtls.Dept_Code;

Reports can be generated based on these queries or different queries can be

written to generate various reports.