Queries allow you to search, view, alter and analyse data in many different ways. Queries can also be used as a method of producing a table of records from one or more related tables, which can then be turned into a report.
Queries are mostly used as a method of searching or selecting records, which match a certain criteria. When you process a query, it searches through a table or a related set of tables for records that match your criteria. Matching records are pulled out into a new table, often however only certain fields are pulled out from the tables that are being searched.
The results of a query can also be set so that they come out ordered on one of the fields. It may be that you want an alphabetically list of stock or your customers.
In the example shown, we use a query to show selected fields from a product list table. The criteria states that we only show non-discontinued stock and stock with less than 5 items in stock. This may be used to see what stock is running out and may need re-ordered soon.
A compound key is formed when you need to combine two or more fields in a table to uniquely identify individual records. Sometimes no one field can uniquely identify every record, but combining two gives a unique reference. For example in a stock system it is not impossible that two different suppliers may use the same product code for different products. Combining the product code with the supplier reference number could then be used to give a compound key. In a compound key, the fields, which make up the compound key, are usually also simple keys in there own right in other tables. If one of the fields that make up the key is not a simple key, the combination of fields is usually called a composite key rather than a compound key.
In database systems such as MS Access, fields can be set as Primary Keys. A primary key must be the simple key field of a table and MS Access will enforce this. In MS Access this then enforces the uniqueness of the Primary Key field and will not allow duplicates fields to be entered. As well as this the table is automatically indexed on the Primary Key in Access, which is needed to allow the system to quickly check whether an entry is unique or not, before validating it or blocking it. The table can also be ordered on the Primary Key field by using this index.