Question

Discuss with a set inter-related data tables, the basics of creating queries in MS Access?

Answer 1

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 capability 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 date 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 from each group has been ascertained 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 and Catld field respectively to retrieve Code, Name and Category of Expense account which have been debited.