Queries on data from a single table are of limited use but queries really come into their own when combining data from two or more tables. A query that listed batches which have not had any data entered for some time might be a useful thing, as would a query which list batches with features whose Cpk is less than 1.0 (or any other figure).
First though we will construct a basic building block for later queries and write a query that combines the Features and VarFeatures tables so that we can treat them as one. We will use SQL's INNER JOIN function which combines matching records from two tables. Recall that most of ATS SPC’s database tables include an ID field of some sort: BatchID, FeatureID etc. and these are used to link together records from different tables. In the case of the Features and VarFeatures tables the linking field is called FtrID and the SQL statement to do the job looks like this…
SELECT BatchID, FtrID, Sequence, Type, Name, ShortName
FROM Features INNER JOIN VarFeatures
ON Features.FtrID = VarFeatures.VarFtrID;
We have told the query engine to combine records from the Features table with records from the VarFeatures table if the FtrID field in one equals the VarFtrID field in the other. Records from either table which have no matching record in the other table are not included. Note the dot syntax when specifying fields from a specific table: table.field. Sometimes specific table.field identifiers are necessary if, for example, two tables have fields of the same name. Here Access insists on them when specifying fields for a JOIN.
Changing to datasheet view will show you what amounts to a list of features in your ATS SPC database (since ATS SPC v2.0 has no non-variables features).
In Access, multiple table queries with JOINs are perhaps easier to implement visually than in SQL. You just add more tables from the Show Table dialog. (choose Query – Show Table to open the dialog if it is not already there) and drag fields from any table into the query columns. Access knows the relationships between the tables so it is able to fill in the JOIN clauses automatically. This is perhaps the best part of building queries visually!
Save the query as say, qryFeatures, so we can use it again. (To save just try to close the query window and Access will prompt you for a name to save it by.)
While there are other types of JOIN in SQL, the real purpose of this chapter is to introduce you to ATS SPC's database so we will ignore them for now.
The query above will be even more useful if we add some statistical results to it. These can be found in the VarStats and ProcessStats tables depending on which type of feature it is… the former for basic features and the latter for Mean/Range and Mean/Sigma features.
We will use the ProcessStats table in this example. Re-open qryFeatures and edit the SQL to read…
SELECT BatchID, FtrID, Sequence, Type, Name, ShortName, Cp, Cpk
FROM (Features INNER JOIN VarFeatures
ON Features.FtrID = VarFeatures.VarFtrID)
INNER JOIN ProcessStats ON Features.FtrID = ProcessStats.VarFtrID;
The parts to add are shown in bold typeface – and don't forget the parentheses around the first JOIN in the FROM clause! Effectively another table is JOINed to the pair now in parentheses in the original JOIN clause. To build such a query visually just add another table!
Switch to datasheet view to see the results. This is beginning to get useful – we have a list of features with their Cp and Cpk values. Add an ORDERED BY clause and you could have the list in ascending order of Cpk. The worst process in your database would be shown at the top of the list.
Choose File SaveAs and save this query (into the current database) as qryProcFtrs – we'll use it again later.
Since queries are like tables in many ways can they be used in further queries? The answer is 'yes' and qryProcFtrs from the last example is ripe for further use combining as it does all the information you might want about features.
We can JOIN the Batches table with the qryProcFtrs query to produce an ordered list of features and their parent batches in order of Cp or Cpk. This will give a useful 'hit list' of the worst processes on the shop floor…
SELECT Batches.Name AS Batch, qryProcFtrs.Name AS Feature, Cp, Cpk
FROM Batches INNER JOIN qryProcFtrs
ON Batches.BatchID = qryProcFtrs.BatchID
ORDER BY Cpk;
Note the renaming of fields – both Batches and qryProcFtrs have a field called 'Name'. Batches.Name specifically refers to the field in the Batches table and 'AS Batch' means that, in this query, that field will be known as 'Batch'.
Switch to datasheet view and see where your quality nightmares are!