SQL stands for Structured Query Language. It's a standard language for accessing and manipulating databases.
MySQL is a database management system, like SQL Server 2005, Oracle, Informix, Postgres etc. MySQL is a RDMS (Relational Database Management System). All types of RDMB use SQL.
SQL is used to manipulate database or to create a database. It's actually a common language. MySQL is an actual computer application. You must need to download or collect it and install it. MySQL is one of the most popular open source database management system. MySQL has an SQL interpreter.
MySQL can be used as the back engine database for several kinds of applications and it's one of the best choice for many web programmers for web-base application.
Tuesday, November 30, 2010
Saturday, November 27, 2010
VERY INTERESTING AND INFORMATIVE THINGS
1.
If you are right handed, you will tend to chew your food on your right side. If you are left handed, you will tend to chew your food on your left side
2.
If you stop getting thirsty, you need to drink more water. For when a human body is dehydrated, its thirst mechanism shuts off.
3.
Chewing gum while peeling onions will keep you from crying.
4.
Your tongue is germ free only if it is pink. If it is white there is a thin film of bacteria on it.
5.
The Mercedes-Benz motto is 'Das Beste oder Nichts' meaning 'the best or nothing'.
6.
The Titanic was the first ship to use the SOS signal.
7.
The pupil of the eye expands as much as 45 percent when a person looks at something pleasing.
8 .
The average person who stops smoking requires one hour less sleep a night.
9.
Laughing lowers levels of stress hormones and strengthens the immune system. Six-year-olds laugh an average of 300 times a day. Adults only laugh 15 to 100 times a day.
10.
The roar that we hear when we place a seashell next to our ear is not the ocean, but rather the sound of blood surging through the veins in the ear.
11.
Dalmatians are born without spots.
12.
Bats always turn left when exiting a cave.
13.
The 'v' in the name of a court case does not stand for 'versus', but for 'and' (in civil proceedings) or 'against' (in criminal proceedings)
14.
Men's shirts have the buttons on the right, but women's shirts have the buttons on the left
15.
The owl is the only bird to drop its upper eyelid to wink. All other birds raise their lower eyelids
16.
The reason honey is so easy to digest is that it's already been digested by a bee
17.
Roosters cannot crow if they cannot extend their necks
18.
The color blue has a calming effect. It causes the brain to release calming hormones
19.
Every time you sneeze some of your brain cells die
20.
Your left lung is smaller than your right lung to make room for your heart
21.
The verb "cleave" is the only English word with two synonyms which are antonyms of each other: adhere and separate
22.
When you blush, the lining of your stomach also turns red
23.
When hippos are upset, their sweat turns red
If you are right handed, you will tend to chew your food on your right side. If you are left handed, you will tend to chew your food on your left side
2.
If you stop getting thirsty, you need to drink more water. For when a human body is dehydrated, its thirst mechanism shuts off.
3.
Chewing gum while peeling onions will keep you from crying.
4.
Your tongue is germ free only if it is pink. If it is white there is a thin film of bacteria on it.
5.
The Mercedes-Benz motto is 'Das Beste oder Nichts' meaning 'the best or nothing'.
6.
The Titanic was the first ship to use the SOS signal.
7.
The pupil of the eye expands as much as 45 percent when a person looks at something pleasing.
8 .
The average person who stops smoking requires one hour less sleep a night.
9.
Laughing lowers levels of stress hormones and strengthens the immune system. Six-year-olds laugh an average of 300 times a day. Adults only laugh 15 to 100 times a day.
10.
The roar that we hear when we place a seashell next to our ear is not the ocean, but rather the sound of blood surging through the veins in the ear.
11.
Dalmatians are born without spots.
12.
Bats always turn left when exiting a cave.
13.
The 'v' in the name of a court case does not stand for 'versus', but for 'and' (in civil proceedings) or 'against' (in criminal proceedings)
14.
Men's shirts have the buttons on the right, but women's shirts have the buttons on the left
15.
The owl is the only bird to drop its upper eyelid to wink. All other birds raise their lower eyelids
16.
The reason honey is so easy to digest is that it's already been digested by a bee
17.
Roosters cannot crow if they cannot extend their necks
18.
The color blue has a calming effect. It causes the brain to release calming hormones
19.
Every time you sneeze some of your brain cells die
20.
Your left lung is smaller than your right lung to make room for your heart
21.
The verb "cleave" is the only English word with two synonyms which are antonyms of each other: adhere and separate
22.
When you blush, the lining of your stomach also turns red
23.
When hippos are upset, their sweat turns red
SQL Tutorials
Introduction
This is the first in a series of articles that explain what SQL is and how you can use it in your Microsoft® Access 2000 applications. There are three articles in all: a fundamental, an intermediate, and an advanced article. The articles are designed to progressively show the syntax and methods for using SQL, and to bring out those features of SQL that are new to Access 2000.
SQL Defined
To really gain the benefit and power of SQL, you must first come to a basic understanding of what it is and how you can use it.
What Is Structured Query Language?
SQL stands for Structured Query Language and is sometimes pronounced as "sequel." At its simplest, it is the language that is used to extract, manipulate, and structure data that resides in a relational database management system (RDBMS). In other words, to get an answer from your database, you must ask the question in SQL.
Why and Where Would You Use SQL?
You may not know it, but if you've been using Access, you've also been using SQL. "No!" you may say. "I've never used anything called SQL." That's because Access does such a great job of using it for you. The thing to remember is that for every data-oriented request you make, Access converts it to SQL under the covers.
SQL is used in a variety of places in Access. It is used of course for queries, but it is also used to build reports, populate list and combo boxes, and drive data-entry forms. Because SQL is so prevalent throughout Access, understanding it will greatly improve your ability to take control of all of the programmatic power that Access gives you.
Note
The particular dialect of SQL discussed in this article applies to version 4.0 of the Microsoft Jet database engine. Although many of the SQL statements will work in other databases, such as Microsoft SQL Server™, there are some differences in syntax. To identify the correct SQL syntax, consult the documentation for the database system you are using.
Data Definition Language
Data definition language (DDL) is the SQL language, or terminology, that is used to manage the database objects that contain data. Database objects are tables, indexes, or relationships—anything that has to do with the structure of the database—but not the data itself. Within SQL, certain keywords and clauses are used as the DDL commands for a relational database.
Data Manipulation Language
Data manipulation language (DML) is the SQL language, or terminology, that is used to manage the data within the database. DML has no effect on the structure of the database; it is only used against the actual data. DML is used to extract, add, modify, and delete information contained in the relational database tables.
ANSI and Access 2000
ANSI stands for the American National Standards Institute, which is a nationally recognized standards-setting organization that has defined a base standard for SQL. The most recently defined standard is SQL-92, and Access 2000 has added many new features to conform more closely to the standard, although some of the new features are available only when you are using the Jet OLE DB provider. However, Access has also maintained compliance with previous versions to allow for the greatest flexibility. Access also has some extra features not yet defined by the standard that extend the power of SQL.
To understand more about OLE DB and how it fits into the Microsoft Universal Data Access strategy, visit the Visual Basic Programmer's Guide.
SQL Coding Conventions
Throughout this article, you will notice a consistent method of SQL coding conventions. As with all coding conventions, the idea is to display the code in such a way as to make it easy to read and understand. This is accomplished by using a mix of white space, new lines, and uppercase keywords. In general, use uppercase for all SQL keywords, and if you must break the line of SQL code, try to do so with a major section of the SQL statement. You'll get a better feel for it after seeing a few examples.
Poorly formatted SQL code
Copy
CREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL,[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,Phone TEXT(10),Email TEXT(50))
Well-formatted SQL code
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER NOT NULL,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50))
Using Data Definition Language
When you are manipulating the structure of a database, there are three primary objects that you will work with: tables, indexes, and relationships.
• Tables are the database structure that contains the physical data, and they are organized by their columns (or fields) and rows (or records).
• Indexes are the database objects that define how the data in the tables is arranged and sorted in memory.
• Relationships define how one or more tables relate to one or more other tables.
All three of these database objects form the foundation for all relational databases.
Creating and Deleting Tables
Tables are the primary building blocks of a relational database. A table contains rows (or records) of data, and each row is organized into a finite number of columns (or fields). To build a new table in Access by using Jet SQL, you must name the table, name the fields, and define the type of data that the fields will contain. Use the CREATE TABLE statement to define the table in SQL. Let's suppose that we are building an invoicing database, so we will start with building the initial customers table.
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER,
[Last Name] TEXT(50),
[First Name] TEXT(50),
Phone TEXT(10),
Email TEXT(50))
Notes
• If a field name includes a space or some other nonalphanumeric character, you must enclose that field name within square brackets ([ ]).
• If you do not declare a length for text fields, they will default to 255 characters. For consistency and code readability, you should always define your field lengths.
• For more information about the types of data that can be used in field definitions, type SQL data types in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
You can declare a field to be NOT NULL, which means that null values cannot be inserted into that particular field; a value is always required. A null value should not be confused with an empty string or a value of 0, it is simply the database representation of an unknown value.
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER NOT NULL,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50))
To remove a table from the database, use the DROP TABLE statement.
Copy
DROP TABLE tblCustomers
Working with Indexes
An index is an external data structure used to sort or arrange pointers to data in a table. When you apply an index to a table, you are specifying a certain arrangement of the data so that it can be accessed more quickly. However, if you apply too many indexes to a table, you may slow down the performance because there is extra overhead involved in maintaining the index, and because an index can cause locking issues when used in a multiuser environment. Used in the correct context, an index can greatly improve the performance of an application.
To build an index on a table, you must name the index, name the table to build the index on, name the field or fields within the table to use, and name the options you want to use. You use the CREATE INDEX statement to build the index. For example, here's how you would build an index on the customers table in the invoicing database mentioned earlier.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID)
Indexed fields can be sorted in one of two ways: ascending (ASC) or descending (DESC). The default order is ascending, and it does not have to be declared. If you use ascending order, the data will be sorted from 1 to 100. If you specify descending order, the data will be sorted from 100 to 1. You should declare the sort order with each field in the index.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID DESC)
There are four main options that you can use with an index: PRIMARY, DISALLOW NULL, IGNORE NULL, and UNIQUE. The PRIMARY option designates the index as the primary key for the table. You can have only one primary key index per table, although the primary key index can be declared with more than one field. Use the WITH keyword to declare the index options.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID)
WITH PRIMARY
To create a primary key index on more than one field, include all of the field names in the field list.
Copy
CREATE INDEX idxCustomerName
ON tblCustomers ([Last Name], [First Name])
WITH PRIMARY
The DISALLOW NULL option prevents insertion of null data in the field. (This is similar to the NOT NULL declaration used in the CREATE TABLE statement.)
Copy
CREATE INDEX idxCustomerEmail
ON tblCustomers (Email)
WITH DISALLOW NULL
The IGNORE NULL option causes null data in the table to be ignored for the index. That means that any record that has a null value in the declared field will not be used (or counted) in the index.
Copy
CREATE INDEX idxCustomerLastName
ON tblCustomers ([Last Name])
WITH IGNORE NULL
In addition to the PRIMARY, DISALLOW NULL, and IGNORE NULL options, you can also declare the index as UNIQUE, which means that only unique, non-repeating values can be inserted in the indexed field.
Copy
CREATE UNIQUE INDEX idxCustomerPhone
ON tblCustomers (Phone)
To remove an index from a table, use the DROP INDEX statement.
Copy
DROP INDEX idxName
ON tblCustomers
Defining Relationships Between Tables
Relationships are the established associations between two or more tables. Relationships are based on common fields from more than one table, often involving primary and foreign keys.
A primary key is the field (or fields) that is used to uniquely identify each record in a table. There are three requirements for a primary key: It cannot be null, it must be unique, and there can be only one defined per table. You can define a primary key either by creating a primary key index after the table is created, or by using the CONSTRAINT clause in the table declaration, as shown in the examples later in this section. A constraint limits (or constrains) the values that are entered in a field. For more information about constraints, see the article "Intermediate Microsoft Jet SQL for Access 2000."
A foreign key is a field (or fields) in one table that references the primary key in another table. The data in the fields from both tables is exactly the same, and the table with the primary key record (the primary table) must have existing records before the table with the foreign key record (the foreign table) has the matching or related records. Like primary keys, you can define foreign keys in the table declaration by using the CONSTRAINT clause.
There are essentially three types of relationships:
• One-to-oneFor every record in the primary table, there is one and only one record in the foreign table.
• One-to-manyFor every record in the primary table, there are one or more related records in the foreign table.
• Many-to-manyFor every record in the primary table, there are many related records in the foreign table, and for every record in the foreign table, there are many related records in the primary table.
For example, let's add an invoices table to our invoicing database. Every customer in our customers table can have many invoices in our invoices table—this is a classic one-to-many scenario. We will take the primary key from the customers table and define it as the foreign key in our invoices table, thereby establishing the proper relationship between the tables.
When defining the relationships between tables, you must make the CONSTRAINT declarations at the field level. This means that the constraints are defined within a CREATE TABLE statement. To apply the constraints, use the CONSTRAINT keyword after a field declaration, name the constraint, name the table that it references, and name the field or fields within that table that will make up the matching foreign key.
The following statement assumes that the tblCustomers table has already been built, and that it has a primary key defined on the CustomerID field. The statement now builds the tblInvoices table, defining its primary key on the InvoiceID field. It also builds the one-to-many relationship between the tblCustomers and tblInvoices tables by defining another CustomerID field in the tblInvoices table. This field is defined as a foreign key that references the CustomerID field in the Customers table. Note that the name of each constraint follows the CONSTRAINT keyword.
Copy
CREATE TABLE tblInvoices
(InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY,
CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID
REFERENCES tblCustomers (CustomerID),
InvoiceDate DATETIME,
Amount CURRENCY)
Note that the primary key index (PK_InvoiceID) for the invoices table is declared within the CREATE TABLE statement. To enhance the performance of the primary key, an index is automatically created for it, so there's no need to use a separate CREATE INDEX statement.
Now let's create a shipping table that will contain each customer's shipping address. Let's assume that there will be only one shipping record for each customer record, so we will be establishing a one-to-one relationship.
Copy
CREATE TABLE tblShipping
(CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY
REFERENCES tblCustomers (CustomerID),
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))
Note that the CustomerID field is both the primary key for the shipping table and the foreign key reference to the customers table.
NoteWhen you are creating a one-to-one relationship by using DDL statements, the Access user interface may display the relationship as a one-to-many relationship. To correct this problem, after the one-to-one relationship has been created, open the Relationships window by clicking Relationships on the Tools menu. Make sure that the affected tables have been added to the Relationships window, and then double-click the link between the tables to open the Edit Relationships dialog box. Click the Join Type button to open the Join Properties dialog box. You don't have to select an option, just click OK to close the dialog box, and then click OK to close the Edit Relationships dialog box. The one-to-one relationship should now be displayed correctly.
For more information about relationships and how they work, type relationships in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using Data Manipulation Language
DML is all about working with the data that is stored in the database tables. Not only is DML used for retrieving the data, it is also used for creating, modifying, and deleting it.
Retrieving Records
The most basic and most often used SQL statement is the SELECT statement. SELECT statements are the workhorses of all SQL statements, and they are commonly referred to as select queries. You use the SELECT statement to retrieve data from the database tables, and the results are usually returned in a set of records (or rows) made up of any number of fields (or columns). You must designate which table or tables to select from with the FROM clause. The basic structure of a SELECT statement is:
Copy
SELECT field list
FROM table list
To select all the fields from a table, use an asterisk (*). For example, the following statement selects all the fields and all the records from the customers table:
Copy
SELECT *
FROM tblCustomers
To limit the fields retrieved by the query, simply use the field names instead. For example:
Copy
SELECT [Last Name], Phone
FROM tblCustomers
To designate a different name for a field in the result set, use the AS keyword to establish an alias for that field.
Copy
SELECT CustomerID AS [Customer Number]
FROM tblCustomers
Restricting the Result Set
More often than not, you will not want to retrieve all records from a table. You will want only a subset of those records based on some qualifying criteria. To qualify a SELECT statement, you must use a WHERE clause, which will allow you to specify exactly which records you want to retrieve.
Copy
SELECT *
FROM tblInvoices
WHERE CustomerID = 1
Note the CustomerID = 1 portion of the WHERE clause. A WHERE clause can contain up to 40 such expressions, and they can be joined with the And or Or logical operators. Using more than one expression allows you to further filter out records in the result set.
Copy
SELECT *
FROM tblInvoices
WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#
Note that the date string is enclosed in number signs (#). If you are using a regular string in an expression, you must enclose the string in single quotation marks ('). For example:
Copy
SELECT *
FROM tblCustomers
WHERE [Last Name] = 'White'
If you do not know the whole string value, you can use wildcard characters with the Like operator.
Copy
SELECT *
FROM tblCustomers
WHERE [Last Name] LIKE 'W*'
There are a number of wildcard characters to choose from, and the following table details what they are and what they can be used for.
Wildcard character Description
* or % Zero or more characters
? or _ (underscore) Any single character
# Any single digit (0-9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist
NoteThe % and _ (underscore) wildcard characters should be used only through the Jet OLE DB provider and ActiveX® Data Objects (ADO) code. They will be treated as literal characters if they are used though the Access SQL View user interface or Data Access Objects (DAO) code.
For more information about using the Like operator with wildcard characters, type wildcard characters in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Sorting the Result Set
To specify a particular sort order on one or more fields in the result set, use the optional ORDER BY clause. As explained earlier in the "Working with Indexes" section, records can be sorted in either ascending (ASC) or descending (DESC) order; ascending is the default.
Fields referenced in the ORDER BY clause do not have to be part of the SELECT statement's field list, and sorting can be applied to string, numeric, and date/time values. Always place the ORDER BY clause at the end of the SELECT statement.
Copy
SELECT *
FROM tblCustomers
ORDER BY [Last Name], [First Name] DESC
You can also use the field numbers (or positions) instead of field names in the ORDER BY clause.
Copy
SELECT *
FROM tblCustomers
ORDER BY 2, 3 DESC
For more information about using the ORDER BY clause, type ORDER BY clause in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using Aggregate Functions to Work with Values
Aggregate functions are used to calculate statistical and summary information from data in tables. These functions are used in SELECT statements, and all of them take fields or expressions as arguments.
To count the number of records in a result set, use the Count function. Using an asterisk with the Count function causes Null values to be counted as well.
Copy
SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices
To count only non-Null values, use the Count function with a field name:
Copy
SELECT Count(Amount) AS
[Number of Valid Invoice Amounts]
FROM tblInvoices
To find the average value for a column or expression of numeric data, use the Avg function:
Copy
SELECT Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
To find the total of the values in a column or expression of numeric data, use the Sum function:
Copy
SELECT Sum(Amount) AS [Total Invoice Amount]
FROM tblInvoices
To find the minimum value for a column or expression, use the Min function:
Copy
SELECT Min(Amount) AS [Minimum Invoice Amount]
FROM tblInvoices
To find the maximum value for a column or expression, use the Max function:
Copy
SELECT Max(Amount) AS [Maximum Invoice Amount]
FROM tblInvoices
To find the first value in a column or expression, use the First function:
Copy
SELECT First(Amount) AS [First Invoice Amount]
FROM tblInvoices
To find the last value in a column or expression, use the Last function:
Copy
SELECT Last(Amount) AS [Last Invoice Amount]
FROM tblInvoices
For more information about using the aggregate functions, type SQL aggregate functions in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Grouping Records in a Result Set
Sometimes there are records in a table that are logically related, as in the case of the invoices table. Since one customer can have many invoices, it could be useful to treat all the invoices for one customer as a group, in order to find statistical and summary information about the group.
The key to grouping records is that one or more fields in each record must contain the same value for every record in the group. In the case of the invoices table, the CustomerID field value is the same for every invoice a particular customer has.
To create a group of records, use the GROUP BY clause with the name of the field or fields you want to group with.
Copy
SELECT CustomerID, Count(*) AS [Number of Invoices],
Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
GROUP BY CustomerID
Note that the statement will return one record that shows the customer ID, the number of invoices the customer has, and the average invoice amount, for every customer who has an invoice record in the invoices table. Because each customer's invoices are treated as a group, we are able to count the number of invoices, and then determine the average invoice amount.
You can specify a condition at the group level by using the HAVING clause, which is similar to the WHERE clause. For example, the following query returns only those records for each customer whose average invoice amount is less than 100:
Copy
SELECT CustomerID, Count(*) AS [Number of Invoices],
Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
GROUP BY CustomerID
HAVING Avg(Amount) < 100
For more information about using the GROUP BY clause, type GROUP BY clause in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Inserting Records into a Table
There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases, you use the SQL statement INSERT INTO to accomplish the task. INSERT INTO statements are commonly referred to as append queries.
To add one record to a table, you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. To define the value list, use the VALUES clause. For example, the following statement will insert the values "1", "Kelly", and "Jill" into the CustomerID, Last Name, and First Name fields, respectively.
Copy
INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
VALUES (1, 'Kelly', 'Jill')
You can omit the field list, but only if you supply all the values that record can contain.
Copy
INSERT INTO tblCustomers
VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')
To add many records to a table at one time, use the INSERT INTO statement along with a SELECT statement. When you are inserting records from another table, each value being inserted must be compatible with the type of field that will be receiving the data. For more information about data types and their usage, see "Intermediate Microsoft Jet SQL for Access 2000."
The following INSERT INTO statement inserts all the values in the CustomerID, Last Name, and First Name fields from the tblOldCustomers table into the corresponding fields in the tblCustomers table.
Copy
INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
SELECT CustomerID, [Last Name], [First Name]
FROM tblOldCustomers
If the tables are defined exactly alike, you leave can out the field lists.
Copy
INSERT INTO tblCustomers
SELECT * FROM tblOldCustomers
For more information about using the INSERT INTO statement, type INSERT INTO statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Updating Records in a Table
To modify the data that is currently in a table, you use the UPDATE statement, which is commonly referred to as an update query. The UPDATE statement can modify one or more records and generally takes this form:
Copy
UPDATE table name
SET field name = some value
To update all the records in a table, specify the table name, and then use the SET clause to specify the field or fields to be changed.
Copy
UPDATE tblCustomers
SET Phone = 'None'
In most cases, you will want to qualify the UPDATE statement with a WHERE clause to limit the number of records changed.
Copy
UPDATE tblCustomers
SET Email = 'None'
WHERE [Last Name] = 'Smith'
For more information about using the UPDATE statement, type UPDATE statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Deleting Records from a Table
To delete the data that is currently in a table, you use the DELETE statement, which is commonly referred to as a delete query, also known as truncating a table. The DELETE statement can remove one or more records from a table and generally takes this form:
Copy
DELETE FROM table list
The DELETE statement does not remove the table structure, only the data that is currently being held by the table structure. To remove all the records from a table, use the DELETE statement and specify which table or tables you want to delete all the records from.
Copy
DELETE FROM tblInvoices
In most cases, you will want to qualify the DELETE statement with a WHERE clause to limit the number of records to be removed.
Copy
DELETE FROM tblInvoices
WHERE InvoiceID = 3
If you want to remove data only from certain fields in a table, use the UPDATE statement and set those fields equal to NULL, but only if they are nullable fields. For more information about nullable fields, see "Intermediate Microsoft Jet SQL for Access 2000."
Copy
UPDATE tblCustomers
SET Email = Null
For more information about using the DELETE statement, type DELETE statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using SQL in Access
Now that we've had a basic overview of the SQL syntax, let's look at some of the ways we can use it in an Access application. To do this, we'll use the sample database included with this article. Through queries and sample code, the acFundSQL.mdb sample demonstrates the different SQL statements discussed in this article.
NoteMany of the sample queries used in acFundSQL.mdb depend on certain tables existing and containing data. Because some of the queries in acFundSQL.mdb alter the data or the database structure, you may eventually have difficulty running other queries due to missing or altered data, tables, or indexes. If this problem occurs, open the frmResetTables form and click the Reset Tables button to re-create the tables and their original default data. To manually step through the reset-table process, execute the following queries in the order they are listed:
Building Queries
Queries are SQL statements that are saved in an Access database and can be used at any time, either directly from the Access user interface or from the Visual Basic® for Applications (VBA) programming language. You can build queries by using query Design view, which greatly simplifies the building of SQL statements, or you can build queries by entering SQL statements directly in the SQL view window.
As mentioned at the beginning of this article, Access converts all data-oriented tasks in the database into SQL statements. To demonstrate this behavior, let's build a query in query Design view.
1. Open the acFundSQL.mdb database.
1. Make sure that the tblCustomers table has been created and that it contains some data.
2. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
3. In the New Query dialog box, click Design View, and then click OK.
4. In the Show Table dialog box, click tblCustomers, click Add, and then click Close.
5. In the tblCustomers field list, click the asterisk (*) and drag it to the first field in the query design grid.
6. On the View menu, click SQL View. This opens the SQL view window and displays the SQL syntax that Access is using for this query.
NoteThis query is similar to the Select All Customers query already saved in the acFundSQL database.
Specifying a Data Source
To make a connection to data in the database's tables, Access objects use data source properties. For example, a form has a RecordSource property that connects it to a particular table in the database. Anywhere that a data source is specified, you can use an SQL statement (or a saved query) instead of the name of a table. For example, let's build a new form that connects to the customers table by using an SQL SELECT statement as the data source.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. In the Database window, click Forms under Objects, and then click New on the Database window toolbar
3. In the New Form dialog box, click Design View, and then click OK. A blank form is now open in Design view.
4. On the View menu, click Properties to open the form's property sheet.
5. In the RecordSource property text box, type the following SQL statement:
Copy
SELECT * FROM tblCustomers
6. Press the ENTER key on your keyboard. The field list appears, and it lists all of the available fields from the tblCustomers table.
7. Select all of the fields by holding down the SHIFT key and clicking the first and then the last field listed.
8. Drag the selected fields to the center of the Detail section on the blank form and then release the mouse button.
9. Close the property sheet.
10. On the View menu, click Form View, and then use the record selectors at the bottom of the form to scroll through all the records in the tblCustomers table.
Another great place to use an SQL statement is in the RowSource property for a list or combo box. Let's build a simple form with a combo box that uses an SQL SELECT statement as its row source.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. Create a new form and open it in Design view.
3. On the View menu, click Toolbox.
4. Make sure that the Control Wizards (upper rightmost) button in the toolbox is not pressed in.
5. Click the Combo Box button and then click in the center of the blank form's Detail section.
6. Make sure that the combo box in the form is selected, and then click Properties on the View menu.
7. In the RowSource property text box, type the following SQL statement:
Copy
SELECT [Last Name] FROM tblCustomers
8. Press ENTER, and then close the property sheet.
9. On the View menu, click Form View. In the form, click the down arrow next to the combo box. Note that all the last names from the customers table are listed in the combo box.
Using SQL Statements Inline
The process of using SQL statements within VBA code is referred to as using the statements "inline." Although a deep discussion of how to use VBA is outside the scope of this article, it is a straightforward task to execute SQL statements in VBA code.
Suppose we need to run an UPDATE statement from code, and we want to run the code when a user clicks a button on a form.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. Create a new form and open it in Design view.
3. On the View menu, click Toolbox.
4. Make sure that the Control Wizards (upper rightmost) button in the toolbox is not pressed in.
5. Click the Command Button button and then click in the center of the blank form's Detail section.
6. Make sure that the command button in the form is selected, and then click Properties on the View menu.
7. Click in the following property text boxes and enter the values given:
Name: cmdUpdatePhones
Caption: Update Phones
8. Click the OnClick property text box, click the Build button (…), and then click Code Builder to open the Visual Basic Editor.
9. Type or paste the following lines of code in the cmdUpdatePhones_Click subprocedure:
Copy
Dim conDatabase As ADODB.Connection
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "UPDATE tblCustomers SET Phone = 'None'"
conDatabase.Execute strSQL
MsgBox "All phones have been set to ""None""."
conDatabase.Close
Set conDatabase = Nothing
10. Close the Visual Basic Editor, close the property sheet, and then click Form View on the View menu.
11. Click the Update Phones button. You should see a message box that says all the phone numbers have been set to "None." You can verify this by opening the tblCustomers table.
Although using SQL statements inline is great for action queries (that is, append, delete, and update), they are most often used in select queries to build sets of records. Let's suppose that we want to loop through a results-based set to accomplish what the UPDATE statement did. Following a similar procedure for the UPDATE example, use the following code in the cmdSelectPhones_Click subprocedure:
Copy
Dim conDatabase As ADODB.Connection
Dim rstCustomers As ADODB.Recordset
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "SELECT Phone FROM tblCustomers"
Set rstCustomers = New Recordset
rstCustomers.Open strSQL, conDatabase, _
adOpenDynamic, adLockOptimistic
With rstCustomers
Do While Not .EOF
!Phone = "None"
.Update
.MoveNext
Loop
End With
MsgBox "All phones have been set to ""None""."
rstCustomers.Close
conDatabase.Close
Set rstCustomers = Nothing
Set conDatabase = Nothing
In most cases, you will achieve better performance by using the UPDATE statement because it acts on the table as a whole, treating it as a single set of records. However, there may be some situations where you simply must loop through a set of records in order to achieve the results you need.
One Last Comment
Although it may be difficult to believe, this article has only scratched the surface of the SQL language as it applies to Access. By now you should have a good basic understanding of SQL and how you can use it in your Access 2000 applications. Try out your new skills by using SQL in any RecordSource or RowSource property you can find, and use the resources listed in the next section to further your knowledge of SQL and Access.
This is the first in a series of articles that explain what SQL is and how you can use it in your Microsoft® Access 2000 applications. There are three articles in all: a fundamental, an intermediate, and an advanced article. The articles are designed to progressively show the syntax and methods for using SQL, and to bring out those features of SQL that are new to Access 2000.
SQL Defined
To really gain the benefit and power of SQL, you must first come to a basic understanding of what it is and how you can use it.
What Is Structured Query Language?
SQL stands for Structured Query Language and is sometimes pronounced as "sequel." At its simplest, it is the language that is used to extract, manipulate, and structure data that resides in a relational database management system (RDBMS). In other words, to get an answer from your database, you must ask the question in SQL.
Why and Where Would You Use SQL?
You may not know it, but if you've been using Access, you've also been using SQL. "No!" you may say. "I've never used anything called SQL." That's because Access does such a great job of using it for you. The thing to remember is that for every data-oriented request you make, Access converts it to SQL under the covers.
SQL is used in a variety of places in Access. It is used of course for queries, but it is also used to build reports, populate list and combo boxes, and drive data-entry forms. Because SQL is so prevalent throughout Access, understanding it will greatly improve your ability to take control of all of the programmatic power that Access gives you.
Note
The particular dialect of SQL discussed in this article applies to version 4.0 of the Microsoft Jet database engine. Although many of the SQL statements will work in other databases, such as Microsoft SQL Server™, there are some differences in syntax. To identify the correct SQL syntax, consult the documentation for the database system you are using.
Data Definition Language
Data definition language (DDL) is the SQL language, or terminology, that is used to manage the database objects that contain data. Database objects are tables, indexes, or relationships—anything that has to do with the structure of the database—but not the data itself. Within SQL, certain keywords and clauses are used as the DDL commands for a relational database.
Data Manipulation Language
Data manipulation language (DML) is the SQL language, or terminology, that is used to manage the data within the database. DML has no effect on the structure of the database; it is only used against the actual data. DML is used to extract, add, modify, and delete information contained in the relational database tables.
ANSI and Access 2000
ANSI stands for the American National Standards Institute, which is a nationally recognized standards-setting organization that has defined a base standard for SQL. The most recently defined standard is SQL-92, and Access 2000 has added many new features to conform more closely to the standard, although some of the new features are available only when you are using the Jet OLE DB provider. However, Access has also maintained compliance with previous versions to allow for the greatest flexibility. Access also has some extra features not yet defined by the standard that extend the power of SQL.
To understand more about OLE DB and how it fits into the Microsoft Universal Data Access strategy, visit the Visual Basic Programmer's Guide.
SQL Coding Conventions
Throughout this article, you will notice a consistent method of SQL coding conventions. As with all coding conventions, the idea is to display the code in such a way as to make it easy to read and understand. This is accomplished by using a mix of white space, new lines, and uppercase keywords. In general, use uppercase for all SQL keywords, and if you must break the line of SQL code, try to do so with a major section of the SQL statement. You'll get a better feel for it after seeing a few examples.
Poorly formatted SQL code
Copy
CREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL,[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,Phone TEXT(10),Email TEXT(50))
Well-formatted SQL code
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER NOT NULL,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50))
Using Data Definition Language
When you are manipulating the structure of a database, there are three primary objects that you will work with: tables, indexes, and relationships.
• Tables are the database structure that contains the physical data, and they are organized by their columns (or fields) and rows (or records).
• Indexes are the database objects that define how the data in the tables is arranged and sorted in memory.
• Relationships define how one or more tables relate to one or more other tables.
All three of these database objects form the foundation for all relational databases.
Creating and Deleting Tables
Tables are the primary building blocks of a relational database. A table contains rows (or records) of data, and each row is organized into a finite number of columns (or fields). To build a new table in Access by using Jet SQL, you must name the table, name the fields, and define the type of data that the fields will contain. Use the CREATE TABLE statement to define the table in SQL. Let's suppose that we are building an invoicing database, so we will start with building the initial customers table.
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER,
[Last Name] TEXT(50),
[First Name] TEXT(50),
Phone TEXT(10),
Email TEXT(50))
Notes
• If a field name includes a space or some other nonalphanumeric character, you must enclose that field name within square brackets ([ ]).
• If you do not declare a length for text fields, they will default to 255 characters. For consistency and code readability, you should always define your field lengths.
• For more information about the types of data that can be used in field definitions, type SQL data types in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
You can declare a field to be NOT NULL, which means that null values cannot be inserted into that particular field; a value is always required. A null value should not be confused with an empty string or a value of 0, it is simply the database representation of an unknown value.
Copy
CREATE TABLE tblCustomers
(CustomerID INTEGER NOT NULL,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50))
To remove a table from the database, use the DROP TABLE statement.
Copy
DROP TABLE tblCustomers
Working with Indexes
An index is an external data structure used to sort or arrange pointers to data in a table. When you apply an index to a table, you are specifying a certain arrangement of the data so that it can be accessed more quickly. However, if you apply too many indexes to a table, you may slow down the performance because there is extra overhead involved in maintaining the index, and because an index can cause locking issues when used in a multiuser environment. Used in the correct context, an index can greatly improve the performance of an application.
To build an index on a table, you must name the index, name the table to build the index on, name the field or fields within the table to use, and name the options you want to use. You use the CREATE INDEX statement to build the index. For example, here's how you would build an index on the customers table in the invoicing database mentioned earlier.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID)
Indexed fields can be sorted in one of two ways: ascending (ASC) or descending (DESC). The default order is ascending, and it does not have to be declared. If you use ascending order, the data will be sorted from 1 to 100. If you specify descending order, the data will be sorted from 100 to 1. You should declare the sort order with each field in the index.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID DESC)
There are four main options that you can use with an index: PRIMARY, DISALLOW NULL, IGNORE NULL, and UNIQUE. The PRIMARY option designates the index as the primary key for the table. You can have only one primary key index per table, although the primary key index can be declared with more than one field. Use the WITH keyword to declare the index options.
Copy
CREATE INDEX idxCustomerID
ON tblCustomers (CustomerID)
WITH PRIMARY
To create a primary key index on more than one field, include all of the field names in the field list.
Copy
CREATE INDEX idxCustomerName
ON tblCustomers ([Last Name], [First Name])
WITH PRIMARY
The DISALLOW NULL option prevents insertion of null data in the field. (This is similar to the NOT NULL declaration used in the CREATE TABLE statement.)
Copy
CREATE INDEX idxCustomerEmail
ON tblCustomers (Email)
WITH DISALLOW NULL
The IGNORE NULL option causes null data in the table to be ignored for the index. That means that any record that has a null value in the declared field will not be used (or counted) in the index.
Copy
CREATE INDEX idxCustomerLastName
ON tblCustomers ([Last Name])
WITH IGNORE NULL
In addition to the PRIMARY, DISALLOW NULL, and IGNORE NULL options, you can also declare the index as UNIQUE, which means that only unique, non-repeating values can be inserted in the indexed field.
Copy
CREATE UNIQUE INDEX idxCustomerPhone
ON tblCustomers (Phone)
To remove an index from a table, use the DROP INDEX statement.
Copy
DROP INDEX idxName
ON tblCustomers
Defining Relationships Between Tables
Relationships are the established associations between two or more tables. Relationships are based on common fields from more than one table, often involving primary and foreign keys.
A primary key is the field (or fields) that is used to uniquely identify each record in a table. There are three requirements for a primary key: It cannot be null, it must be unique, and there can be only one defined per table. You can define a primary key either by creating a primary key index after the table is created, or by using the CONSTRAINT clause in the table declaration, as shown in the examples later in this section. A constraint limits (or constrains) the values that are entered in a field. For more information about constraints, see the article "Intermediate Microsoft Jet SQL for Access 2000."
A foreign key is a field (or fields) in one table that references the primary key in another table. The data in the fields from both tables is exactly the same, and the table with the primary key record (the primary table) must have existing records before the table with the foreign key record (the foreign table) has the matching or related records. Like primary keys, you can define foreign keys in the table declaration by using the CONSTRAINT clause.
There are essentially three types of relationships:
• One-to-oneFor every record in the primary table, there is one and only one record in the foreign table.
• One-to-manyFor every record in the primary table, there are one or more related records in the foreign table.
• Many-to-manyFor every record in the primary table, there are many related records in the foreign table, and for every record in the foreign table, there are many related records in the primary table.
For example, let's add an invoices table to our invoicing database. Every customer in our customers table can have many invoices in our invoices table—this is a classic one-to-many scenario. We will take the primary key from the customers table and define it as the foreign key in our invoices table, thereby establishing the proper relationship between the tables.
When defining the relationships between tables, you must make the CONSTRAINT declarations at the field level. This means that the constraints are defined within a CREATE TABLE statement. To apply the constraints, use the CONSTRAINT keyword after a field declaration, name the constraint, name the table that it references, and name the field or fields within that table that will make up the matching foreign key.
The following statement assumes that the tblCustomers table has already been built, and that it has a primary key defined on the CustomerID field. The statement now builds the tblInvoices table, defining its primary key on the InvoiceID field. It also builds the one-to-many relationship between the tblCustomers and tblInvoices tables by defining another CustomerID field in the tblInvoices table. This field is defined as a foreign key that references the CustomerID field in the Customers table. Note that the name of each constraint follows the CONSTRAINT keyword.
Copy
CREATE TABLE tblInvoices
(InvoiceID INTEGER CONSTRAINT PK_InvoiceID PRIMARY KEY,
CustomerID INTEGER NOT NULL CONSTRAINT FK_CustomerID
REFERENCES tblCustomers (CustomerID),
InvoiceDate DATETIME,
Amount CURRENCY)
Note that the primary key index (PK_InvoiceID) for the invoices table is declared within the CREATE TABLE statement. To enhance the performance of the primary key, an index is automatically created for it, so there's no need to use a separate CREATE INDEX statement.
Now let's create a shipping table that will contain each customer's shipping address. Let's assume that there will be only one shipping record for each customer record, so we will be establishing a one-to-one relationship.
Copy
CREATE TABLE tblShipping
(CustomerID INTEGER CONSTRAINT PK_CustomerID PRIMARY KEY
REFERENCES tblCustomers (CustomerID),
Address TEXT(50),
City TEXT(50),
State TEXT(2),
Zip TEXT(10))
Note that the CustomerID field is both the primary key for the shipping table and the foreign key reference to the customers table.
NoteWhen you are creating a one-to-one relationship by using DDL statements, the Access user interface may display the relationship as a one-to-many relationship. To correct this problem, after the one-to-one relationship has been created, open the Relationships window by clicking Relationships on the Tools menu. Make sure that the affected tables have been added to the Relationships window, and then double-click the link between the tables to open the Edit Relationships dialog box. Click the Join Type button to open the Join Properties dialog box. You don't have to select an option, just click OK to close the dialog box, and then click OK to close the Edit Relationships dialog box. The one-to-one relationship should now be displayed correctly.
For more information about relationships and how they work, type relationships in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using Data Manipulation Language
DML is all about working with the data that is stored in the database tables. Not only is DML used for retrieving the data, it is also used for creating, modifying, and deleting it.
Retrieving Records
The most basic and most often used SQL statement is the SELECT statement. SELECT statements are the workhorses of all SQL statements, and they are commonly referred to as select queries. You use the SELECT statement to retrieve data from the database tables, and the results are usually returned in a set of records (or rows) made up of any number of fields (or columns). You must designate which table or tables to select from with the FROM clause. The basic structure of a SELECT statement is:
Copy
SELECT field list
FROM table list
To select all the fields from a table, use an asterisk (*). For example, the following statement selects all the fields and all the records from the customers table:
Copy
SELECT *
FROM tblCustomers
To limit the fields retrieved by the query, simply use the field names instead. For example:
Copy
SELECT [Last Name], Phone
FROM tblCustomers
To designate a different name for a field in the result set, use the AS keyword to establish an alias for that field.
Copy
SELECT CustomerID AS [Customer Number]
FROM tblCustomers
Restricting the Result Set
More often than not, you will not want to retrieve all records from a table. You will want only a subset of those records based on some qualifying criteria. To qualify a SELECT statement, you must use a WHERE clause, which will allow you to specify exactly which records you want to retrieve.
Copy
SELECT *
FROM tblInvoices
WHERE CustomerID = 1
Note the CustomerID = 1 portion of the WHERE clause. A WHERE clause can contain up to 40 such expressions, and they can be joined with the And or Or logical operators. Using more than one expression allows you to further filter out records in the result set.
Copy
SELECT *
FROM tblInvoices
WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#
Note that the date string is enclosed in number signs (#). If you are using a regular string in an expression, you must enclose the string in single quotation marks ('). For example:
Copy
SELECT *
FROM tblCustomers
WHERE [Last Name] = 'White'
If you do not know the whole string value, you can use wildcard characters with the Like operator.
Copy
SELECT *
FROM tblCustomers
WHERE [Last Name] LIKE 'W*'
There are a number of wildcard characters to choose from, and the following table details what they are and what they can be used for.
Wildcard character Description
* or % Zero or more characters
? or _ (underscore) Any single character
# Any single digit (0-9)
[charlist] Any single character in charlist
[!charlist] Any single character not in charlist
NoteThe % and _ (underscore) wildcard characters should be used only through the Jet OLE DB provider and ActiveX® Data Objects (ADO) code. They will be treated as literal characters if they are used though the Access SQL View user interface or Data Access Objects (DAO) code.
For more information about using the Like operator with wildcard characters, type wildcard characters in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Sorting the Result Set
To specify a particular sort order on one or more fields in the result set, use the optional ORDER BY clause. As explained earlier in the "Working with Indexes" section, records can be sorted in either ascending (ASC) or descending (DESC) order; ascending is the default.
Fields referenced in the ORDER BY clause do not have to be part of the SELECT statement's field list, and sorting can be applied to string, numeric, and date/time values. Always place the ORDER BY clause at the end of the SELECT statement.
Copy
SELECT *
FROM tblCustomers
ORDER BY [Last Name], [First Name] DESC
You can also use the field numbers (or positions) instead of field names in the ORDER BY clause.
Copy
SELECT *
FROM tblCustomers
ORDER BY 2, 3 DESC
For more information about using the ORDER BY clause, type ORDER BY clause in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using Aggregate Functions to Work with Values
Aggregate functions are used to calculate statistical and summary information from data in tables. These functions are used in SELECT statements, and all of them take fields or expressions as arguments.
To count the number of records in a result set, use the Count function. Using an asterisk with the Count function causes Null values to be counted as well.
Copy
SELECT Count(*) AS [Number of Invoices]
FROM tblInvoices
To count only non-Null values, use the Count function with a field name:
Copy
SELECT Count(Amount) AS
[Number of Valid Invoice Amounts]
FROM tblInvoices
To find the average value for a column or expression of numeric data, use the Avg function:
Copy
SELECT Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
To find the total of the values in a column or expression of numeric data, use the Sum function:
Copy
SELECT Sum(Amount) AS [Total Invoice Amount]
FROM tblInvoices
To find the minimum value for a column or expression, use the Min function:
Copy
SELECT Min(Amount) AS [Minimum Invoice Amount]
FROM tblInvoices
To find the maximum value for a column or expression, use the Max function:
Copy
SELECT Max(Amount) AS [Maximum Invoice Amount]
FROM tblInvoices
To find the first value in a column or expression, use the First function:
Copy
SELECT First(Amount) AS [First Invoice Amount]
FROM tblInvoices
To find the last value in a column or expression, use the Last function:
Copy
SELECT Last(Amount) AS [Last Invoice Amount]
FROM tblInvoices
For more information about using the aggregate functions, type SQL aggregate functions in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Grouping Records in a Result Set
Sometimes there are records in a table that are logically related, as in the case of the invoices table. Since one customer can have many invoices, it could be useful to treat all the invoices for one customer as a group, in order to find statistical and summary information about the group.
The key to grouping records is that one or more fields in each record must contain the same value for every record in the group. In the case of the invoices table, the CustomerID field value is the same for every invoice a particular customer has.
To create a group of records, use the GROUP BY clause with the name of the field or fields you want to group with.
Copy
SELECT CustomerID, Count(*) AS [Number of Invoices],
Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
GROUP BY CustomerID
Note that the statement will return one record that shows the customer ID, the number of invoices the customer has, and the average invoice amount, for every customer who has an invoice record in the invoices table. Because each customer's invoices are treated as a group, we are able to count the number of invoices, and then determine the average invoice amount.
You can specify a condition at the group level by using the HAVING clause, which is similar to the WHERE clause. For example, the following query returns only those records for each customer whose average invoice amount is less than 100:
Copy
SELECT CustomerID, Count(*) AS [Number of Invoices],
Avg(Amount) AS [Average Invoice Amount]
FROM tblInvoices
GROUP BY CustomerID
HAVING Avg(Amount) < 100
For more information about using the GROUP BY clause, type GROUP BY clause in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Inserting Records into a Table
There are essentially two methods for adding records to a table. The first is to add one record at a time; the second is to add many records at a time. In both cases, you use the SQL statement INSERT INTO to accomplish the task. INSERT INTO statements are commonly referred to as append queries.
To add one record to a table, you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. To define the value list, use the VALUES clause. For example, the following statement will insert the values "1", "Kelly", and "Jill" into the CustomerID, Last Name, and First Name fields, respectively.
Copy
INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
VALUES (1, 'Kelly', 'Jill')
You can omit the field list, but only if you supply all the values that record can contain.
Copy
INSERT INTO tblCustomers
VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')
To add many records to a table at one time, use the INSERT INTO statement along with a SELECT statement. When you are inserting records from another table, each value being inserted must be compatible with the type of field that will be receiving the data. For more information about data types and their usage, see "Intermediate Microsoft Jet SQL for Access 2000."
The following INSERT INTO statement inserts all the values in the CustomerID, Last Name, and First Name fields from the tblOldCustomers table into the corresponding fields in the tblCustomers table.
Copy
INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
SELECT CustomerID, [Last Name], [First Name]
FROM tblOldCustomers
If the tables are defined exactly alike, you leave can out the field lists.
Copy
INSERT INTO tblCustomers
SELECT * FROM tblOldCustomers
For more information about using the INSERT INTO statement, type INSERT INTO statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Updating Records in a Table
To modify the data that is currently in a table, you use the UPDATE statement, which is commonly referred to as an update query. The UPDATE statement can modify one or more records and generally takes this form:
Copy
UPDATE table name
SET field name = some value
To update all the records in a table, specify the table name, and then use the SET clause to specify the field or fields to be changed.
Copy
UPDATE tblCustomers
SET Phone = 'None'
In most cases, you will want to qualify the UPDATE statement with a WHERE clause to limit the number of records changed.
Copy
UPDATE tblCustomers
SET Email = 'None'
WHERE [Last Name] = 'Smith'
For more information about using the UPDATE statement, type UPDATE statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Deleting Records from a Table
To delete the data that is currently in a table, you use the DELETE statement, which is commonly referred to as a delete query, also known as truncating a table. The DELETE statement can remove one or more records from a table and generally takes this form:
Copy
DELETE FROM table list
The DELETE statement does not remove the table structure, only the data that is currently being held by the table structure. To remove all the records from a table, use the DELETE statement and specify which table or tables you want to delete all the records from.
Copy
DELETE FROM tblInvoices
In most cases, you will want to qualify the DELETE statement with a WHERE clause to limit the number of records to be removed.
Copy
DELETE FROM tblInvoices
WHERE InvoiceID = 3
If you want to remove data only from certain fields in a table, use the UPDATE statement and set those fields equal to NULL, but only if they are nullable fields. For more information about nullable fields, see "Intermediate Microsoft Jet SQL for Access 2000."
Copy
UPDATE tblCustomers
SET Email = Null
For more information about using the DELETE statement, type DELETE statement in the Office Assistant or on the Answer Wizard tab in the Microsoft Access Help window, and then click Search.
Using SQL in Access
Now that we've had a basic overview of the SQL syntax, let's look at some of the ways we can use it in an Access application. To do this, we'll use the sample database included with this article. Through queries and sample code, the acFundSQL.mdb sample demonstrates the different SQL statements discussed in this article.
NoteMany of the sample queries used in acFundSQL.mdb depend on certain tables existing and containing data. Because some of the queries in acFundSQL.mdb alter the data or the database structure, you may eventually have difficulty running other queries due to missing or altered data, tables, or indexes. If this problem occurs, open the frmResetTables form and click the Reset Tables button to re-create the tables and their original default data. To manually step through the reset-table process, execute the following queries in the order they are listed:
Building Queries
Queries are SQL statements that are saved in an Access database and can be used at any time, either directly from the Access user interface or from the Visual Basic® for Applications (VBA) programming language. You can build queries by using query Design view, which greatly simplifies the building of SQL statements, or you can build queries by entering SQL statements directly in the SQL view window.
As mentioned at the beginning of this article, Access converts all data-oriented tasks in the database into SQL statements. To demonstrate this behavior, let's build a query in query Design view.
1. Open the acFundSQL.mdb database.
1. Make sure that the tblCustomers table has been created and that it contains some data.
2. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
3. In the New Query dialog box, click Design View, and then click OK.
4. In the Show Table dialog box, click tblCustomers, click Add, and then click Close.
5. In the tblCustomers field list, click the asterisk (*) and drag it to the first field in the query design grid.
6. On the View menu, click SQL View. This opens the SQL view window and displays the SQL syntax that Access is using for this query.
NoteThis query is similar to the Select All Customers query already saved in the acFundSQL database.
Specifying a Data Source
To make a connection to data in the database's tables, Access objects use data source properties. For example, a form has a RecordSource property that connects it to a particular table in the database. Anywhere that a data source is specified, you can use an SQL statement (or a saved query) instead of the name of a table. For example, let's build a new form that connects to the customers table by using an SQL SELECT statement as the data source.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. In the Database window, click Forms under Objects, and then click New on the Database window toolbar
3. In the New Form dialog box, click Design View, and then click OK. A blank form is now open in Design view.
4. On the View menu, click Properties to open the form's property sheet.
5. In the RecordSource property text box, type the following SQL statement:
Copy
SELECT * FROM tblCustomers
6. Press the ENTER key on your keyboard. The field list appears, and it lists all of the available fields from the tblCustomers table.
7. Select all of the fields by holding down the SHIFT key and clicking the first and then the last field listed.
8. Drag the selected fields to the center of the Detail section on the blank form and then release the mouse button.
9. Close the property sheet.
10. On the View menu, click Form View, and then use the record selectors at the bottom of the form to scroll through all the records in the tblCustomers table.
Another great place to use an SQL statement is in the RowSource property for a list or combo box. Let's build a simple form with a combo box that uses an SQL SELECT statement as its row source.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. Create a new form and open it in Design view.
3. On the View menu, click Toolbox.
4. Make sure that the Control Wizards (upper rightmost) button in the toolbox is not pressed in.
5. Click the Combo Box button and then click in the center of the blank form's Detail section.
6. Make sure that the combo box in the form is selected, and then click Properties on the View menu.
7. In the RowSource property text box, type the following SQL statement:
Copy
SELECT [Last Name] FROM tblCustomers
8. Press ENTER, and then close the property sheet.
9. On the View menu, click Form View. In the form, click the down arrow next to the combo box. Note that all the last names from the customers table are listed in the combo box.
Using SQL Statements Inline
The process of using SQL statements within VBA code is referred to as using the statements "inline." Although a deep discussion of how to use VBA is outside the scope of this article, it is a straightforward task to execute SQL statements in VBA code.
Suppose we need to run an UPDATE statement from code, and we want to run the code when a user clicks a button on a form.
1. Open the acFundSQL.mdb database and make sure that the tblCustomers table has been created and that it contains some data.
2. Create a new form and open it in Design view.
3. On the View menu, click Toolbox.
4. Make sure that the Control Wizards (upper rightmost) button in the toolbox is not pressed in.
5. Click the Command Button button and then click in the center of the blank form's Detail section.
6. Make sure that the command button in the form is selected, and then click Properties on the View menu.
7. Click in the following property text boxes and enter the values given:
Name: cmdUpdatePhones
Caption: Update Phones
8. Click the OnClick property text box, click the Build button (…), and then click Code Builder to open the Visual Basic Editor.
9. Type or paste the following lines of code in the cmdUpdatePhones_Click subprocedure:
Copy
Dim conDatabase As ADODB.Connection
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "UPDATE tblCustomers SET Phone = 'None'"
conDatabase.Execute strSQL
MsgBox "All phones have been set to ""None""."
conDatabase.Close
Set conDatabase = Nothing
10. Close the Visual Basic Editor, close the property sheet, and then click Form View on the View menu.
11. Click the Update Phones button. You should see a message box that says all the phone numbers have been set to "None." You can verify this by opening the tblCustomers table.
Although using SQL statements inline is great for action queries (that is, append, delete, and update), they are most often used in select queries to build sets of records. Let's suppose that we want to loop through a results-based set to accomplish what the UPDATE statement did. Following a similar procedure for the UPDATE example, use the following code in the cmdSelectPhones_Click subprocedure:
Copy
Dim conDatabase As ADODB.Connection
Dim rstCustomers As ADODB.Recordset
Dim strSQL As String
Set conDatabase = CurrentProject.Connection
strSQL = "SELECT Phone FROM tblCustomers"
Set rstCustomers = New Recordset
rstCustomers.Open strSQL, conDatabase, _
adOpenDynamic, adLockOptimistic
With rstCustomers
Do While Not .EOF
!Phone = "None"
.Update
.MoveNext
Loop
End With
MsgBox "All phones have been set to ""None""."
rstCustomers.Close
conDatabase.Close
Set rstCustomers = Nothing
Set conDatabase = Nothing
In most cases, you will achieve better performance by using the UPDATE statement because it acts on the table as a whole, treating it as a single set of records. However, there may be some situations where you simply must loop through a set of records in order to achieve the results you need.
One Last Comment
Although it may be difficult to believe, this article has only scratched the surface of the SQL language as it applies to Access. By now you should have a good basic understanding of SQL and how you can use it in your Access 2000 applications. Try out your new skills by using SQL in any RecordSource or RowSource property you can find, and use the resources listed in the next section to further your knowledge of SQL and Access.
Native Proud of Asiad Kabaddi Gold Medalist Mamata Poojary
After all no one in her family is connected to sports field. But when Mamatha Poojary, who hails from an agriculture background family settled in a small village, called Hermunde achieves the peak at the international level sports event through her hard work, villagers with innocent eyes tend to be grateful for the achievement done by ‘their’ girl.
Having agricultural family background, overcoming all difficulties that is part and parcel of village life on an average, inculcating valor attitude in her, Mamata could make it up to the stage where hundred crore population of India could witness Indian flag held high in a foreign land.
Mamata Poojary of Hermunde village near Karkala clinched the yellow metal in women's kabaddi final match in Asian Games defeating the strong Thailand team.
Eldest Daughter
Mamata’s birthplace is Kelamaraje of Hermunde village in Karkala taluk of Udupi district. She was born to farmer Bhoja Poojary and Kitti Poojary couple. Mamata who had to put in immense effort had passion for sports, is now the ray of hope for the parents, village and the nation at large. She has a younger sister Madhura and elder brother Vishwanath.
Born in the year of 1986, Mamata finished her primary education in Hermunde, and then high school at Jyothi High School at Ajekar. Afterwards she finished her pre-university education in Muniyalu Government PU College and later pursued her occasional course. She obtained her Bachelor of Arts degree in Gokarnanatheshwara College, Mangalore. Passion for sports was the clincher for her. Due to her success in sports, she also got employment in South Railway in Hyderabad.
During her school days itself she showed signs of her capacity by bagging loads of awards by winning in volleyball, javelin, short-put, kabaddi. With this she helped the name of her institutions shine at the national level. She engaged herself more in kabaddi during her college days. Dronacharya awardee senior kabaddi player Ajekar Ramesh Suvarna encouraged her during initial days while she was studying in Government Junior College, Muniyal . Apart from uphill struggle by Mamata, it is noteworthy that Ramesh Suvarna’s eagerness towards promoting a rural talent has today put the name of a small village up at the international map. He has lauded the achievement of Mamata in international sports event.
Awards
She initiated her hunt for awards when she represented Mangalore University team at the international level kabaddi match held at Tirunelveli. She pocketed gold medal in that match. Then she won medal in All India Open Kabaddi Tournament held in Hingaat and Dadar. During the year 2006 she won gold by representing Indian women kabaddi team at the South Asia Games held in Colombo, Srilanka.
She became the golden girl in first Asian beach sports kabaddi match held at Baali, in second Asian women kabaddi championship held at Tehran of Iran and in the third women kabaddi championship held in Chennai. And now, at the Asian Games in Guangzhou, China.
Felicitation Galore
Various local organizations like Karkala Bus Agents' Union, Mumbai Mulund Friends Club, Karkala JCI and villagers of Hermunde felicitated this icon at Karkala taluk level literary convention. Her parents have expressed hearty gratitude for recognizing their child. They have also appealed the government to motivate her and give her the deserved provisions, in tune with foreign countries where great support is provided to their sports faculty.
Journalist Devaraya Prabhu has decided to organize a public felicitation programme in Karkala. Many people like doctors in Ajekar village, zilla panchayat vice-president Dr Santhosh Kumar Shetty, office-bearers of Kurpady Youth Club, Shivakumar Kurpady, trustee of Radha Nayak Trust, Ajekar panchayat members William, Pramod Damodar, photographer of Mumbai Laxmish Shetty Ajekar and journalist Sathyendra Ajekar among others lauded the effort of Mamata and congratulated her.
Yes, more so the time is right for the government to prove that it is in support of rural sports icon.
Thursday, November 25, 2010
Asian Games: Kundapur girl Ashwini Chidananda Akkunje Shetty bags gold in 400 mts hurdles
Ashwini Chidananda Akkunje Shetty won a surprise gold in the women's 400 metres hurdles while her compatriot Juana Murmu missed the bronze by a whisker at the Asian Games here on Thursday.
With a personal best timing of 56.15 seconds, Ashwini clinched the third gold medal for India from the track and field event of the Asian Games at the Aoti Main Stadium.
The silver went to China's Wang Xing with a timing of 56.76 secs and Japan's Satomi Kubokokura got the bronze with a timing of 56.83 secs.
Jauna was fourth in 56.88 secs.
Ashwini had won Gold for India in the 4x400 mts womens' relay in the recently held Commonwealth Games at Delhi. She was also conferred with the Karnataka Rajyotsava Award this year.
Subscribe to:
Posts (Atom)
