Crystal,
I REALLY appreciate you sending this info. You are right, this clears up a
lot of issues and jargon with SQL. I think you just have to work with this
stuff for a bit to start grasping how it all works together. I'm SLOWLY
getting there.... but at least I can feel progress none the less.
I have the database about where I want it. I have one more hurdle. I have
the Products with their product IDs (PID)... I have the Activities with their
activity IDs... I have the Kits with their IDs (KitID). The Kit is an
Activity and its ID with all the Products it takes to perform that Activity.
So KitID 1 may have Activity 24 which includes Products 4, 8, 10, and 22. I
also have the Books with thier book IDs (BID) and the Book Kits (BKitID).
Each Book Kit has an individual BKitID number and name and takes a Book ID
and seeds all the KitIDs and AIDs and Activity Names that go into making that
Book. So a BKitID will have with it a KitID and all its associated AIDs+PIDs
for each Activity included in the Book. All of this is now working great. I
can seed and lookup Activities and all the Products that make up the
individual Activities. I can also look up the Books and all the Activities
that make up that book.
Now the next step. To somehow get in a report....
If we look up a Book, it tells us the Activities that are included in that
book and all the Products that go into making up those Activities that make
up that Book. I think I'll have to try to get a query that will take the
BKitIDs and the KitIDs and bring that information together. If you have any
great ideas, let me know!
Anyway, I really appreciate all your help... I love the info on SQL!
Thanks again!
Kass
:
Hi Kass,
here you are!
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:
