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

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!

Kass

"strive4peace" <"strive4peace2006 at yaho" wrote:


Hi Kass,

I am still new to copying links... those posts may be more detailed than you want to get right now anyway, don't worry about it.

Here is a link on SQL basics. If the link doesn't work, let me know and I'll paste the information in here

news://msnews.microsoft.com:119/uQ4OpVogGHA.3652@xxxxxxxxxxxxxxxxxxxx


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:

Crystal,

Thanks for your quick responce. I want to look at the other form suggestions you listed at the top of the page, but I couldn't get the links to work. There was an error message indicating that server 119 is no longer available or something like that. Can you advise...perhaps I am just not accessing this correctly.

I think your multi-select list box may be what I'm looking for, however I'm going to have to do some studying. I'm not well versed in SQL. I may have to work on this and get back to you. In the mean time, if you know how I can get to the info you sent, I'm very interested in learning all I can. I really want this project to work for the people I'm building this database for... it will save them an enormous amount of time and redundancy if I can just figure this out!

I need a bigger brain! :-)

Thanks for your help!

Kass

"strive4peace" <"strive4peace2006 at yaho" wrote:



Hi Kass,

look here for ideas on other things you can do with your form:

news://msnews.microsoft.com:119/1149538886.072984.248900@xxxxxxxxxxxxxxxxxxxxxxxxxxxx

news://msnews.microsoft.com:119/uMnyDySiGHA.3884@xxxxxxxxxxxxxxxxxxxx

news://msnews.microsoft.com:119/Odvb5qYiGHA.3780@xxxxxxxxxxxxxxxxxxxx

for choosing multiple activities, consider using a multi-select listbox , then you can loop thru the listbox selected items and construct a WHERE clause for an SQL statement that you can write to show all kit parts

here is some code for constructing a the WHERE clause from a multi-select listbox for an SQL statement

'~~~~~~~~~~~~~~~~~~~~~
Dim varItem As Variant
Dim mWhere As String

mWhere = ""
For Each varItem In me.listbox_controlname.ItemsSelected
'delete the line that doesn't apply
'for text
mWhere = mWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "
'for numbers
mWhere = mWhere _
& me.listbox_controlname.ItemData(varItem) & ", "
Next varItem

if len(mWhere ) > 0 then
mWhere = "[Field_Name] IN (" & mWhere

'remove comma from the end and add a parenthesis
mWhere = left(mWhere ,len(mWhere )-2)) & ")"

end if

'then, if you have other criteria, you can put code here

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

you can use the same code to construct a string to use for the WHERE parameter of an OpenReport action

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kass wrote:


I need to be able to populate a list using choices from another list for a science kit database I have.

I have one form called "Activities" with a subform call Kit Parts. I choose

from a list of Activities, and then populate the subform with all the Kit

Parts needed to perform that Activity. I have many Activities and their corresponding Kit Parts housed in a Kit Detail Table. This all works just fine!

Now what I need, is to be able to create a form and a corresponding report where I can choose and add several different Activities into a subform that together make a Kit. The form and its corresponding report will show a comprehensive list of all the Kit Parts needed for all those Activities that make up the Kit.

I know how to make a form where you choose from a list the Activity and it shows the Kit Parts needed for that one Activity. But, I don't seem to be able to take this to the "3D level" so to speak, where I choose MULTIPLE Activities and the form (and eventually a report) will automatically show ALL the Kit Parts needed for those chosen Activities.

Can anyone help me think through this barrier?

Thanks for your help!

Kass

.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Multi-Select ListBox
    ... the selected values are then used as query criteria. ... > ' Remove the leading comma from the string ... > ' Build the new SQL statement incorporating the string ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... this clears up a lot of issues and jargon with SQL. ... I think I'll have to try to get a query that will take the BKitIDs and the KitIDs and bring that information together. ... Queries (just shows the QBE grid for convenience -- ... and It really helps to use Aliases for tablenames as it makes the SQL statement shorter. ...
    (microsoft.public.access.forms)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)