Useful with Calibration Management Software Reports
SQL (pronounced sequel) stands for Structured Query Language. SQL is the basic language
of most common databases, including MS Access and MS SQL Server, the two databases
we use with Calibration Control. The part of the SQL language we need to focus on
is the SELECT statement, which we use to retrieve information from our calibration
management software (Calibration Control). Although SELECT statements are used by
reports, web pages, on screen displays, and even moving data between applications,
we will need SQL primarily for reports.
A Primer on Tables
Before we get started, let’s make sure we understand the source of our data when
we use a SELECT statement. Within a database, information is stored in tables that
look something like a spreadsheet with columns and rows. Unlike a spreadsheet, a
table uses records (horizontal rows) and fields (vertical columns).
Think of each record as if it were a photocopied standard form that you use for
keeping track of your test equipment and that you keep all these forms in a file.
The file may have a hundred forms, each with information describing a specific piece
of test equipment. Just like a table, you can sort your forms in a different order
and you can find records by scanning a single field on each form; it just takes
a bit longer with paper compared to a database table.
The Equipment Master View
Within Calibration Control, you will probably derive most of your reports from the
qryEquipmentMaster view (also called a query) because it has most of the fields
you need for your test equipment records. The fields in the view will also be easier
to read than their corresponding codes in the root table. Think of a view as a way
to pre-package part of the SQL complexity that makes your data easier to work with.
As an example, if you look at the contents of the tblEquipmentMaster table (where
your data is actually stored) you’ll see fields like ModelNumberID with meaningless
numbers in the fields (see image below). On the other hand, if you look at the qryEquipmentMaster,
you’ll see the actual model numbers and descriptions that you’re familiar with.
This is because the view has SQL code in it that looks up and displays the meaning
of the ModelID code so you don’t need to.
The SQL SELECT Statement
There are four main parts of the SELECT statement that we need to cover.
- SELECT – (IDs fields from a table that will be included.)
- FROM – (IDs the table where the fields come from.)
- WHERE – (Defines the filter that includes only the records you want to include.)
- ORDER BY – (Defines the sort order of the records.)
While the SELECT and FROM clauses are always required, you will only need to include
the WHERE and ORDER BY clauses when you want to filter and sort. Otherwise, your
results will include all records and in no particular order other than the physical
order of the underlying table.
Here is an example of a common SQL statement you would use to return all the fields
from the qryEquipmentMaster view:
Pretty easy, right? Notice the asterisk (*)? The asterisk is a wildcard that includes
all fields. With the relatively easy SELECT statements that we need in our calibration
management software, using the wildcard is usually your best bet because you don’t
need to worry about whether you forgot a field or not when you’re designing your
Now let’s assume that we want to filter and sort our results in the following example:
WHERE DepartmentCode = 'QA'
ORDER BY Location;
You can even add multiple filters and multiple sorts with the following MS Access:
WHERE (DepartmentCode = 'QA') AND (CalibrationDue < #5/1/2011#)
ORDER BY Location DESC, SerialNumber;
Note that the date value has number signs (#) around it rather than the single quotes
of the text values? You need to use the # sign when you are using MS Access and
the single quote (‘) when using SQL Server. Although both databases use SQL Server,
there are still slight differences.
Here's the same code for MS SQL Server:
WHERE (DepartmentCode = 'QA') AND (CalibrationDue < '5/1/2011')
ORDER BY Location DESC, SerialNumber;
The final example deals with filtering for numeric values and is compliant with
both MS Access and MS SQL Server. Note that the
value (1) uses neither the single quote (‘) or the number sign (#)?
WHERE FrequencyUnits = 1;
You can learn more about the SQL SELECT statement through an Internet search. If
you can think of any good beginner SQL sites, let me know and I can post a link
on this page.
Last updated: 13 Jun 2011
Compatibility: Calibration Control versions 5.x