Thanks, Pete
you are welcome
Don't let the SQL acronym intimidate you, it is not tough to
learn the basics.
~~~~~~~~~~~ SQL background ~~~~~~~~~~~
SQL is Structured Query Language
It is a statement to get data out of one or more
tables/queries. This is what Access stores for:
1. Queries (just shows the QBE grid for convenience --
choose View, SQL)
2. RowSource for Comboboxes and Listboxes (if list does not
come directly from a table since a query is actually an SQL
statement)
3. RecordSource for Reports and Forms (if list does not come
directly from a table)
Queries can be different types, for instance:
1. Select
2. Crosstab
3. Make-Table
4. Update
5. Append
6. Delete
Select and Crosstab queries show data while the others are
"action" queries" and do not display data -- so you cannot
use them for record sources or for row sources.
To expand and reiterate:
Queries are a very powerful tool in Access -- you can do far
more than simply use them to select data
.... Queries can be ACTION Queries...they DO things, not show
you things
they can add data to a table --> APPEND Query
they can make changes to a table --> UPDATE Query
they can delete records from a table --> Delete Query
they can make tables --> Make-Table Query
they can transpose data --> Crosstab Query (which is also,
actually, SELECTing data from a table since there is not action)
Internally, Access stores an SQL (Structured Query Language)
statement for queries*, not the visual representation you
see when you design queries -- the QBE (Query-By-Example)
grid is for your benefit, so you can use drag-n-drop and
pick methods and visually look at things better.
*this is why it is so important to get comfortable with
lokking at SQL statements
A great way to become more familar with SQL is to LOOK at
the SQL everytime you make a query. Until you actually start
to look, you never realize how easy and sensible it actually
is... and It really helps to use Aliases (short
abbreviations) for tablenames as it makes the SQL statement
shorter.
~~~~~~~~~~~ SELECT ~~~~~~~~~~~
BASIC SQL SYNTAX
SELECT fieldlist
FROM tablename
IN anotherdatabase.mdb
WHERE conditions
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;
~~~~~~~~~~~ APPEND ~~~~~~~~~~~
An Append Query is a select query preceeded by
INSERT INTO Tablename (field1, field2, etc )
~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used
UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield
Then identifies what to change
SET table1.fieldtochange = expression
Then, if you have criteria...
WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#
~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;
~~~~~~~~~~~ DELETE ~~~~~~~~~~~
DELETE A.*
FROM tblArticles AS A
WHERE (A.somefield=2);
~~~~~~~~~~~ CROSSTAB ~~~~~~~~~~~
TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname3
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3
PIVOT By B.Fieldname1;
you can use equations to pivot (this will be column
headings). For instance, if you want the column headings to
be year and month, you can do this:
PIVOT Format([DateField],'yy-mm');
If you also want a column that totals whatever you have in
all the VALUE columns (the expression after TRANSFORM),
repeat your transform expression after the SELECT keyword
and the GROUPBY keywords
for instance:
TRANSFORM Count(B.Fieldname1) AS FieldAlias
SELECT
A.Fieldname2,
A.Fieldname,
Count(B.Fieldname1) AS FieldAlias2
FROM Table2 AS B
INNER JOIN Table1 AS A
ON B.someID = A.someID
GROUP BY
A.Fieldname2,
A.Fieldname3,
Count(B.Fieldname1)
PIVOT By B.Fieldname1;
~~~~~~~~ ALIASES ~~~~~~~~
Using Aliases for tablenames (/querynames) makes the SQL
easier to read.
For calculated fields, it is best to assign your own field
alias instead of letting Access use "expr1", "expr2", etc.
Calculated fields MUST have aliases.
an Alias follows the keyword AS
The SQL statement can be selected, copied, then pasted into
Word for formatting and printing (makes great wallpaper for
your wall, not your computer
as you are learning) or into
Notepad to have as a popup reference while you are working
into design view, etc.
~~~~~~~~ JOINs ~~~~~~~~
When you are getting information from more than one table,
the default join type is INNER JOIN. This means that only
records in common will be displayed. For instance, if you
have a table of Companies and a table of Jobs and not every
Company has done a job, but you want to see ALL the
companies anyway, you would use a Left Join or a Right Join
for the type of relationship between tables.
FROM Companies AS c
LEFT JOIN Jobs AS J ON c.CompID = J.CompID
The join is specified as a LEFT JOIN because ...
ON c.CompID = J.CompID --> C is on the LEFT side of the
equal sign.
~~~~~~~~ PARAMETERS ~~~~~~~~
If you specify data type in Query, Parameters, the SQL
statement is preceeded by (for example):
PARAMETERS [enter category] Text ( 50 );
while the criteria may be:
WHERE (B.Category=[enter category])
~~~~~~~~ LOOK at SQL ~~~~~~~~
Whenever you create a query, LOOK at the SQL statement and
study it for a couple minutes -- it makes sense! Within a
short period, the confusion will be gone...
from the menu, choose:
View, SQL
First, get comfortable with SELECT statements. Once you
have them mastered, learn other forms.
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
