- SQL stands for Structured Query Language. SQL is the industry's standard programming language used to create databases, manipulate, making queries and retrieve data, and provide security to relational database structures.
-
SQL is a support
language that works with database
programs like MS Access, MS SQL Server, Oracle, DB2, Informix,
etc. (Although there
is a standard SQL, no database program actually implements the
full standard).
- It is also called Jet SQL. (You use Jet SQL to interact with data through the database Jet engine behind Microsoft Access).
Combining with VBA, VB.Net, DAO, ADO, ADO.Net and ADOX that Access understands, SQL provides you more control and power over your database. Jet SQL is broken up into two distinct components:
- Data Manipulation Language (DML) - to retrieve, update, delete and add data, and navigate through data.
- Data Definition Language (DDL) - to manage objects in your database such as establishing relationships among tables.
Jet SQL, by itself, cannot create a database and cannot manage security. That is where DAO and ADOX libraries come in to support. Some people will say that they do not need to know SQL in order to program Access, or they can use SQL without DAO, ADO or ADOX. They are basically limiting their options to effectively handling the data.
If you used Access often, you probably have built many queries to retrieve data using the graphical query grid to drag and drop the fields into the grid. You may not realized Access was actually writing SQL in the background for you. Select View | SQL View when you are in the Design View of your query, you will see SQL written behind the scene like this [see diagram].
Basic components of SQL statements include the following:
- Keywords – reserved words that have predefined meaning such as SELECT, CREATE, etc. Most keywords can include arguments, just like functions and procedures. A keyword with arguments is called a clause such as FROM, WHERE, etc. Combined clauses that make a request of database engine are called statements.
- Special characters – reserved characters that logically affect the meaning and arrangement of keywords, identifiers. Examples include ( ),[ ], *, $, commas, periods and semicolons.
- Operators – special characters that perform logical or mathematical operations such as: addition (+), subtraction (-), division (/), multiplication (*), factorial (!) and absolute value (@). Common comparison operators include equivalence (=), less than (<) and greater than (>). Common logical operators include AND, OR, and NOT.
- Constants - data values that can be of type string, bit string, integer, floating point and Boolean.
|
Keywords: ADD ALTER COLUMN ALTER TABLE CREATE TABLE CREATE INDEX CREATE VIEW DEFAULT DELETE DROP INSERT INTO PARAMETERS SELECT SELECT INTO UPDATE ... SET TRANSFORM
Clauses:
Operators: |
Predicates: ALL ANY DISTINCT DISTINCTROW EXISTS EXISTS TOP TOP PERCENT
Variable Type Functions: |
Aggregate Functions: Avg Count (*) Count (ColumnName) First Last Max Min Sum
Date
& Time Functions: Queries: |
String Functions: Asc, AscB, AscW Chr, Chr$ ChrB, ChrB$ ChrW, ChrW$ Join LCase LCase$ Len, LenB Left, Left$ LeftB, LeftB$ LTrim, LTrim$ Instr, InstrB InstrRev Mid, Mid$ MidB, MidB$ Replace Right, Right$ RightB, RightB$ RTrim, RTrim$ Space, Space$ Split Str, Str$ StrComp StrConv StrReverse Trim, Trim$ UCase, UCase$ Val Concatenate what are these $ signs ?? |
To try out all the SQL examples here, you
have to insert the SQL strings into the Module procedure using
ADO, ADOX, DAO
or DoCmd objects. See
examples right below:
|
Sub CreateTable() Dim cn As ADODB.Connection Dim cat As New ADOX.Catalog Dim strSql As String Set cn = CurrentProject.Connection cat.ActiveConnection = CurrentProject.Connection strSql = " CREATE TABLE MaterialList(PartNumber Integer NOT NULL UNIQUE , " _ & "PartDescription Varchar (60) NOT NULL , ReleasedDate DATE )" cn.Execute strSql cat.Tables.Delete " MaterialList " ' this line deletes your table Set cn = Nothing Set cat = Nothing End Sub
|
CREATE TABLE - Used to create a new table and its fields. The syntax is:
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
Example:
|
CREATE TABLE
MaterialList (PartNumber Integer NOT NULL, PartDescription Varchar (60), ReleasedDate Date); |
ADD - Used to add a column to an existing table. The syntax is:
ALTER TABLE "table_name"
ADD Columnname Datatype (Field size);
Example:
Adding a column 'StandardCost' in currency type.
|
ALTER TABLE
MaterialList ADD StandardCost MONEY; |
ALTER TABLE
- The functions are:
To Add or Drop a column and indexes, to and from an existing table.
To change the column name and data type for any column in a table.
To Add Constraints such as Not Null, Primary Key, Foreign Key,
CHECK, etc in a table.
ALTER TABLE table_name
ADD column_name data_type |
DROP COLUMN column_name |
ADD CONSTRAINT ColumnConstraint |
DROP CONSTRAINT ColumnConstraint |
Example:
To Add a column 'QtyOnHand' of SmallInteger
datatype. Add a column '
Country_Of_Make' of character field size 15, null not allowed, and specify a default
value as 'Singapore'. Add a column 'MaterialClass' of character field size 2, and delete the column 'QtyOnHand'.
|
ALTER
TABLE
MaterialList ADD QtyOnHand SMALLINT, Country_Of_Make VARCHAR(15) DEFAULT Singapore NOT NULL; ALTER TABLE MaterialList ADD MaterialClass CHAR(2); ALTER TABLE MaterialList DROP QtyOnHand; |
ALTER COLUMN - Used to specify new data type and field size for the column. The syntax is:
ALTER
TABLE table_name
ALTER COLUMN column_name data_type (field size);
Example:
CREATE INDEX
- Used to index one or more fields in an existing table. The syntax
is: CREATE INDEX
index_name
Example:
CREATE VIEW
-
Used to instruct database system to create a new view. The syntax
is: CREATE VIEW
view_name Example:
DEFAULT
-
Used to set a default value for a column (use in ALTER TABLE
statement). The syntax is:
ALTER TABLE table_name Example:
DELETE
- Used to delete records (all rows) from a table. This is actually
the build-in 'Delete
Query' in the QBE interface.
DELETE FROM table_name
Example:
ALTER TABLE
table_name DROP column_name;
Example:
INSERT INTO
-
Used to insert (or append) records into any existing table. In QBE
interface, this is the 'Update
Query' . INSERT INTO table_name
(column1, column2, ...)
INSERT INTO table_name (column1 column2, ...)
Example:
INSERT
INTO
MaterialList (PartNumber, PartDescription, MaterialClass,
QtyOnHand)
SELECT
PartNumber, PartDescription, MaterialClass,
QtyOnHand SELECT
- Used to retrieves records from one or more tables. The syntax is:
SELECT column_name FROM table_name
Example:
SELECT INTO
- Used to copy an existing table's structure and data to a new table.
It is actually the build-in 'Make-Table
Query...' in the QBE (Query-By-Example) interface. The syntax is:
SELECT column1, column2, ...
Example:
SELECT
* INTO
[Excel 8.0; Database=C:\Q4List.xls].[Q4]
PARAMETERS
- Used to d
PARAMETERS name datatype [, name datatype [, …]]
Example 1: It expects 3 parameters with
respective datatype to be provided, and then applies the criteria to
the matching records in the specified table. See the
diagram in
design View provided by the SQL code below. You can
download database. In the new query view,
go to the menu, under Queries>Parameters... , to the right of
your parameters (enclosed by brackets), use the drop-down list to
select the correct datatype.
TRANSFORM
- Applies an aggregate function to a SELECT statement and creates a
Crosstab Query to display the results.
PARAMETERS expression
Example 1:
UPDATE
- Used to update records or entire column of that table.
This is actually the build-in 'Update
Query' in the QBE interface.
UPDATE table_name
The
SET keyword is used to specify the
column to update and the value to insert into the column.
Example :
UPDATE
MaterialList
SET
StandardCost = StandardCost * 1.05, QtyOnHand = 0
UPDATE
MaterialList Clauses: ALIAS
-
An alternate name for a table or column.
Example:
SELECT column1_name AS
new_name1, column2_name AS new_name2, column3, ...
Example: To retrieve data from columns
'PartNumber' and 'ReleasedDate' with new name as 'FirmDate', from
table 'MaterialList', into a new table 'tbl_TempList'. FROM
- It is followed after every SELECT keyword. First you SELECT the
field name, then use the FROM to specify which table you want to use
for the select. The syntax is:
SELECT column_name FROM table_name
Example:
WHERE
WHERE [search conditions];
E
ASC , DESC
To change column 'MaterialClass' field size to 1 character, and
change column 'Country_Of_Make'
default character
to 'SG' with field size to 2.
ALTER
TABLE
MaterialList
ALTER COLUMN
MaterialClass CHAR(1);
ALTER TABLE MaterialList
ALTER COLUMN
Country_Of_Make VARCHAR(2) DEFAULT SG
ON table_name (column_name)
To create an index on the
column 'MaterialList'.
CREATE
INDEX
Idx_MaterialList
ON
MaterialList (PartNumber);
AS SQL_statement
To create an SQL View
of 'PartNumber' and 'MaterialClass' from the table
'MaterialList.
A View does not physically store the data.
CREATE
VIEW
view_MyPartList
AS
SELECT PartNumber, MaterialClass FROM MaterialList;
ALTER COLUMN column_name data_type (field size) DEFAULT (default
value)
To set a default text 'Singapore' in the column 'Country_Of_Make' with field size 15.
ALTER TABLE MaterialList
ALTER COLUMN Country_Of_Make VARCHAR(15)
DEFAULT
Singapore
The syntax is:
WHERE {condition}
To delete those records from 'MaterialList table where 'StandardCost'
is less than $1.00 and 'ReleasedDate' earlier than 1/1/2000.
DELETE
FROM
MaterialList
WHERE StandardCost < 1.00
AND ReleasedDate < #1/1/2000#;
DROP
-
Used to delete table, delete view and index from a table. The syntax
is:
DROP VIEW view_name;
DROP INDEX index_name ON table_name;
ALTER
TABLE MaterialList
DROP
QtyOnHand;
DROP
VIEW view_MyPartList_;
DROP
INDEX
Index_MaterialList
ON
table_name;
The
syntax is:
VALUES (value1, value2, ...)
SELECT column3, column4, ...
FROM another_tablename
To copy columns PartNumber, PartDescription, MaterialClass
and QtyOnHand from table
MaterialList_Revised to table
MaterialList, only where these conditions are true - ReleasedDate
prior to 12/31/2005 and StandardCost greater then $100.
INSERT
INTO
MaterialList (PartNumber, PartDescription, MaterialClass,
QtyOnHand)
VALUES
('118168', 'Hub Bearing', 'B', NULL);
FROM
MaterialList_Revised
WHERE ReleasedDate < #12/31/2005# AND StandardCost
>100;
SELECT column_name
INTO new_table
FROM derived_table
WHERE conditions
HAVING conditions
ORDER BY expression
To demonstrate selecting column(s) and selecting all the columns from the table.
SELECT
PartNumber
FROM
MaterialList
SELECT
PartNumber, StandardCost
FROM
MaterialList
SELECT
*
FROM
MaterialList
INTO new_table
FROM table1
WHERE conditions
The first example
demonstrates copying records 'closing_stock$' and 'closing_qty' from
table 'Q3MaterialList' into a new table 'Q4MaterialList' where only
column 'closing_qty' has value greater than zero. The second shows
you that it is copying the records (where closing_stock$ is greater
$1) from table 'Q3MaterialList' into a new worksheet named 'Q4' from
an existing workbook 'Q4List.xls'
SELECT
closing_stock$, closing_qty
INTO
Q4MaterialList
FROM Q3MaterialList WHERE closing_qty >0
FROM
Q3MaterialList
WHERE closing_stock$ >1
[see the slide].
Note: you can use name but not datatype in a WHERE or HAVING clause.
' the last
parameter sets criteria to search for a text as part of a whole string:
Like "*" & [search
text] & "*")
PARAMETERS
[What StartDate?] DateTime, [What MinimumCost?] Currency,
[search text] Text (255);
SELECT ss.*
FROM tbl_SafetyStockChangeRecord AS ss
WHERE (((ss.PartNumber) Like "*" & [search text] & "*")
AND ((ss.DateChanged)>=[What StartDate?])
AND ((ss.StandardCost)>=[What MinimumCost?]));
The syntax is:
TRANSFORM aggregate_function
SELECT any_statement
PIVOT pivotfield [IN (value1 [, value2 [, ...] ] ) ]
To
create this crosstab query, first open a
new query, then switch to SQL View, paste from the code below, save
a query name.
It pivots the part numbers on the rows,
compute the gross sales and populate the
values amount over
the time period as column headings
(formatting as 'mmm-yyyy'),
and match to only those part numbers that end with a 'B' suffix.
The table on the right in the
diagram is the result data.
Download
this database. Notice that the query output the column headers in
alphabetical order - Dec, Feb, Jan, etc. You have to use Fixed
Column Headings to tell Access to re-arrange them in the correct
order. Click here
another page see in detail on how to adjust fixed column
headings.
TRANSFORM
Sum([QtySold]*[SalesPrice]) AS GrossSales
SELECT Past6MonthsSales.PartNumber
FROM Past6MonthsSales
WHERE (((Past6MonthsSales.PartNumber) Not Like "*B"))
GROUP BY Past6MonthsSales.PartNumber
PIVOT Format([DateSold],"mmm-yyyy");
T
Example 2:
Finally, the query returns for the selected year a list of different
instruments with sales figures for each month (i.e., The PIVOT is
pivoting over months, therefore each month, for which sales
occurred, has an individual column)
PARAMETERS [Which Year?]
Long;
TRANSFORM
Sum([Shipping].Quantity * [Shipping].UnitPrice) AS GrossIncome
SELECT InstrumentName FROM PurchaseOrder
INNER JOIN (InstrumentCataloq INNER JOIN [Shipping]
ON InstrumentCatalog.CatNum = [Shipping].CatNum)
ON PurchaseOrder.OrderNum = [Shipping].OrderNum
WHERE DatePart("yyyy", PurchaseDate) = [Which Year?]
GROUP BY PurchaseOrder.InstrumentName
ORDER BY PurchaseOrder.InstrumentName
PIVOT DatePart("m",PurchaseDate);
The syntax is:
SET column_1 = [new value], column_2 = [value2] ...
WHERE {condition}
The first example
is to update the records 'StandardCost' incrementing by 5% in the
table 'MaterialList', for all the Part Numbers that end with suffix
' W '. The 2nd example is to update the table with the columns
'StandardCost' marks up by 5% and input 'QtyOnHand' = 0, where the
part number is ' 12345W '. The 3rd example converts only those part
numbers that are Numeric, to String. This is useful if you have a
long mix of part numbers that are both numbers and text.
UPDATE
MaterialList
SET
StandardCost =
StandardCost * 1.05
WHERE PartNumber LIKE '%W';
WHERE PartNumber ='12345W';
SET
MaterialList.PartNumber
= IIf(IsNumeric([PartNumber])=True,CStr(Val([PartNumber])),[PartNumber]);
Aliases are created using the AS clause. See the syntax and example
for the AS clause.
SELECT PartNumber, QtyOnHand AS QOH,
MaterialClass
AS MC
INTO
tbl_TempList
FROM MaterialList;
AS
-
Used to assign an alternate name or a temporary new name, to a column or table. The syntax is:
FROM table_name
SELECT PartNumber,
ReleasedDate AS FirmDate INTO tbl_TempList
FROM MaterialList;
SELECT
*
FROM
MaterialList
It copies all the table's record into a new table 'tbl_TempList',
only for all the part numbers that end with a suffix ' W '
ORDER BY
- Used to sort retrieved records in an ascending or descending
order.
SELECT * INTO tbl_TempList
FROM MaterialList
WHERE
PartNumber Like '*W';
See the next syntax and example for ASC, DESC clauses.
SELECT column_name FROM table_name
[WHERE condition]
ORDER BY column_name [ASC | DESC]
Example: To retrieve data from columns 'PartNumber' and 'ReleasedDate' with new name as 'FirmDate', from table 'MaterialList', into a new table 'tbl_TempList'.
|
SELECT PartNumber, StandardCost, ReleasedDate INTO tbl_TempList FROM MaterialList ORDER BY StandardCost DESC; |
GROUP BY
- Used with aggregate functions to combine groups of records into a
single functional record.
Note:
GROUP BY clause can only be used in queries that contain at least
one
aggregate function. All column names retrieved from the database
specified after the SELECT keyword must be present in the GROUP BY
clause (this does not include column names that are specified within
the functions or alternate column names (aliases).
For an example, in the query grid I dragged three columns to the grid. under View menu, select 'Totals' (or click on the '∑' sign), it will bring you to the aggregate functions query. The default is GROUP BY when you you did not select any function. Now using the drop-down of the Total option, you can select any of the predefined functions. Suppose you want to select the function SUM for the field 'QtyOnHand' [see diagram]. Suppose you also want to change the default fieldname SumOfQtyOnHand to Total_QOH [see diagram]. Note that the full fieldname is Total_QOH: QtyOnHand. Total_QOH is the alias and QtyOnHand is the actual fieldname. Below is the result. You can also see it in the SQL View.
|
SELECT PartNumber, StandardCost,
SUM(QtyOnHand) AS Total_QOH FROM MaterialList GROUP BY PartNumber, StandardCost ORDER BY PartNumber; |
From the preceding GROUP BY example, suppose you also want to include condition of Total_QOH > or = 2 and sort column 'StandardCost' by descending order [see diagram]. In SQL View, it produces the result as below:
| SELECT
MaterialList.PartNumber, MaterialList.StandardCost,
Sum(MaterialList.QtyOnHand) AS Total_QOH FROM MaterialList GROUP BY MaterialList.PartNumber, MaterialList.StandardCost HAVING (((Sum(MaterialList.QtyOnHand))>=2)) ORDER BY MaterialList.StandardCost DESC; |
Now I simplify it INSERT INTOke a table 'tbl_TEmpList' for the queried data (it is the same as you do with the Make-Table Query), the statement now looks like this below:
|
SELECT PartNumber, StandardCost,
Sum(QtyOnHand) AS Total_QOH INTO tbl_TempList FROM MaterialList GROUP BY PartNumber, StandardCost HAVING Sum(QtyOnHand)>=2 ORDER BY StandardCost DESC; |
This next example shows you how to use the HAVING clause with WHERE
clause [see diagram].
|
SELECT PartNumber, StandardCost,
Sum(QtyOnHand) AS Total_QOH INTO tbl_TempList FROM MaterialList WHERE StandardCost >200 and StandardCost<1000 GROUP BY PartNumber, StandardCost HAVING Sum(QtyOnHand)>=2 ORDER BY StandardCost DESC; |
CONSTRAINT
- Allow you to further
control the type of data that is entered into a table. Constraints
can be specified when the table is first created via the CREATE
TABLE statement, or after the table is already created via the
ALTER TABLE statement.
Types of SQL Constraints
| NOT NULL | To specify on a column that NULL empty (not blank) is not allowed. |
| UNIQUE | To specify that all values in a column are distinct and unique values; repetition not allowed. |
| CHECK | To set criterion for the data entered into a column. |
| PRIMARY KEY | To uniquely identify every record in a table. |
| FOREIGN KEY | To link records of a table to the records of another table. |
Example:
|
CREATE TABLE MaterialList (PartNumber Integer NOT NULL, PartDescription Varchar (60) NOT NULL, ReleasedDate DateTime); |
|
CREATE TABLE MaterialList (PartNumber Integer NOT NULL UNIQUE, PartDescription Varchar (60) NOT NULL, ReleasedDate Date); |
Example: setting criterion for the date entered into the column 'ReleasedDate' which must be after 12/31/2000.
|
CREATE TABLE
MaterialList (PartNumber Integer NOT NULL UNIQUE, PartDescription Varchar (60) NOT NULL, ReleasedDate Date, CHECK ( Released Date > #12/31/2000#)); |
Example of how to specify the Primary Key when creating the
MaterialList table.
|
CREATE TABLE MaterialList (PartNumber Integer NOT NULL UNIQUE, PRIMARY KEY (PartNumber), PartDescription Varchar (60), ReleasedDate Date); |
Example of how to specify the
Foreign Key when creating the tbl_CustomerPartsList
table:
For our own convenience we usually use the same names for the
Primary key column and Foreign Key column.
|
CREATE TABLE tbl_CustomerPartsList (CustomerPartNumber Integer PRIMARY KEY, QtyRequested SMALLINT NOT NULL, ReleasedDate DateTime, PartNumber Integer REFERENCES MaterialList (PartNumber)); |
Below are examples of specifying SQL
has already been created.
Note :
ALTER TABLE tbl_CustomerPartsList ALTER TABLE MaterialList
ADD CHECK (ReleasedDate
> #31/12/2000#);
Before using the SQL ALTER TABLE command to add an SQL
Primary Key Constraint, make sure that the field
is defined as 'NOT NULL' . The table 'tbl_CustomerPartsList'
has already been created
and Foreign Key has not yet been put in.
ALTER TABLE MaterialList
ADD
PRIMARY KEY
(PartDescription);
ADD FOREIGN KEY (PartNumber)
REFERENCES MaterialList (PartNumber;
WITH - Used to enforce validation rule. With clause has four options: UNIQUE, PRIMARY, DISALLOW NULL, AND IGNORE NULL.
| WITH clause options: |
Description: |
| UNIQUE | Used to ensure that only unique, non-repeating values are inserted in an indexed column. |
| PRIMARY | Used to designate a column as a primary key. |
| DISALLOW NULL | Used to prevent null data from being inserted into a column. |
| IGNORE NULL | Used to cause null data in a table to be ignored for an index (not to be counted. |
The syntax is:
CREATE INDEX index_name
ON table_name (column_name)
WITH option
Example:
To create an index on the
column 'MaterialList'.
CREATE
UNIQUE INDEX idx_MaterialList
ON MaterialList (PartNumber)
WITH
DISALLOW NULL
Operators:
AND & OR
- The AND operator requires that both expressions on either side of the AND operator to be true in order for the data to be returned. The OR operator requires that at least one expression on either side of the OR operator to be true in order for the data to be returned.
Example:
To create a query called 'view_TempList' by selecting
all the records from table 'MaterialList', where material class can
be either A or B, and 'ReleasedDate' must be before 1/1/2005.
|
CREATE VIEW view_TempList AS
SELECT * FROM MaterialList WHERE (MaterialClass = 'A' OR MaterialClass = 'B') AND ReleasedDate < #1/1/2005#; |
Examples:
The first example creates a View called 'view_TempList' by selecting the records from table 'MaterialList', with the column 'ReleasedDate' that have dates between 1/1/2006 and 2/2/2007. The second example has the same effect as the first except that the command saved the retrieved data into a newly created called 'table_TempList'.
|
CREATE VIEW view_TempList AS
SELECT * FROM MaterialList WHERE ReleasedDate BETWEEN #1/1/2006# AND #2/2/2007#;
|
ON - Used to specify a condition. ON is used after the JOIN operators.
See the examples in the INNER JOIN and OUTER JOIN.
IN - Used to match conditions in a list of expressions.
Example 1: To retrieve all the records in the table where field 'MaterialClass' has either the value 'A', 'B' or 'C". [see diagram]
|
CREATE VIEW view_TempList AS
SELECT * FROM MaterialList WHERE MaterialClass IN ('A', 'B', 'C'); |
Example 2: To retrieve the specified records from another database,
use IN, then followed by a path and database name,
Access dynamic table linking will
retrieve the required data.
|
SELECT Date_Changed,
Part_Number, Old_SSL, New_SSL, Change_By, Request_By INTO tbl_TempList FROM SafetyStock_ChangeRecord IN 'J:\Planning\SSLChangeRecord.mdb'; |
Example 1:
Below SQL statement provides all records excluding values of 'A', 'B' or 'C' in the field 'MaterialClass'. [see diagram]
|
CREATE VIEW view_TempList AS
SELECT * FROM MaterialList WHERE MaterialClass NOT IN ('A', 'B', 'C'); |
Example 2:
Below I used fieldname aliases. Also I used subquery as a second
query (from CustomerList) that is called by the first (from
MaterialList)
using INNER JOIN. It retrieves all the records from the second table
'CustomerList' to the main table 'MaterialList',
excluding all the rows in the second table where customer name is
'SSMC' and where PurchaseOrder dates are earlier than 1/1/2006.
[see diagram]
CREATE VIEW view_TempList AS
SELECT *
FROM MaterialList AS M
INNER JOIN CustomerList AS C
ON M.PartNumber = C.PartNumber
WHERE (M.PartNumber)
NOT IN
(SELECT PartNumber FROM CustomerList
WHERE CustomerName ='SSMC' AND PO_Date < #1/1/2006#);
SELECT fieldlist
FROM table1
INNER JOIN table2
ON table1.field1 {comparison_operator} table2.field2
Example 1:
You must use ON after INNER JOIN. The field common to both tables
MaterialList and CustomerList is 'PartNumber'. It saves into a query
name for the retrieved data - part number, qty on-hand, order qty
and required date - on the matching values of common part numbers. [see
diagram]
CREATE VIEW view_TempList AS
SELECT MaterialList.PartNumber, MaterialList.QtyOnHand,
CustomerList.OrderQty, CustomerList.RequiredDate
FROM MaterialList
INNER JOIN
CustomerList
ON
MaterialList.PartNumber = CustomerList.PartNumber
;
Example 2:
This has the same effect as the above example. The difference is
that I am using
WHERE instead of the INNER JOIN, and also notice that I substitute
the real field names with aliases because the original field names
are long cumbersome. [see
diagram]
CREATE VIEW view_TempList AS
SELECT M.PartNumber, M.QtyOnHand, C.OrderQty, C.RequiredDate
FROM MaterialList AS M, CustomerList AS C
WHERE
(M.PartNumber=[C].[PartNumber]);
SELECT fieldlist
FROM table1
LEFT JOIN table2
ON table1.field1 {comparison_operator} table2.field2
Example: [see diagram].
Notice that I use aliases instead of the
real field names.
Example: [see diagram].
Notice that I use aliases instead of the
real field names.
CREATE VIEW view_TempList AS
SELECT M.PartNumber, M.QtyOnHand, C.OrderQty, C.RequiredDate
FROM MaterialList AS M
LEFT JOIN
CustomerList AS C
ON
M.PartNumber = C.PartNumber;
RIGHT OUTER JOIN
- The RIGHT JOIN operator
is used to create a right outer join and includes all of the records
from the second (right) of the two tables, even if there are no
matching values for records in the first table.
CREATE VIEW view_TempList AS
SELECT M.PartNumber, M.QtyOnHand, C.OrderQty, C.RequiredDate
FROM MaterialList AS M
RIGHT JOIN
CustomerList AS C
ON
M.PartNumber = C.PartNumber
ORDER BY
C.RequiredDate;
SELF JOIN
Example: [see diagram].
Example: [see diagram].
CREATE VIEW view_TempList AS
SELECT M1.PartNumber, M1.StandardCost, M1.QtyOnHand,
M1.FamilyType
FROM MaterialList AS M1, MaterialList AS M2
WHERE ([M1.PartNumber]=[M2].[PartNumber])
AND ([M2].[PartNumber] LIKE ""*W"");
NESTED JOIN
- Allows you to link data with common fields in multiple tables.
|
' the outcome you can see from
the SQL View. SELECT MaterialList.*, CustomerList.CustomerName, CustomerList.ListPrice, OrdersHistory.SalesOrder, OrdersHistory.DeliveryQty, OrdersHistory.DeliveryDate FROM (MaterialList INNER JOIN CustomerList ON MaterialList.PartNumber = CustomerList.PartNumber) INNER JOIN OrdersHistory ON CustomerList.CustomerPartNumber = OrdersHistory.CustomerPartNumber; WHERE ((( CustomerList.ListPrice )>500)); ' the SQL statement after I put in the aliases. CREATE VIEW view_TempList AS SELECT M.*, C.CustomerName, C.ListPrice, O.SalesOrder, O.DeliveryQty, O.DeliveryDate FROM (MaterialList AS M INNER JOIN CustomerList AS C ON M.PartNumber = C.PartNumber) INNER JOIN OrdersHistory AS O ON C.CustomerPartNumber = O.CustomerPartNumber WHERE (((C.ListPrice)>500)); |
UNION - Used to combine records from two or
more queries while excluding duplicate
records. The only method of working with a UNION query is in SQL View,
so you have to begin with a blank query in Design View, then go to
SQL View. Take note when your query is converted into a Union Query,
you won't be able to go back to Design View again. The syntax is:
SELECT column_1, ... column_n
INTO [optional clause]
FROM table1
WHERE [optional clause]
GROUP BY [optional clause]
HAVING [optional clause]
UNION
SELECT column_1, ...column_n.
FROM table2
WHERE [optional clause]
GROUP BY [optional clause]
HAVING [optional clause]
ORDER BY [optional clause]
COMPUTE [optional clause]
Note: The UNION or UNION ALL operator cannot appear within a CREATE VIEW statement or within a Subquery. The UNION or UNION ALL operator can appear within an INSERT INTO.. SELECT statement. The INSERT INTO statement must be in the first query or else you will get an error message.
Note: GROUP BY and HAVING clauses can be used only within individual queries. ORDER BY and COMPUTE clauses are allowed only at the end of the UNION and UNION ALL statement to define the order of the final results or to compute summary values.
Example 1. Paste the statement into the SQL View and save it to a
query name.
|
SELECT PartNumber,
PartDescription FROM MaterialList UNION SELECT PartNumber, PartDescription FROM CustomerList; |
Example 2:
|
INSERT INTO
tbl_TempList SELECT * FROM MaterialList UNION SELECT * FROM CustomerList; |
Example 3:
|
SELECT PartNumber,
PartDescription INTO tbl_TempList FROM MaterialList
UNION SELECT PartNumber, CustomerPartNumber FROM CustomerList UNION SELECT CustomerPartNumber, SalesOrder FROM OrdersHistory; |
UNION ALL - Used to combine records from two or more queries into a single results set consisting of all the rows belonging to all queries in the union (i.e. including duplicate records).
Example:
|
SELECT PartNumber,
PartDescription, ReleasedDate FROM MaterialList WHERE ReleasedDate >#9/25/2005# AND PartNumber LIKE "A*" UNION ALL SELECT PartNumber, CustomerPartNumber, ListPrice FROM CustomerList WHERE ListPrice >=200 ORDER BY PartNumber; |
LIKE
- Used to match patterns in data. Below table shows you all the wildcard characters
that are used with the LIKE operator.
| Character |
Description |
Example |
| ? | Use as a single alphabetic character placeholder. | w?ll finds will, well, and wall |
| * | Matches any number of characters. You can use the asterisk anywhere in a character string. |
wh* finds why,
whom, whose, but not awhile. *ia finds Malaysia, Russia, but not Indian. *ia* finds Malaysian, Russian, but not India. |
| % | Represents any string of zero, one, or more characters. |
'A%' means
all the strings that start with 'A'. '%A' means all the strings that end with 'A'. '%A%' means all the strings that contain the pattern 'A' anywhere. '_IN%' means all the strings that contain a character, then 'IN', followed by anything else. For example, 'SINGAPORE' would satisfy the condition, while 'CHINA' would not. |
| # | The pound sign matches any single numeric character. |
1#3 finds 103,
113, 123 Also, #12/25/2005# finds the exact date or depends on your system's date format. |
| ! | Matches any character not in the brackets. The ! symbol means NOT. | L[!ui]e finds love, but not live or luve. |
| _(underscore) | Represents exactly one character. | 'A_W': All string that starts with 'A' and ends with 'W'. It means 'ASW' would meet the condition where as 'ASSW' would not. |
| - (hypen) | Matches any one of a range of characters. You must specify the range in ascending order (A to Z, not Z to A). | b[a-c]d finds bad, bbd, and bcd, but not bed or bid. |
| [ ] | Matches any character within the specified range. |
[C-G] or set as [cdefg]. [3-5] or set as [345]. 6[7-9]#9% means the find starts with 6, then a digit between 7 and 9, then any single digit, follow by 9, and the remainder can be any character string. |
' retrieve all the records
into a new table where part numbers are not digits from 1 to 4.
' retrieve all
the records into a new table where part numbers begin with
anything, then join by 'A' and - , and then the next
digit is not from 1 to 5, follow by a number, then end with
a character. Example, it can find 0240A-67B
Example:
retrieving all data from the table, where any record in the field 'StandardCost'
is empty but part number in the same row is not empty. [
LIKE
operator examples:
' selecting all records where
part numbers do not end with a suffix 'W' and material class
must be A, B or C.
SELECT * FROM MaterialList
WHERE
PartNumber
NOT
LIKE
'*W' AND MaterialClass LIKE '[A-C]';
SELECT * INTO tbl_TempList FROM MaterialList
WHERE PartNumber
LIKE
'[!1-4]';
SELECT * INTO tbl_TempList FROM MaterialList
WHERE
PartNumber
IS NULL - Used to determine if a field contains data.
SELECT *
INTO tbl_TempList
FROM MaterialList
WHERE StandardCost
IS NULL
AND PartNumber
IS NOT NULL
;
IS NOT NULL
- Used to determine if a field does not contain data. See above example.=, >, <, >=, <=, <>
(called Comparison or Relation operators)| Name: |
Symbol: |
| > | Greater Than |
| < | Less Than |
| = | Equal To |
| >= | Greater Than or Equal To |
| <= | Lesser Than or Equal To |
| <> | No Equal To |
Predicates:
Part of what makes SQL so powerful is its predicates, giving us many choices for selecting or rejecting data. Predicate is usually coded with the WHERE and HAVING clauses.
ALL
- Used to retrieve records from the main query that match All of the records in the subquery. ALL and ANY are called quantified predicates. SQL only allows ALL and ANY to be used with subqueries. When you use any of the comparison operators, the subquery must return a single value. The syntax is:SELECT fieldlist FROM datasource WHERE field comparison_operator ALL|ANY|SOME (subquery)
The subquery selects a single column and returns
any number of rows, including zero rows (an empty subquery).
The main query is then compared to the values of all the rows, using a
quantifier ALL, ANY or SOME.
■ ALL --
If the quantifier is ALL, the predicate is true if
the comparisons between the expression and each of the values are
all true. The predicate is also true if the subquery is empty.
■ ANY --
The predicate is true if the comparison between the
expression and at least one of the values is true. The predicate is
false if the subquery is empty.
A simple example here:
8 >= ALL (6,7,8) is true. (
8 = ALL (6,7,8) is false. (because not all are 8)
8 < ALL (6,7,8) is false (because 8 is more than 6 and 7)
8<> ALL (6,7,8) is false (because there is an 8)
Example 1:
To
query the table 'MaterialList' to retrieve all material records that
have qty on-hand less than 2 pieces in stock. [see diagram]
SELECT * INTO tbl_TempList01
FROM MaterialList
WHERE QtyOnHand <
ALL
(SELECT QtyOnHand FROM MaterialList
WHERE QtyOnHand =2);
Example 2:
To
create a table to store all retrieved data from main query that has
the Release date code in subquery (from table MaterialList), earlier
than 19991028. [see diagram].
You can download the predicates examples. [download
example]
SELECT * INTO tbl_TempList02 FROM MaterialList
WHERE Release_DateCode <
ALL
(SELECT Release_DateCode FROM CustomerList
WHERE Release_DateCode >19991028);
A simple example here:
8 = ANY (5,6,7,8) is true. (because there is an 8)
8<> ANY (5,6,7,8) is true. (because 8 is not = 5, 6 or 7)
8 > ANY (6,7,8) is true. (because
8 is greater than 6 or 7)
NOT (8 = ANY (5,6,7,8)) is false. (because NOT true =false)
Example:
To retrieve data from main query which matches the release_datecode of 20040827 from PO Number '4501409292' in the subquery. (subquery returned the datecode value 200040827). Hence it instructs database program to retrieve all records from table 'MaterialList' that has release_datecode greater than 20040827 [download example]
|
SELECT *
INTO
view_TempList03 FROM MaterialList WHERE Release_DateCode > ANY (SELECT Release_DateCode FROM CustomerList WHERE PO_Number ='4501409292'); |
SOME - same as ANY, and is used to retrieve records from the main query that match Any of the records in the subquery.
WHERE expression [NOT] EXISTS (subquery)
Example 1:
TOP
- Used to display records that fall at the top or bottom of a range that
is specified by the ORDER BY clause. The syntax is:
SELECT TOP number | percent column_name(s)
The EXISTS predicate instruct Access to retrieve the records in part
numbers, customer names and List prices from MaterialList that
satisfy the condition in the subquery WHERE clause, with Purchase
Order dates
placed after 12/31/2004.
[download example]
SELECT PartNumber, CustomerName, ListPrice
INTO view_TempList04
FROM CustomerList AS c
WHERE
EXISTS
(SELECT PartNumber FROM MaterialList AS m
WHERE m.PartNumber = c.PartNumber
AND c.PO_Date >#12/31/2004#);
Below INNER JOIN would produce the same result as using the above
EXISTS predicate. It's useful for you to see the
similarities between the EXISTS predicate and the join solution.
[download example]
SELECT DISTINCT c.PartNumber,
c.CustomerName, c.ListPrice, c.PO_Date
INTO view_TempList05
FROM CustomerList AS c
INNER JOIN
MaterialList AS m
ON c.PartNumber = m.PartNumber
WHERE (((c.PO_Date)>#12/31/2004#));
As an opposite to above EXISTS example, by putting NOT before EXISTS,
that means you instruct Access to retrieve part
numbers, customer names and List prices from MaterialList that
satisfy the condition in the subquery WHERE clause, to 'filter'
those customers who had not placed Purchase Orders on 12/31/2004 or
earlier.
[download example]
SELECT PartNumber, CustomerName, ListPrice
INTO view_TempList06
FROM CustomerList AS c
WHERE
NOT EXISTS
(SELECT PartNumber FROM MaterialList AS m
WHERE m.PartNumber = c.PartNumber
AND c.PO_Date >#12/31/2004#);
FROM table_name
ORDER BY column_name [ASC | DESC]
Example: The ORDER BY clause sorts the Release dates
in descending order, the TOP 5 retrieve the top five records of the
three wanted columns from the ORDER BY clause. [download example]
SELECT
TOP
5 PartNumber, Release_DateCode, QtyOnHand INTO
tbl_TempList07
FROM MaterialList
ORDER BY ReleasedDate DESC;
TOP PERCENT
Example: The ORDER BY clause sorts the List Prices in descending order, the
TOP 50 PERCENT retrieve the top 50% records of the part numbers and
names of customer from the ORDER BY clause.
[
download example]
|
SELECT
TOP
50
PERCENT
PartNumber, CustomerName INTO tbl_TempList08 FROM CustomerList ORDER BY ListPrice DESC; |
DISTINCT
- Used to display unique
values in a column. It eliminates duplicates only in the retrieved
fields. Must use with SELECT.
You can specify more than one column after Distinct. The syntax is:
SELECT DISTINCT column_name(s)
FROM table_name
Example: display the unique combination values of countries-of-make and factory plants. [download example]
SELECT DISTINCT
Country_Of_Make, Plant INTO tbl_TempList09
FROM MaterialList;
DISTINCTROW
SELECT DISTINCTROW column_name(s)
FROM table_name
INNER JOIN Orders ON Customers.CustID = Orders.OrdCustID
WHERE [optional
clause]
ORDER BY [optional
clause]
* * * A important note on DISTINCT and DISTINCTROW predicates * * *
Both results in unique records, but DISTINCT returns those records
that are unique for just the fields referenced.
DISTINCTROW returns all unique records for the underlying table and
includes all the other fields for uniqueness even if you did not
requested them. So if there are two records that are identical
except for non-selected fields,
DISTINCT will return one record and
DISTINCTROW will return two records.
Example: [
download ]|
SELECT DISTINCTROW
c.PartNumber, c.CustomerName INTO tbl_TempList10 FROM CustomerList AS c INNER JOIN MaterialList AS m ON c.PartNumber = m.PartNumber WHERE c.PO_Date > #6/30/2005# ORDER BY c.PartNumber; |
The subquery's syntax can take many forms as follow.
When you use any of the comparison operators, the subquery must
return a single value.
SELECT field1…, (subquery) AS alias FROM datasource
String Functions: Description:
Aggregate Functions: Description:
SELECT
SELECT
Date & Time Functions: Description:
Formatting codes for the Dates & Times data type.
Description:
SELECT
Variable Type Description:
SELECT
CBool([QtyOnHand]) AS _CBool,
Miscellaneous Functions: Description:
SELECT CurDir() AS _CurDir, The following is a list of general
SQL data types that may not be supported by all relational
databases. Data Type Syntax Explanation
SUBQUERY
- a subquery is a SELECT query within another SELECT,
SELECT INTO,
INSERT, UPDATE,
DELETE query, or another subquery,
enabling values to be passed among these linked queries. In other words, the subquery
returns a result set, which is then subject to the main query's
conditions and criteria.The results
of the embedded SELECT, also known as the
inner query
or
inner
select, become part of the search condition for the main query,
otherwise known as the
outer query
or
outer select.
SELECT fieldlist FROM datasource WHERE field comparison operator (subquery)
SELECT fieldlist FROM datasource WHERE field ANY|SOME|ALL (subquery)
SELECT fieldlist FROM datasource WHERE expression [NOT] EXISTS (subquery)
'
Example 1: criteria from main query is to have sales amount from
table1 greater than sales of $2000 from subselect.
SELECT
* FROM table_1
WHERE salesAmt >(SELECT
salesAmt FROM table_2 WHERE salesAmt <=2000)
'
Example 2: table1 will deletes its records only where part numbers
are matching, and where PO dates in table2 are prior to
12/31/2004 (subselect's criteria). The WHERE NOT EXISTS is
usually for linking to retrieve unmatched records.
DELETE
FROM table1
WHERE NOT EXISTS (SELECT
PartNumber FROM table2
WHERE table1.PartNumber = table2.PartNumber AND
table2.PO_Date >=#12/31/2004#);"
More examples
on subquery in another page.
Function:
Asc, AscB &
AscW(string)
The three functions process the first character of
the string and ignore the rest of them. Asc function returns an
Integer value that represents the ASCII code for the first character
in the string. AscB returns the first byte, and AscW returns
the first unicode character code except on platform that do not
support unicode, in which case, it is similar to Asc function.
Chr( ),
Chr$( )
The Chr( ) function returns the character associated with the specified character
code.
ChrB( ),
ChrB$(
)
Instead of returning a character, which may be one or
two bytes, ChrB() returns the ASCII value of a single byte.
ChrW( ),
ChrW$(
)
Returns a string containing the unicode character
code, except on platform that do not support unicode, in which case,
it is similar to Asc function.
Concatenate
Jet SQL allows concatenation to join
strings from different sources including column values,
literal strings, output from user defined functions. Use the
& (ampersand) or + (plus) operators. The
difference between the two operators is how
null strings are processed. The
+ operator processes a null string as a blank (eg.
string1+Null ="string1"). The & operator processes the
joining of any string with a null as null (eg. string1&Null
=Null).
Instr &
InstrB ([Start,
] SourceString,
SearchString[, compare])
Instr () returns the position of the first
occurrence of one string within another string. InStrB ()
returns the byte position of the first occurrence of one
string within another string.
Start is optional -
numeric expression that sets the starting position for each
search. If omitted, search begins at the first character
position. The start argument is required if compare is
specified.
SourceString is required
- string expression being searched.
SearchString is required
-string expression sought.
Compare is optional -
specifies the type of string comparison. If compare is
omitted (or 0), the Option Compare setting determines the type of
comparison. See here on
Instr's comparison settings and values.
InstrRev (stringcheck, stringmatch[, start[,
compare]])
Returns the position of an occurrence of one
string within another. The search begins from the end of
string, but the position returned counts from the beginning
of the string.
stringcheck is required - string expression being
searched.
stringmatch required - string expression being searched for.
start is optional -
numeric expression that sets the starting position for each
search. If omitted (or 0), the search begins at the last
character position by default (-1)
compare is optional - numeric value indicating the kind of
comparison to use when evaluating substrings. If omitted, a
binary comparison is performed.
See here on
InstrRev's comparison
settings and values.
Join(sourcearray[, delimiter])
Returns a string created by joining a number
of substrings contained in an array.
sourcearray is required,
which is one-dimensional array containing substrings to be
joined.
delimiter is optional,
which is the string character used to separate the
substrings in the returned string. If omitted, the space
character (" ") is used. If delimiter is a zero-length
string (""), all items in the list are concatenated with no
delimiters.
Len( ),
LenB(string
| varname)
Returns a Long containing the number of
characters in a string or the number of bytes required to
store a or the number of bytes required to store a
variable. Instead of returning the number of characters in a
string, LenB returns the number of bytes used to represent
that string.
varname is any valid
variable name. If varname contains Null, Null is returned.
If varname is a Variant, Len treats it the same as a String
and always returns the number of characters it contains.
Left & Left$ (String, n)
Returns the leftmost n characters of string.
LeftB &
LeftB$(String, n)
Use the LeftB function with byte data contained in a
string. Instead of specifying the number of characters to return,
length specifies the number of bytes.
LTrim( ),
LTrim$( )
Remove leading spaces from a string.
Mid & Mid$ (StringExpression, Start, Length)
Returns a string that is a part of another
string. Mid has a Variant return type and Mid$ has a
String return type.
string is required, is
string expression from which characters are returned. If string
contains Null, Null is returned.
start is required, which is
character position in string at which the part to be taken
begins. If start is greater than the number of characters in
string, Mid returns a zero-length string ("").
length is optional; Variant
(Long). It specifies number of characters to return. If omitted
or if there are fewer than length characters in the text
(including the character at start), all characters from the
start position to the end of the string are returned.
MidB &
MidB$
(StringExpression, Start, Length)
Use the MidB function with byte data
contained in a string, as in double-byte character set
languages. Instead of specifying the number of characters,
the arguments specify numbers of bytes.
Replace
(expression, find, replace[, start[,
count[, compare]]])
Returns a string in which a specified
substring has been replaced with another substring a
specified number of times.
expression is required
whichn contains substring to replace.
find is required, which
is substring being searched for.
replace is required,
which is the replacement substring.
start is optional, which
is the position within expression where substring search is
to begin.
count is optional, which
is the number of substring substitutions to perform. If
count is omitted, the default value is 1, which means make
all possible substitutions.
compare is optional,
which is Numeric value indicating the kind of comparison to
use when evaluating substrings.
Right &
Right(String, n)
Returns the rightmost n characters of string.
RightB &
RightB$(String, n)
Use the RightB function with byte data
contained in a string. The n length specifies the number of
bytes to return.
RTrim( ),
RTrim$( )
Remove trailing spaces from a string.
Space, Space$(number)
Returns a Variant (String) consisting of the
specified number of spaces. Useful for formatting output and
clearing data in fixed-length strings.
Split(expression[, delimiter[, limit[,
compare]]])
It is the counterpart of Join function. It
returns a zero-based, one-dimensional array containing a
specified number of substrings.
expression is required,
which is string expression containing substrings and
delimiters. If expression is a zero-length string(""), Split
returns an empty array (array with no elements and no data).
delimiter is optional,
which is string character used to identify substring limits.
If omitted, the space character (" ") is assumed to be the
delimiter. If delimiter is a zero-length string, a
single-element array containing the entire expression string
is returned.
limit is optional -
number of substrings to be returned. 1 means that all
substrings are returned.
compare is optional -
numeric value indicating the kind of comparison to use.
Str, Str$(number)
Converts a numeric value to a string. When numbers are
converted to strings,a leading space is reserved for the
sign of the number. The space is filled with a minus sign
for negative numbers.
StrComp(string1, string2[, compare])
StrComp function returns a value indicating
the result of two strings comparison.
string1 is required -
any valid string expression.
string2 is required -
any valid string expression.
compare is optional -
determines whether the comparison will be case-sensitive. If
compare is 0 (or omitted), the comparison is case-sensitive.
If it is 1, the comparison is case-insensitive.
See here
on StrComp's comparison settings and values.
StrConv(string,
conversion, LCID)
Returns a string variable converted as
specified by the conversion argument. To perform multiple
conversions, add the corresponding values. Most can be
combined.
string - required, as a
String expression to be converted.
conversion - required,
as Integer value(s) specifying the type of conversion to
perform.
LCID is optional. The
LocaleID, if different than the system LocaleID (the
default).
See here on
StrConv's conversion argument settings.
String(number, character )
Returns a Variant (String) containing a
repeating character string of the length specified.
Example:
String(3, '*') returns ***
StrReverse( )
Returns a string in which the character order
of a specified string is reversed. If string expression is a
zero-length string (""), a zero-length string is returned.
If expression is Null, an error occurs.
Trim( ),
Trim$( )
Remove leading and trailing spaces from a string.
UCase( ),
UCase$(
)
Returns a string in which all letters of
an argument have been converted to uppercase.
Val( )
Returns the numeric value of a string
made up of digits. The function starts reading the string
from the left and stops when it reaches a character that
isn't part of a number including spaces.
Function:
Avg( )
Return the average value of a numeric column.
Sum( )
Return the total of values stored in a column.
Count(*)
Count the rows in a table including Null values.
CountT (column_name)
Count the rows in a column excluding Null values.
First( )
Return the first value stored in a column.
Last( )
Return the last value stored in a column.
Min( )
Return the lowest value stored in a column.
Max( )
Return the highest value stored in a column.
String
Functions Examples:
CurDir() AS _CurDir,
Val([PartNumber]) As _Val,
RTrim([PartNumber]) AS _RTrim,
RTrim$([PartNumber]) AS '_RTrim$',
Trim([EndItem]) AS _Trim,
LeftB$([_Trim],2) AS '_LeftB$',
Right([_Trim],2) AS _Right,
UCase([_Trim]) AS _UCase,
UCase$([_Trim]) AS '_UCase$',
LCase([_Trim]) AS _LCase,
Len([_Trim]) AS _Len,
LenB([_Trim]) AS _LenB,
Mid([_Trim],2,4) AS _Mid,
InStr(3,[_Trim],'PR') AS _InStr,
InStrRev([PartDescription],'-') AS _InStrRev,
Trim(PartNumber)+String(4,'-')&[ReleaseDate] AS _String,
Trim([PartNumber])+Space(3)+[PartDescription] AS _Space,
Replace([_Trim], 'OJ', 'OJECT', 5, 2) AS _Replace,
LTrim([PartNumber])+' '+[PartDescription] As _concatenate,
Str([QtyOnHand]) AS _Str, Str$([PartID]) AS '_Str$',
StrReverse([PartNumber]) AS _StrReverse,
StrComp([StandardCost],[SalesPrice],) AS _StrComp,
StrConv([PartDescription],1,64) AS _StrConv,
Chr(83)&Chr(73)&Chr(78)&Chr(71)&Chr(80)&Chr(79)&Chr(82)&Chr(69) AS _Chr
INTO
tbl_Temp01
FROM
ComputedFields;
Aggregate Functions Examples:
Last([EndItem]) AS _LAST,
Max([StandardCost]) AS _MAX,
Min([StandardCost]) AS _MIN,
Sum([StandardCost]) AS _SUM,
Avg([StandardCost]) AS _AVG,
Count(*) AS tbl_rows_COUNT,
Count([StandardCost]) AS col_rows_Count
INTO tbl_Temp02
FROM ComputedFields;
Function:
Date( )
Returns current date.
DateAdd (interval,
number, date)
Returns a date that corresponds to a given
date plus some specified interval.
interval - required,
string expression which is the interval of time you want to
add.
number - required,
numeric expression that is the number of intervals. It can
be positive (to get dates in the future) or negative (to get
dates in the past).
date - required. Example, DateAdd("m", 1,
"31-Jan-2006"). The result is 2/28/2006.
DateDiff (interval, date1, date2[,
firstdayofweek[, firstweekofyear]])
This function is the counterpart of DateAdd.
DateDiff returns a Variant (Long) specifying the
number of time intervals between two specified dates.
interval - required, a
string expression that is the interval of time you use to
calculate the difference between date1 and date2.
date1, date2 are
required - the two dates you want to use in the calculation.
firstdayofweek -
optional, a constant that specifies the first day of the
week. If not specified, Sunday is assumed.
firstweekofyear - optional,
a constant that specifies the first week of the year. If not
specified, the first week is assumed to be the week in which
January 1 occurs.
If date1 refers to a later point in time than date2, the
DateDiff function returns a negative number. Example,
DateDiff("d","12/25/2005", "6/25/2006") gives you 182 days.
DatePart
(interval, date[firstdayof week] [, firstweekofyear])
To inspect a date and returns a specific
interval of time.
interval - required, represents a string expression that is
the interval of time you use to return.
date - required, the date value that
you wish to evaluate.
firstdayofweek
-
optional, argument that specifies the 1st day of the week. If not
specified, Sunday is assumed. firstweekofyear - optional,
argument that specifies the 1st week of the year. If not
specified, the first week is assumed to be the week in which Jan 1
occurs. DatePart("h", "1/1/2006 23:55:56") = 23.
DateSerial (year,
month, day)
Returns a Variant (Date) for a specified
year, month, and day.
Example: DateSerial(2006, 12, 27) gives you 12/27/2006.
DateValue (date)
Returns a Variant (Date).
Example: DateValue("6/25/2006") - DateValue("12/25/2002")
gives you 1278.
Day( )
Return a number from 1 to 31 indicating the day portion of a given date.
FileDateTime (
file_path )
FileDateTime function returns the date and
time of when a file was created or last modified.
Example:
FileDateTime (E:\xxx.xls)
Format &
Format$ (column_name, date_format)
Format a number, date, time, or string according to
instructions contained in a format expression.
MonthName (month[,
abbreviate])
Returns a string indicating the specified
month.
month is required - the
numeric designation of the month.
abbreviate is optional - a boolean value that
indicates if the month name is to be abbreviated.
By default, month names are not abbreviated.
Month( )
Returns a number from 1 to 12 indicating the month portion of a given date.
Year( )
Returns a number from 100 to 9999 indicating the year portion of a given date.
Now( )
Returns current date and time (equivalent to current_timestamp)
Hour( )
Returns an integer (between 0-23) representing the hour of the day.
Minute( )
Returns an integer (between 0-59) representing the minute of the hour.
Second( )
Returns an integer (between 0-59) representing the second of the minute.
Time( )
Returns current time (of day).
TimeSerial (hour, minute, second)
Return the time for a specific hour, minute, and second.
TimeValue
(time)
Returns a Variant of type time. The required
time argument is normally a string expression representing a
time from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59
P.M.)
WeekDay(date, [firstdayofweek])
Returns an integer 1 through 7 representing
the day of the week ( 1 for Sunday, 2 for Monday, and so
on). date is required.
firstdayofweek is
optional -a constant that specifies the first day of the
week. If not specified, vbSunday is assumed.
WeekdayName(weekday,
abbreviate, firstdayofweek)
Returns a string indicating the specified day
of the week.
weekday is required -
the numeric designation of the week.
abbreviate is optional - a boolean value that
indicates if the weekday name is to be abbreviated.
firstdayofweek is optional - a
numeric value indicating the first day of the week
** An important note about ' Time '.
Access stores all dates and times as numbers. Access
designates day 1 as 1/1/1900 and increments all other dates
starting from this date. (eg. 2/15/2007 is stores as 39128,
which is 39128 days after 1/1/1900). Negative numbers means
dates prior to 12/30/1899. Time is stored as a
fraction of a day. One hour =0.0416666 (or 1/24 of a day),
one minute =0.0006944 (or 1/1440 of a day), one second
=0.0000115 or the equivalent of 1/86400, and 6.00 a.m. is
stored as 0.25 (or 1/4 of a day).
Function:
:
Time separator for hours, minutes, and
seconds. Use the separator defined in your Windows regional settings.
/
Date separator. Use the separator defined in
your Windows regional settings.
c
Displays the general date format.
d or dd
Display the day as a number
with or without a leading zero.
ddd
Display the day as three text characters
(Sun, Mon, Tue, and so on).
dddd
Spell out the full name of the day (Sunday,
Monday, Tuesday, and so on).
ddddd
Use the short date setting from Windows
Control Panel to display the full date (default is m/d/y).
dddddd
Use the long date setting from Windows
Control Panel to display the full date (default is mmmm dd, yyyy).
w or ww
Display the day of the week as a number with or without a
leading zero.
.
m or mm
Display the month as a number
with or without a leading zero.
mmm
Display the month as three text characters
(Jan, Feb, Mar, and so on).
mmmm
Display
the full name of the month
(January, February, March, and so on).
q
Display the numeric quarter of the year. (1-4)
y
Display the year as a number. (1-366)
yy
Display the year as two digits.(00-99)
yyyy
Display the year as four digits. (0100-9999)
h or hh
Displays the hour as one or two digits.
n or nn
Displays minutes as one or two digits.
s or ss
Displays seconds as one or two digits.
tttt
Display the Long Time format specified in
the Windows Control Panel.
AM/PM, am/pm,
A/P or a/p
Displays the time using a 12-hour clock. Access relies on the system clock in your computer to set the value.
Blank space,
+ - $ ()
Use blank spaces, some math characters (+ -), and financial symbols ($ ?
?) as needed anywhere in your format strings. If you want to use other
common math symbols, such as slash (\ or /) and the asterisk (*), you
must surround them in double quotation marks.
Literal text
Surround any text that you want users to see in double quotes.
\
Forces Access to display the character that immediately follows. This is
the same as surrounding a character in double quotation marks.
*
When used, the character immediately after the asterisk becomes a fill
character — a character used to fill blank spaces. Access normally
displays text as left aligned and fills any area to the right of the
value with blank spaces. You can add fill characters anywhere in a
format string, and Access will fill any blank spaces with the specified
character.
Dates & Times
Function Examples:
Now() AS _Now,
Time() AS _Time,
Date() AS _Date,
DateAdd('d',10, ReleaseDate) AS _DateAdd,
DateDiff('d', ReleaseDate, Now()) AS _DateDiff,
DatePart('yyyy', ReleaseDate) AS _DatePart,
DateValue(ReleaseDate) As _DateValue,
FileDateTime('C:\Northwind.mdb') AS _FileDateTime,
Format(ReleaseDate,'yyyy-mmm. dd') AS _Format_date,
DateSerial (Year(ReleaseDate), Month(ReleaseDate),
Day(ReleaseDate)+30) AS _DateSerial,
Hour([ReleaseDate]-(Minute(Now())))+ Minute(#12:58:59
PM#) + Second(Time())) AS _HourMinSec
INTO tbl_Temp03
FROM
ComputedFields;
Function:
CBool
Converts the value of expression
to a Boolean data type.
CByte
Converts the value of expression to a Byte
data type.
CCur
Converts the value of expression to a Currency
data type.
CDate
Converts the value of expression to a Date
data type.
CDbl
Converts the value of expression to a Double
data type.
CInt
Converts the value of expression to an Integer
data type.
CLng
Converts the value of expression to a Long
data type.
CSng
Converts the value of expression to a Single
data type.
CStr
Converts the value of expression to a String
data type.
CVar
Converts the value of expression to a Variant
data type.
IsDate
Returns a Boolean value that indicates whether
expression is capable of being converted to a date value.
IsEmpty
Returns a Boolean value that indicates whether a
numeric or string expression has been initialized.
IsError
Returns a Boolean value that indicates whether a
given expression is an error value.
IsMissing
Returns a Boolean value that indicates whether an
optional Variant argument (argname) has been passed to a
procedure. IsMissing returns True if no value has been provided for
the specified argument.
IsNull
Returns a Boolean value that indicates whether a
given expression contains no data and is Null.
IsNumeric
Returns a Boolean value that indicates whether a
given expression can be evaluated as a numeric value.
Variable Type Functions Examples:
CByte([PartID]) AS _CByte,
CCur([StandardCost]) AS _CCur,
CDate([ReleaseDate]) AS _CDate,
CDbl([StandardCost]) AS _CDbl,
CInt([QtyOnHand]) AS _CInt,
CLng([PreviousQuarterSales]) AS _CLng,
CSng([StandardCost]) AS _CSng,
CStr(Trim([PartNumber])) AS
_CStr
CVar
IsDate([ReleaseDate]),
IsEmpty([StandardCost])
AS _IsEmpty,
IsError([StandardCost])
AS _IsError,
IsNull(StandardCost)
AS _IsNull,
IsNumeric(PartID)
AS _IsNumeric,
IsMissing([StandardCost])
AS _IsMissing,
PartID, PartNumber, ReleaseDate, StandardCost,
QtyOnHand
INTO
tbl_Temp04
FROM
ComputedFields;
Function:
Abs
(number)
Returns the absolute value of a
number. Example: Abs(-123.4) would return 123.4
Int
(expression)
Returns the integer portion of a number.
Example: Int(-123.6) would return -123
Fix
(number)
Both Fix and Int remove the fractional part of
number and return the resulting integer value. The difference
between Int and Fix is that if number is negative, Int
returns the first negative integer less than or equal to number,
whereas Fix returns the first negative integer greater than or equal
to number. For example, Int converts -8.4 to -9, and Fix
converts -8.4 to -8.
Round (expression, [
decimal_places])
Returns a number rounded to a specified number of
decimal places. In Access, if the expression that you are rounding
ends with a 5, the last digit after rounding is always an even
number.
For example: Round(6.75, 1) =6.8,
Round(6.85, 1) =6.8, and Round(6.95, 1) =7
Choose (position,
value1, value2, ... value_n)
Selects and returns a value from a list of values
based on a given position.
position is position number in
the list of values to return.
value1, value2, ... value_n is a
list of values.
Note:
If position is less than 1, the Choose function will return a null
value.
If position is greater than the number of values, the Choose
function will return a null value. If position is a fraction (not an
integer value), it will be rounded to the nearest whole number.
Example: Choose(2, " I love", "Access", "VBA") would return
"Access".
CurDir (drive)
Returns the current path. drive is an optional
parameter. If this parameter is omitted, the CurDir function assumes
the current drive. Example: CurDir () would return "C:\Documents and
Settings\user\My Documents" CurDir ("E") would return "E:\"
IIf (condition,
value_if_true, value_if_false)
This is the Immediate If. It returns one value if a
specified condition evaluates to TRUE, or another value if it
evaluates to FALSE. The performance of the IIF structure is somewhat
slow and not commonly used for processing very huge data.
Nz ( variant,[ value_if_null ])
In Access, the Nz function lets you return a value
when a variant is null.
variant is a variable that is a
variant datatype.
value_if_null is optional. It is
the value to use when the variant is a null value. If this parameter
is omitted and the variant is a null value, the Nz function will
return a zero or a zero-length string.
Example: Nz(varName, "n/a") would return value 'n/a' if the variable
varName contained a null value.
Switch (expression1, value1,
expression2, value2, ... expression_n, value_n)
Evaluates a list of expressions and returns the
corresponding value for the first expression in the list that is
TRUE.
expression1, expression2, expression_n
is a list of expressions that are evaluated. The function looks for
the first expression that evaluates to TRUE.
value1,
value2, ... value_n is a list of values.
Miscellaneous
Functions Examples:
Abs([StandardCost]) AS _Abs,
Int([StandardCost]) AS _Int,
Fix([StandardCost]) AS _Fix,
Round([StandardCost]) AS _Round,
NZ([StandardCost],'is Null') AS _Nz,
NZ(Choose([PartID],'A','B','C'),'D') AS _Nz_Choose,
Choose([PartID],'M','Y',' ','S','I','N','G','A','P','O','R','E')
AS _Choose,
Switch([PartDescription] Like ""%obot%"",'Robotics',[QtyOnHand]>0,'ShortShip')
AS _Switch,
IIf(IsDate([ReleaseDate] And [ReleaseDate]>#1/1/2005#),[SalesPrice]*1.05,[SalesPrice]*1.15)
AS _IIF
, *
integer
integer
smallint
smallint
numeric
numeric(p,s)
Where p is a precision
value; s is a scale value. For example, numeric(6,2)
is a number that has 4 digits before the decimal and 2 digits after
the decimal.
decimal
decimal(p,s)
Where p is a precision
value; s is a scale value.
real
real
Single-precision floating point
number
double precision
double precision
Double-precision floating point
number
float
float(p)
Where p is a precision
value.
character
char(x)
Where x is the number
of characters to store. This data type is space padded to fill the
number of characters specified.
character varying
varchar2(x)
Where x is the number
of characters to store. This data type does NOT space pad.
bit
bit(x)
Where x is the number
of bits to store.
bit varying
bit varying(x)
Where x is the number
of bits to store. The length can vary up to x.
date
date
Stores year, month, and day values.
time
time
Stores the hour, minute, and second
values.
timestamp
timestamp
Stores year, month, day, hour,
minute, and second values.
time with time zone
time with time zone
Exactly the same as time, but also
stores an offset from UTC of the time specified.
timestamp with time zone
timestamp with time zone
Exactly the same as timestamp, but
also stores an offset from UTC of the time specified.
year-month interval
Contains a year value, a month
value, or both.
day-time interval
Contains a day value, an hour value,
a minute value, and/or a second value.
