Just cant get my head around queries

[Craig Armitage]

I dont know why but queries just really confuse me.. Im probably trying to
run before i can walk but i always find that having something that needs
doing helps me understand things better..

This is what im trying to do..

I have a Jobs table and a Customers table. In the jobs table i have a link
back to the customer table using a field called Job_CustomerID. I also
have a list box on a blank form thats going to be a Jobs browser. The idea
being that i can browse down the list and double click on a job I would like
to view...
I would like to populate the list box with the JobID (Primary Key) and the
Customers Name.

This means that i need to fill it with all the jobs in the jobs database but
i need to somehow fill the listbox with data from the Customers Table.

Can someone help me with this please... also, does anyone know a good site
that explains queries in simple terms.


Thanks again guys.

 

[Guest]

Lets start with a few generalities and then look at your specific problem:

A query is just a way of retrieving data from one or more tables (I'm
ignoring what Access calls 'action queries' for the moment as these are for
updating (including adding or deleting rows) the data rather than retrieving
it). There are various ways you can control the data retrieved:

1. The rows returned can be 'restricted' so you only get those which match
certain criteria. In design view this is done via the 'criteria' row of each
column, in SQL view by the query's WHERE clause.

2. The data can be 'projected' over certain columns so only some of the
columns from the underlying tables are returned. In design view you simply
put those columns you want returned in the columns of the design grid; in SQL
view you list them in the SELECT clause.

3. The order in which the rows are returned can be controlled. In design
view the 'sort' row of the relevant columns is used. The sort can be
Ascending or Descending and works from left to right in the design grid. In
SQL the ORDER BY clause is used.

Queries which draw upon two or more tables JOIN the tables to each other on
columns from each. These are normally the primary key column of the
'referenced' (one side) table and the corresponding foreign key column of the
'referencing (many-side) table in a one-to-many relationship. In design view
you drag from the column of one table to the relevant column of another to
create the JOIN. In SQL view its usually done through the JOIN clause, but
can also be done by 'join criteria' in the WHERE clause. Prior to the SQL 92
standard this was the only way in fact.

Joins can be INNER JOINS where only rows with a match in both tables are
returned, or OUTER JOINS where a row from one side of a JOIN is returned even
if there is no match on the other side. OTER JOINs can be a LEFT OUTER JOIN
or a RIGHT OUTER JOIN which governs which table returns the unmatched rows.
For instance to return all customers even if some had no Jobs record you'd
use a LEFT OUTER JOIN when joining Customers to Jobs. Outer joins are
created in design view by selecting the relevant option in the Join
Properties dialogue. In SQL you simply type in LEFT JOIN or RIGHT JOIN as
appropriate (The OUTER is optional).

As well as straightforward queries which return rows from the tables, you
can use what Access calls 'Totals' queries. These are aggregating queries
where you can get the sum, average, max, min values or the count of rows over
a group of records. You could count the number of jobs per customer for
instance. In design view you select Totals from the View menu and then
select Group By in the Totals row for the column(s) on which you wish to
group the results, and the relevant aggregation operator, count, sum etc, for
the column(s) whose aggregated value over the grouped data you want to
return. In SQL you use a GROUP BY clause and the relevant aggregation
operator(s) followed by the column name, e.g SUM(NetPrice). For counting
rows you can use COUNT(*).

That's probably enough of the basics fo the moment. Lets look at your
specific problem:

You could use a single list box for this with a RowSource query which joins
the two tables like so:

SELECT JobID, CustomerName
FROM Jobs INNER JOIN Cistomers
ON Jobs.Job_CustomerID = Customers.CustomerID
ORDER BY CustomerName, JobID;

BTW its better to use the same names for the keys, so calling the foreign
key column in Jobs CustomerID so it matches the name of the primary key
column of Customer's would be better.

By setting the list box's ColumnCount property to 2 and its ColumnWidths
property as appropriate e.g. 1.5cm;3cm, you'll see both columns listed with
the list in customer name order and then job ID order per customer.
Provided you've left the BoundColumn property as 1 the value of the list box
will be the selected JobID so to open the form the code for the list box's
DblClick event procedure would be:

DoCmd.OpenForm "YourForm", WhereCondtion:= "JobID = " & Me.lstJobs

where lstJobs is the name of the list box.

However, the list box will probably have quite a lengthy list, so I'd
suggest using two list boxes (or combo boxes if you prefer), the first to
list just the customers with:

SELECT CutomerID, CustomerName
FROM Customers
ORDER BY CustomerName;

The second which lists just the jobs for the customer you've selected in the
first list box by using a RowSource which references the first list box:

SELECT JobID
FROM Jobs
WHERE Job_CustomerID = Forms!frmJobsDlg!lstCustomers;

where frmJobsDlg is the name of the form and lstCustomers is the first list
boxes name. When you select a customer from the first list box the second
one won't update automatically. To get it to do so you requery it with the
following in the first list box's AfterUpdate event procedure:

Me.lstJobs.Requery

You can then open the form from the second list box's DblClick event
procedure with the same code as for the single list box.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Well done, Ken. Very good overview!

 

[Guest]

Thank you, Doug.

Well done, Ken. Very good overview!

 

[Joseph Meehan]

Lets start with a few generalities and then look at your specific
problem:

A query is just a way of retrieving data from one or more tables ...

Very good overall and understandable. However I would suggest changing
the word "row" to "record." People new to database have a real problem
getting their heads around the idea of records not rows of database not
spreadsheet.

 

[Guest]

Hi Ken,

I concur with Doug's opinion.

I only have one tiny little itsy-bitsy disagreement. That is in this
statement:

BTW its better to use the same names for the keys, so calling the foreign
key column in Jobs CustomerID so it matches the name of the primary key
column of Customer's would be better.

My personal preference is to use unique field names throughout a database. I
prefix my primary and foreign keys with lowercase "pk" and "fk", as in
pkCustomerID and fkCustomerID. This offers several advantages in my opinion:

1.) When you are looking at VBA code that references key columns, you will
know immediately if you are dealing with a primary or foreign key, just by
looking at it's name.

2.) In Tools > Relationships view, if you have any fields named with the
foreign key prefix, they should likely be involved in a relationship. It is
easy to scan a relationships view to make sure that you've not left out any
relationships. Otherwise, this may indicate a left over field from a changed
design that can be deleted.

3.) Combo boxes used to assist in data entry, by selecting a value from a
list, *must* have the foreign key as the Control Source. If both primary and
foreign keys have the same name, it's not so easy to tell which field is set
as the control source. Try accidently setting a primary key as the control
source. (I had to troubleshoot more than one student's database in the past
with this exact problem).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Rick Brandt]

Very good overall and understandable. However I would suggest
changing the word "row" to "record." People new to database have a
real problem getting their heads around the idea of records not rows
of database not spreadsheet.

The relational theory guys would say the exact opposite. Fields and Records is
incorrect while Rows and Columns is correct.

 

[Joseph Meehan]

The relational theory guys would say the exact opposite. Fields and
Records is incorrect while Rows and Columns is correct.

I would not argue with that. I don't believe that anyone with a good
knowledge of the subject would have any problems understanding the use of
either pair of words. I was only suggesting that for many of those who post
questions here, the use of records and fields helps them get the idea of the
difference between database and spreadsheet.

 

[Jamie Collins]

I don't believe that anyone with a good
knowledge of the subject would have any problems understanding the use of
either pair of words.

I would not argue that but I would advise against correcting someone's
usage, especially from row/column (the terms of choice in the wider SQL
world, especially the ANSI Standards and the literature) to
record/field (the terms of choice in the Access world).

I was only suggesting that for many of those who post
questions here, the use of records and fields helps them get the idea of the
difference between database and spreadsheet.

And for many, the records/fields terms imply a file system, so using
the rows/column terms help get them into a SQL mindset.

Jamie.

--

 

[Guest]

Joseph:

This is getting quite like the old days in the CompuServe forums (of blessèd
memory), when Joe Celko used to vehemently correct everyone in sight over the
use of these terms!

I'm sticking to my guns on this one. As Jamie has correctly pointed out the
terms 'record' and 'field' apply to file systems, not tables. Tables have
rows and columns (though Joe Celko points out in one of his books that its
actually rows which have columns, not tables, though I'm not too sure about
that myself; I'd favour rows having ' column positions', as used by Chris
Date).

Tables differ fundamentally from files (in the database sense), principally
in that data in the latter can be identified by physical position, whereas in
the former only by name.

I'm not as dogmatic about this as Joe. If someone wants to use 'field' and
'record' that's up to them, but I like them to know what I'm referring to
when I use 'row' and 'column'.

Ken Sheridan
Stafford England

 

[Guest]

Tom:

I don't like 'tags' myself for table or column names, though I do use then
for control names or VBA variables. I find they interfere with the semantics
when writing/reading SQL. I try follow the convention of using plural or
collective nouns for table names and singular nouns for column headings,
keeping them as close to plain English as possible. It makes writing complex
SQL a whole lot easier I find.

I once received a database to work on where the original developer had used
a naming convention scheme involving tags of Byzantine proportions for column
names, and it was a complete nightmare trying to understand what his SQL
statements were supposed to do.

I don't follow the convention of using proper case for table names and lower
case for column names. For the latter I use CamelCase (not camelCase). One
thing I really hate is when people use upper case for table and column names.
It makes it so difficult to pick out the table and field names from the
(upper case) keywords in SQL.

Ken Sheridan
Stafford England

 

[Guest]

Hi Ken,

I also use the plural form for table names, and the singular form for field
names. I believe one will find this advice in the book SQL Queries for Mere
Mortals, among other places.

I once received a database to work on where the original developer had used
a naming convention scheme involving tags of Byzantine proportions for column
names, and it was a complete nightmare trying to understand what his SQL
statements were supposed to do.

Been there....Speed Ferret to the rescue!

I also use camel case for my table and field names, with standard naming
convention tags (for example, "tbl") in lowercase. For SQL statements in VBA
code, I capitalize the SQL keywords such as SELECT, FROM, INNER, OUTER, JOIN,
ORDER BY, etc. but field and table names are shown in their camel case form.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

My personal preference is to use unique field names throughout a
database. I prefix my primary and foreign keys with lowercase "pk"
and "fk", as in pkCustomerID and fkCustomerID. This offers several
advantages in my opinion:

I disagree completely. In my opinion, fields that contain the same
data should have the same name. Since the FK data is a duplicate of
data from the parent table and does not actually belong to the child
table, it should have the same name in the child table as it has in
the parent table.

3.) Combo boxes used to assist in data entry, by selecting a value
from a list, *must* have the foreign key as the Control Source. If
both primary and foreign keys have the same name, it's not so easy
to tell which field is set as the control source. Try accidently
setting a primary key as the control source. (I had to
troubleshoot more than one student's database in the past with
this exact problem).

Huh? Under what circumstances would your form's recordsource include
both the PK and the FK? That makes absolutely no sense.

If you've got both your PK and FK in a SQL statement, then you're
doing something wrong.

 

[David W. Fenton]

I'm not as dogmatic about this as Joe. If someone wants to use
'field' and 'record' that's up to them, but I like them to know
what I'm referring to when I use 'row' and 'column'.

If you're really going to be Celko-style dogmatic, you should be
talking about relations and tuples and so forth.

 

[Guest]

Hi David,

I disagree completely.

You're entitled to your opinion. I find prefixing my primary and foreign key
names helps keep things straight in my mind. For example, if you see the
ShipVia field in VBA code (see Northwind), would you know without looking
that it refers to a foreign key field?

Huh? Under what circumstances would your form's recordsource include
both the PK and the FK? That makes absolutely no sense.

A student's database, for one!! <smile>


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Jamie Collins]

This is getting quite like the old days in the CompuServe forums (of blessèd
memory), when Joe Celko used to vehemently correct everyone in sight overthe
use of these terms!

I'm sticking to my guns on this one. As Jamie has correctly pointed out the
terms 'record' and 'field' apply to file systems, not tables. Tables have
rows and columns

Here's the thing: Celko considers Access to be a file system e.g.

http://groups-beta.google.com/group/comp.databases/msg/ab5a990c0a755ea8

">> Would you call MS Access a relational database system? <<
No. It depends on left to right evaluation of code, the DISTINCTROW
clearly shows that it does not use tables, pivots are reports and not
queries, forms are not part of an RDBMS, and it does not conform to
Standard SQL. It is an application development tool built around an
indexed file system with a front end that looks like (but does not work
like) SQL."

So I'd guess in the context of Access he would be fine with records and
fields <g>. However, in all seriousness, I cannot draw him into
discussion of Jet. From a recent email: "I work with Databases and not
ACCESS. If I were doing something for a small office and did not need
a database, I would probably go with FileMaker Pro."

Jamie.

--

 

[David W. Fenton]

Tom Wickerath <AOS168b AT comcast DOT net> wrote in

[quoting me:]

You're entitled to your opinion. I find prefixing my primary and
foreign key names helps keep things straight in my mind. For
example, if you see the ShipVia field in VBA code (see Northwind),
would you know without looking that it refers to a foreign key
field?

I'd never name a foreign key that way, as foreign keys should always
be ID fields (I don't believe in natural keys).

A student's database, for one!! <smile>

Well, then the error is not in naming conventions at all.

 

[Jamie Collins]

I'd never name a foreign key that way

I agree: fields should generally be consistent throughout the schema.
Sometimes they drop the class name (e.g. customer_last_name becomes
last_name in the Customer table) and occasionally they gain a
representation term (e.g. employee_ID becomes subordinate_employee_ID
and manager_employee_ID respectively).

The class name does the job e.g. a column named shipper_ID (to give it
its proper name <g>) in the Orders table, coupled with the existence of
a table named Shippers, should confirm that it comes from another
domain.

FWIW I don't consider Northwind to be a great example of DBMS design...

I don't believe in natural keys

I could not disagree more: a disbelief in natural keys ensures zero
data integrity because you have nothing but luck to save you from
duplicates.

....just like Northwind! For example, there is nothing to stop this:

INSERT INTO Shippers (CompanyName) VALUES ('Speedy Express');
INSERT INTO Shippers (CompanyName) VALUES ('Speedy Express');
INSERT INTO Shippers (CompanyName) VALUES ('Speedy Express');

Jamie.

--

 

[Armen Stein]

For another perspective, I'll throw some of our database conventions
in here:

Our foreign keys are named exactly the same as the primary key, with
an exception where the foreign key is duplicated or ambiguous. For
example tblEmployee has foreign key EmployeeKey_Boss, or tblCustomer
has SalesRepKey_Default.

For all fields in a table other than foreign keys, we prefix the field
name with the table name, like tblContact has ContactFirstName and
ContactLastName. This ensures that no two fields (other that FKs) are
named the same. It keeps things clear when the table name isn't
readily visible. Sure, the field names are longer, but they're easy
to understand in a complex system. The VBA editor will complete a
control name for you when you press Ctrl-Space.

We use the singular form for table names, because they are a bit
shorter, they read better (IMO) in code and SQL, and because some
table names are awkward in plural form. Tables may be safely assumed
as plural, without needing an 's' on the end. Apologies to Michael
Hernandez - it's one of the only things in Mere Mortals that I
disagree with!

We use meaningless AutoNumber/Identity keys almost exclusively, with
the exception of certain static lookup tables. We guard against
inappropriate duplicate records by using unique indexes or just
warning the user in the UI. Natural keys are cumbersome when used at
lower levels in complex databases, because they require multiple
fields to make up a primary key. In our databases, each relationship
includes just one field from each table.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Guest]

I'd never name a foreign key that way, as foreign keys should always

be ID fields (I don't believe in natural keys).

I'm not about to get into a religous debate over the merits of natural keys
versus meaningless numeric keys; that is not germaine to this discussion. In
the case of the ShipVia key, it is a numeric foreign key. (I'll agree with
Jamie that Northwind is certainly not a shining example of database design).
The issue was your statement "In my opinion, fields that contain the same
data should have the same name." I've never seen this written in stone for
primary key/foreign key fields in any authoratative database design books. If
you can find this, please cite a reference.

For non-key fields, take for example UnitPrice in the Northwind Products
table versus UnitPrice in the Order Details table, the fields could be
considered to contain the same data. Would you always name these types of
fields the same name (ie. where one field is duplicated to store historical
information, whether it be UnitPrice, Customer Name, Customer Address, etc.)?
Consider the following quote taken from page 22 of SQL Queries for Mere
Mortals (page referenced for the edition published in 2000):

"The main thing to remember: Make sure that each field in your database has
a unique name and that it appears only once in the entire database structure.
The only exception to this rule is when a field is being used to establish a
relationship between two tables."

So, Michael Hernandez and/or John Viescas have definately indicated where
they stand on non-key fields. Key fields are the only exception. But, I just
don't see anything that states that one must (or should) always name their
key fields the same.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tom Wickerath <AOS168b AT comcast DOT net> wrote in

[quoting me:]

You're entitled to your opinion. I find prefixing my primary and
foreign key names helps keep things straight in my mind. For
example, if you see the ShipVia field in VBA code (see Northwind),
would you know without looking that it refers to a foreign key
field?

I'd never name a foreign key that way, as foreign keys should always
be ID fields (I don't believe in natural keys).

A student's database, for one!! <smile>

Well, then the error is not in naming conventions at all.