PQ Systems Knowledge Base

Your data may contain many other columns. For this demonstration we’ll work with a simple table. As long as you have a column to establish some grouping (we’ll use Date) and another column containing the PassFail data, the solution below should work.

For a p-chart, you need to know the number inspected and the number of failures to calculate a percentage for each data point on the chart. In this example, it is not immediately apparent where we get the number inspected. Also, on some days we have zero that fail but this fact is not obvious just looking at the data. For example, notice that on 1/1/2003 there are no FAIL records.

We need a solution that will convert the data shown above into a form more suitable for a p-chart. Ideally, we will end up with a result table that looks something like this:

In the attached .zip file, you will find an Access 2000 database and a CHARTrunner 1.6.x chart definition which demonstrate a solution to this problem. You will see that the chart is defined against a query that is stored in the Access database. Don’t be intimidated by this “transform” query. It looks complex, but it is only text that tell the database to do some work for us. It can easily be cut and pasted into your database and then altered to match your table and field names.

To study the solution to this problem, follow these steps:

Unzip the attached file into some temporary folder.
Run CHARTrunner 1.6.x or higher.
Set the CHARTrunner working folder to the folder you just created.
Take a look at the chart and the chart definition.
If you have Access 2000, you can look at the query by opening the Access database.

If you do not have Access 2000, here is the text of the query used to solve this problem:

TRANSFORM

iif(IsNull(count([pfdata].PassFail)),0,count([pfdata].PassFail)) AS CountOfPassFail

SELECT Min([pfdata].DATE) AS ProductionDate, Count([DATE]) AS Total
FROM pfdata
GROUP BY [pfdata].DATE
ORDER BY [pfdata].DATE
PIVOT [pfdata].PassFail;

Some things to note about this query:

The use of the IIF function (known as immediate if) is not standard SQL. This may not work for data in Oracle or other SQL databases. Without the IIF, the query will return an empty cell for days where there are NO failures. For a p-chart it is better to get an actual zero. To get a count for each date, we must use the count function and the group by clause. To get a separate column for PASS and FAIL, we use pivot.