Re: Populating a list -- SQL basic syntax, running SQL from code

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

"strive4peace" <"strive4peace2006 at yaho" wrote:

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:
View, SQL


First, get comfortable with SELECT statements. Once you
have them mastered, learn other forms.

~~~~~~~~ USING VBA to execute SQL statements ~~~~~~~~

You can execute action queries using VBA

'~~~~~~~~~~~~~~~~
dim strSQL as string

strSQL = "UPDATE tablename " _
& " SET fieldname = value " _
& " WHERE conditions;"

debug.print strSQL
currentdb.execute strSQL

'~~~~~~~~~~~~~~~~

** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug
window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:
Crystal,

I really appreciate your patience with this! I tried that link and again
got the message "The server could not be found" , that it couldn't download
the requested message and that the message was likely removed or expired from
the server.... also it included the following detail message:

Configuration:
Account: msnews.microsoft.com:119
Server: msnews.microsoft.com:119
Protocol: NNTP
Port: 119
Secure(SSL): 0
Code: 800ccc0d

I'm just pasting the link into an IE browser address line... is that the
correct method? I made sure I was signed in this time in case that was the
hold up.

Sorry for all the hassle. I'm definitely interested in the information if
you don't mind taking another stab at it.

Thanks!
.