Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?
Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?
A query is a statement that gives refined data according to the conditions and specifications of the user. It can retrieve the desired data from the multiple data simultaneously and can also update or delete multiple records at the same time. It is a link between the interrelated tables, forms, etc.
Accounting information that is presented in an accounting report is generated by creating and executing various queries under DBMS. A query communicates to Access the kind of information which we require to extract from one or more interrelated tables.
The following are the basics of creating queries in the MS-Access with a set of inter-related data tables.
As Relational DBMS stores data in different tables, so there is no or minimum data redundancy. But a complete view of data that is stored across various tables can be achieved only by executing queries based on SQL. A query shows the records containing fields from across a number of data tables. A SQL statement has a number of parameters such as data definition, query, update, capability to define user-oriented views of database, specify security and authorisation and defining integrity constraints.
Example:
The procedure of creating queries for inter-related data tables using SQL statements is explained below by the help of an example.
SELECT Code, Name, Sum (Amount)
FROM Vouchers INNER JOIN Accounts.
ON (Accounts, Code = Voucher Debit)
GROUP BY Code, Name.
In this query, the ‘Vouchers’ table has been joined with ‘Accounts’ table on the basis of ‘Code’ fields of ‘Accounts’ and ‘Debit’ field of vouchers. The result record set has been grouped on the basis of Code and Name of Accounts.
A query is a statement that gives refined data according to the conditions and specifications of the user. It can retrieve the desired data from the multiple data simultaneously and can also update or delete multiple records at the same time. It is a link between the interrelated tables, forms, etc.
Accounting information that is presented in an accounting report is generated by creating and executing various queries under DBMS. A query communicates to Access the kind of information which we require to extract from one or more interrelated tables.
The following are the basics of creating queries in the MS-Access with a set of inter-related data tables.
As Relational DBMS stores data in different tables, so there is no or minimum data redundancy. But a complete view of data that is stored across various tables can be achieved only by executing queries based on SQL. A query shows the records containing fields from across a number of data tables. A SQL statement has a number of parameters such as data definition, query, update, capability to define user-oriented views of database, specify security and authorisation and defining integrity constraints.
Example:
The procedure of creating queries for inter-related data tables using SQL statements is explained below by the help of an example.
SELECT Code, Name, Sum (Amount)
FROM Vouchers INNER JOIN Accounts.
ON (Accounts, Code = Voucher Debit)
GROUP BY Code, Name.
In this query, the ‘Vouchers’ table has been joined with ‘Accounts’ table on the basis of ‘Code’ fields of ‘Accounts’ and ‘Debit’ field of vouchers. The result record set has been grouped on the basis of Code and Name of Accounts.
