how do i get a query to work?

  • Thread starter Thread starter Edward
  • Start date Start date
E

Edward

basically, MS office help and the walkthroughs I have seen
dont actually give me definitions on what a Query is
actually doing... in fact, they dont really define
anything to do with the product. They just throw a bunch
of examples up in the air and say "imitate." I dont learn
that way.

I need to know what a Query is, what the fields Im filling
up are actually telling the computer, and how it all
relates to the other features in MS Access. I have
created forms successfully, and relational databases. But
I cannot search them, filter them, any of that fancy stuff
that actually makes a database something more than a
glorified shopping list. If Im understanding correctly,
queries are how I do this.... but Im just not seeing it...
the relationships dont make any sense.


Can someone explain in concept terms, not via example and
imitate, how this works? Right now this is just seeming
like high school calculus all over again. The teachers
know what theyre talking about, but examples arent
teaching the fundamental concepts, just how to copycat
procedure.

Thanks to anyone who can assist.

-EA
 
I need to know what a Query is, what the fields Im filling
up are actually telling the computer, and how it all
relates to the other features in MS Access. I have
created forms successfully, and relational databases. But
I cannot search them, filter them, any of that fancy stuff
that actually makes a database something more than a
glorified shopping list. If Im understanding correctly,
queries are how I do this.... but Im just not seeing it...
the relationships dont make any sense.
Click to expand...

A Query is a standardized way to select one or more fields, from zero
or more records, from one or more Tables. It can do a lot of other
things as well (sorting, cross-tabulating, updating the table, etc.)
but let's start with a simple Select query.

A Query has several Clauses:

SELECT <some fields>
FROM <some table or tables>
WHERE <some criteria>
ORDER BY <some fields>

The SELECT clause is simply a list of the fields that you want to
include in the Query. They must all be fields that are part of the
table(s) in the FROM clause; you can also put in 'calculated fields'
by putting an expression like

SELECT [A] * / [C] AS Quotient

This will perform the calculation indicated and name the result
"Quotient".

The FROM clause lists the tables involved in the query and specifies
how they are joined. Tables are usually joined by linking the "Primary
Key" - the field, often but not necessarily an Autonumber, which
uniquely identifies a record in the "one" side table - to a Foreign
Key field in the "many" side table. For example,

FROM [Companies] INNER JOIN [Contacts]
ON [Companies].[CompanyID] = [Contacts].[CompanyID]

would join the Companies table (the one side table) to the Contacts
who work at that company, linking the two tables by their CompanyID
fields. The "INNER" join is just jargon - it means to only return data
from either table if there is a matching CompanyID in both tables.
OUTER joins will come in a later lesson.

The WHERE clause is an almost arbitrarily complex expression of
Boolean logic - it must evaluate to either TRUE or FALSE. If it's TRUE
for a given record, that record will be included in the query; if it's
FALSE, it won't. For a simple example, you could have a WHERE clause
just applying one criterion to one field:

WHERE [CompanyName] = "McDonald's"

or you could have a much more complex one applying criteria to
multiple fields, using the Boolean operators AND and OR to join them.
These are not the English language conjunctions! They're like
arithmetic operators such as + or -:

A AND B is true if A is true and B is also true
It is false if either A or B or both are false
A OR B is true if A is true; it is also true if B is true
It is false if neither A nor B is true

There are quite a few other operators - IN(<a list of values>),
BETWEEN <some value> AND <another value>, LIKE <some value containing
a wildcard character such as * or ? or #>, etc. etc. The WHERE clause
is actually optional - if you don't have any criteria, all records
will be returned.

The optional ORDER BY clause specifies how the records are to be
sorted; you can say ORDER BY <somefield> ASC to sort ascending, or
ORDER BY <somefield> DESC to sort in descending order. You can specify
multiple fields, the records will be sorted in left to right order.

The Access query grid is JUST A TOOL to build this kind of SQL string.
You can select the tables that you want to include by choosing them
form the list of tables and putting them in the upper part of the
query grid (thereby building the FROM clause); drag a field from one
table icon to another (the JOIN clause); select fields from the table
or tables into the grid (the SELECT clause); put conditions on the
Criteria line (the WHERE clause); and select Ascending or Descending
on the Sort line (the ORDER BY clause). Or, you can jump right into
the SQL window and start typing if you prefer. I'll do both - often
just throwing the needed tables and selecting the fields in the query
grid, then going into SQL to do more elaborate things.

If you wish to describe some specific query you'ld like to do and
explain what problems you're having with it (and if doing so doesn't
violate your learning style!) please feel free to do so.
 
Edward said:
basically, MS office help and the walkthroughs I have seen
dont actually give me definitions on what a Query is
actually doing... in fact, they dont really define
anything to do with the product. They just throw a bunch
of examples up in the air and say "imitate." I dont learn
that way.

I need to know what a Query is, what the fields Im filling
up are actually telling the computer, and how it all
relates to the other features in MS Access. I have
created forms successfully, and relational databases. But
I cannot search them, filter them, any of that fancy stuff
that actually makes a database something more than a
glorified shopping list. If Im understanding correctly,
queries are how I do this.... but Im just not seeing it...
the relationships dont make any sense.


Can someone explain in concept terms, not via example and
imitate, how this works? Right now this is just seeming
like high school calculus all over again. The teachers
know what theyre talking about, but examples arent
teaching the fundamental concepts, just how to copycat
procedure.

Thanks to anyone who can assist.

-EA
Click to expand...

I agree with everything John Vinson said, his descriptions are great.


<technical discussion of MS Access and JET follows, ignore as desired>


Yes, this will dicuss MS Access Queries, but will get to it in a
round-about way.

A "Relational" Database is made up of several features.

The "DBMS", or Database Management System. Or "RDBMS" for Relational
Database Management System. MS Access and JET (mostly JET) are one such
product. More on this later.

The "Table" (technically known as a "Relation", and is the reason
Relational DBs are called what they're called).

Tables have "Rows" (technically known as a "Tuple", though not many refer
to it that way).

Tables also have "Columns" (although, if you listen to Mr. C.J. Date, it's
the Row that has Columns, but never mind).

Tables, Rows, Columns.

Every Column represents a place to keep a piece of data (whatever kind of
data the database designer wanted there). According to best practices in
database design, each Column should contain only a single piece of data that
cannot be broken down into sub-pieces of any kind.

A Row contains many Columns. The data found in one or more of those
Columns, together, uniquely identify the Row, and this Column or combination
of Columns is called the Primary Key (although virtually every RDBMS product
allows Tables to be created with no Primary Key at all).

Now that we have our Tables and their contents, what do we do with them?

We can ask the Database Manager (a piece of software) "questions" about
the data in the Tables. We do that with "SQL", a specialized computer
language. This is a key aspect of RDBMSes: The use of SQL to ask questions
about data in Tables through the Database Manager. The Database Manager
(JET, in the case of MS Access) rules the roost. Every question posed for
the database (a written SQL statement) is passed to the Database Manager.
It looks at the question, and decides the best way to get at the data stored
in the Database's Tables (a bunch of very smart people thought up lots of
cool and efficient ways to get at the data, and designed the Database
Manager to be able to figure out which of those ways were best to use to
solve any question posed with SQL). The Database Manager uses the
SQL/Question to go out to the data in the Tables, grab that data, and come
back with it and put it in a format also specified by the SQL/Question for
human viewing.

SQL is a type of computer language. The core of the language is commonly
used in all RDBMS products (SELECT, UPDATE, INSERT, DELETE, etc.), although
virtually every vendor has proprietary "extensions" built into it; MS
Access's "Crosstab--TRANSFORM/PIVOT" feature, is one such.
SQL is called a non-procedural language, because in the purest sense you
cannot write a series of instructions with decision branches and loops
(hallmark features of most computer languages) using it. Each SQL statement
tends to be all or nothing. Most RDBMS products have proprietary extensions
that allow SQL to be used in a procedural way (sort of); VBA for MS Access,
Transact-SQL for MS SQL Sever, etc.

In the SQL/RDBMS world, the SELECT statement of SQL is quite commonly
known as a "query", because it asks a question. "Query" has even become
synonomous with any SQL Statement, even those that don't ask questions
(UPDATE, CREATE TABLE, etc.).

Now, we finally get around to the MS Access Query. It is a little named
software "object" inside MS Access. It "wrappers" any SQL statment that JET
is capable of executing. The QBE (Query By Expression Grid) is a front-end
and user-friendly GUI-way of avoiding the need for knowledge of SQL. You
pick Column names and write Criteria, and when done, MS Access figures out
how to turn all that GUI-stuff into an SQL statment. Yup, all that just to
create a piece of SQL computer code. When you run the "Query", it is
actually taking the SQL statement inside the Query (viewable through SQL
View) and passing it to the JET Database Manager software. JET figures out
how to get the data, and then tosses it back into a GUI datasheet for the
user to view on the computer screen (or a Report calls on a Query and the
info is either displayed on-screen or is printed (but with lots of nice
formatting options not available to a simple computer-screen data-sheet)).
Most other RDBMS products call this sort of arrangement a "VIEW". SQL
even has a "CREATE VIEW" statement, but this is not implemented in JET, and
so we have the MS Access Query set-up that we do.

It is totally possible to avoid using the QBE Grid. Personally, I switch
to SQL view and write straight SQL for almost all cases (this is because I
learned to write SQL on other products, and don't get along with the QBE
Grid too well).


Sincerely,

Chris O.
 
1. Edit---Select All---Copy
2. Open Notepad---Edit---Paste
3. File---Save As---"Best query explanation ever" in personal Access folder

Fabulous explanation John, serious.

Jeff Conrad
Bend, Oregon

John Vinson said:
I need to know what a Query is, what the fields Im filling
up are actually telling the computer, and how it all
relates to the other features in MS Access. I have
created forms successfully, and relational databases. But
I cannot search them, filter them, any of that fancy stuff
that actually makes a database something more than a
glorified shopping list. If Im understanding correctly,
queries are how I do this.... but Im just not seeing it...
the relationships dont make any sense.
Click to expand...

A Query is a standardized way to select one or more fields, from zero
or more records, from one or more Tables. It can do a lot of other
things as well (sorting, cross-tabulating, updating the table, etc.)
but let's start with a simple Select query.

A Query has several Clauses:

SELECT <some fields>
FROM <some table or tables>
WHERE <some criteria>
ORDER BY <some fields>

The SELECT clause is simply a list of the fields that you want to
include in the Query. They must all be fields that are part of the
table(s) in the FROM clause; you can also put in 'calculated fields'
by putting an expression like

SELECT [A] * / [C] AS Quotient

This will perform the calculation indicated and name the result
"Quotient".

The FROM clause lists the tables involved in the query and specifies
how they are joined. Tables are usually joined by linking the "Primary
Key" - the field, often but not necessarily an Autonumber, which
uniquely identifies a record in the "one" side table - to a Foreign
Key field in the "many" side table. For example,

FROM [Companies] INNER JOIN [Contacts]
ON [Companies].[CompanyID] = [Contacts].[CompanyID]

would join the Companies table (the one side table) to the Contacts
who work at that company, linking the two tables by their CompanyID
fields. The "INNER" join is just jargon - it means to only return data
from either table if there is a matching CompanyID in both tables.
OUTER joins will come in a later lesson.

The WHERE clause is an almost arbitrarily complex expression of
Boolean logic - it must evaluate to either TRUE or FALSE. If it's TRUE
for a given record, that record will be included in the query; if it's
FALSE, it won't. For a simple example, you could have a WHERE clause
just applying one criterion to one field:

WHERE [CompanyName] = "McDonald's"

or you could have a much more complex one applying criteria to
multiple fields, using the Boolean operators AND and OR to join them.
These are not the English language conjunctions! They're like
arithmetic operators such as + or -:

A AND B is true if A is true and B is also true
It is false if either A or B or both are false
A OR B is true if A is true; it is also true if B is true
It is false if neither A nor B is true

There are quite a few other operators - IN(<a list of values>),
BETWEEN <some value> AND <another value>, LIKE <some value containing
a wildcard character such as * or ? or #>, etc. etc. The WHERE clause
is actually optional - if you don't have any criteria, all records
will be returned.

The optional ORDER BY clause specifies how the records are to be
sorted; you can say ORDER BY <somefield> ASC to sort ascending, or
ORDER BY <somefield> DESC to sort in descending order. You can specify
multiple fields, the records will be sorted in left to right order.

The Access query grid is JUST A TOOL to build this kind of SQL string.
You can select the tables that you want to include by choosing them
form the list of tables and putting them in the upper part of the
query grid (thereby building the FROM clause); drag a field from one
table icon to another (the JOIN clause); select fields from the table
or tables into the grid (the SELECT clause); put conditions on the
Criteria line (the WHERE clause); and select Ascending or Descending
on the Sort line (the ORDER BY clause). Or, you can jump right into
the SQL window and start typing if you prefer. I'll do both - often
just throwing the needed tables and selecting the fields in the query
grid, then going into SQL to do more elaborate things.

If you wish to describe some specific query you'ld like to do and
explain what problems you're having with it (and if doing so doesn't
violate your learning style!) please feel free to do so.
Click to expand...
 
My suggestion is to learn by doing. Work your way from simple to more
complicated examples. The "design view" and "SQL view" windows operate in
both directions. The QBE "Design view" window will generate a SQL string
displayed in "SQL view". If you type a valid SQL string into the SQL View,
you will see an equivalent result in Design View (QBE window). Exceptions
are union and pass-through queries (SQL View only).

Queries can also be chained. One query can be built upon anothery query.
Queries are preferable to sequential record processing, although it is
possible to accomplish similar results by writing VBA code or SQL queries.
Both capabilities exist in Access. There are multiple ways to do things.
You will have to experiment in order to determine which "style" works best
for you.

An encyclopedic reference manual is available. The online help within
Access is very comprehensive and contains lots of code examples.

I've been able to do anything I wanted to do with Access 97 and later
versions without limitations other than volume of data and number of
concurrent users. It's ideal for a workgroup of up to ten users. My $100
investment has been repaid at least 3,000 times.

To learn, you're gonna have to work at it. There isn't any substitute for
actual experience.
 
1. Edit---Select All---Copy
2. Open Notepad---Edit---Paste
3. File---Save As---"Best query explanation ever" in personal Access folder

Fabulous explanation John, serious.
Click to expand...

<right mouseclick>
<select Copy to Folder>
<select Kudos>

Thanks Jeff. Worth the effort of typing it just for that!
 
<right mouseclick>
<select Copy to Folder>
<select Kudos>

Thanks Jeff. Worth the effort of typing it just for that!
Click to expand...

<vbg>

Don't think for a minute I didn't actually save that!!

Jeff Conrad
Bend, Oregon
 
Queries and Relationships were/are a mystery to me, also.
Here's what I know, hope it helps...WHAT IS A QUERY?
Your database is like a filing cabinet that holds all of
your data. Once you create the tables and populate the
fields w/info, you then have to tell Access "how" to bring
that information together. This is done by creating
RELATIONSHIPS between the tables. The RELATIONSHIPS you
create enable you to QUERY your database so that useful
information may be retrieved. QUERIES enable you to answer
specific questions about the information you have in your
database.
 
Back
Top