It is a well know fact that Relational Database management system stores data in different table (relations) so that there is no or minimum data redundancy. But for a complete view of data stored across various tables is achieved only by executing Queries based on SQL. A query is capable of displaying record containing field from across a number of data tables.
In other words SQL has statement for data definition, query and update. Besides this, it has the ca-pability to define user oriented views of database; specify security and authorisation, define integrity constraints and various other operations. Various SQL statements are used to create queries for inter-related data tables.
Some of the basics of creating queries in MS Access with a set of inter related data table are here with the help of the following statements.
| SELECT |
Code, Name Sum (Amount) |
| FROM |
Vouchers INNER JOIN Accounts |
| ON |
(Accounts Code = Voucher. Debit) |
| Group BY |
Code, Name |
If we talk about the above query the vouchers table has been joined with Accounts table on the basis of Code field of Accounts and Debit field of Vouchers. The result record set has been grouped on the basis of Code and name of accounts. The sum of amount of each group has been ascertaining and displayed.
We can take another example to understand it better:
| SELECT |
Debit As code, Name Category |
| FROM |
Voucher, Accounts, Account type |
| WHERE |
Debit = code AND Type = Catld |
| AND |
Category = "Expenses" |
In the above query, vouchers table, Account table and Account type table are joined on the basis of Debit Field, code field respectively to retrieve Code, Name, and Category of Expense account which have been debited.
