Queries
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. |
