Sandeep:
The first thing to note is that in the query paradigm all but two lines are
optional, those in brackets. The non-optional lines are the first and third,
which make up a simple SELECT query to return all rows from a table or
tables, the first line determining which columns are returned, e.g.
SELECT CustomerID, FirstName, LastName
FROM Customers;
I'll leave the second line aside for the present and return to it later.
The fourth line, the WHERE clause, restricts the result set to a subset of
rows from the source table on the basis of a criterion or criteria, e.g.
SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE City = "London";
The fifth line groups the query and is usually used when aggregating date,
e.g. to count the number of customers per city:
SELECT City, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY City;
The fifth line, the HAVING clause, restricts the result set, but unlike a
WHERE clause applies the restriction after the data is grouped and is
normally applied to the result of an aggregation operation, e.g. to identify
those cities having more than five customers:
SELECT City
FROM Customers
GROUP BY City
HAVING COUNT(*) > 5;
The final line simply orders the rows returned on one or more columns in
either ascending (the default) or descending order, e.g.
SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE City = "London"
ORDER BY LastName, FirstName;
Note that if you are using a query as the RecordSource of a report do not
use an ORDER BY clause. Instead use the internal sorting and grouping
mechanism in the report.
Returning to the second line this turns the query into what in Access is
called a 'make-table query', and creates a new table containing the rows
returned by the query. The new table will have no primary key column,
indexes etc defined, so if you do create a table in this way it will be
necessary to apply such features after it has been created. Its often better
to create an empty table first and use an 'append' query (using an INSERT
INTO statement) rather than a 'make table query' as this way the indexes and
other constraints can be set up fisrt to ensure the integrity of the data in
the new table.
Finally, note that in most of the examples I've given above I've included a
CustomerID column as well as the first and last customer names. Names can be
duplicated and to find a table with two customers of the same name is not out
of the question, so its essential to use a unique identifier for each, which
can be an autonumber column for convenience. You might think that by
combining names with a value in some other column might uniquely identify
people, but be wary of doing this. I was once attending a hospital
outpatients clinic when I overheard the staff saying that there were two
women also attending, both with the same names and date of birth. Their
medical records had become mixed up because it had been assumed that the
names and date of birth in combination would be a unique identifier of each
patient attending the clinic!
Ken Sheridan
Stafford, England
Sandeep said:
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
Hi
Can any one tell me how the query is executed step by step
thanks
Sandeep
