Access Yes/No Pass Fields: Mapping Calibration Results

"It’s either ticked or it isn’t." That is how most people describe the Pass column in their Access calibration database, and it sounds harmless until you start mapping that column into a new system. Then someone asks what the No on a torque wrench from March 2021 actually means. Did it fail? Did the lab adjust it and return it in tolerance? Did it never go out at all? Nobody knows. The column never had a way to say.

This is a common way for a migration to stall. Picture it: the quality engineer has the export, the field map and a cutover date. Then the dry-run report shows a couple of hundred historical calibrations as failures, and the plant manager asks why none of them has an impact assessment. They were never all failures. It was one Boolean column carrying four or five different meanings, and it had been doing that for a decade.

This article covers how that happens, why it matters to an auditor, and how to map legacy results properly before cutover instead of discovering the problem after.

Why the Yes/No field looked like the right choice

Pass/fail is how technicians talk, and a checkbox on a form is the fastest thing to click. When the database was built, the question it had to answer was simple: "Can this gauge be used on the line?" A Yes/No field answers exactly that.

The problem is structural. In Access, a Yes/No column in a table holds only True or False. It cannot hold Null. The triple-state checkbox you can put on a form does not change what the table stores. So when a record is created before the result is known, for example when a gauge is logged as sent to the external lab, the field is already False. Unless someone comes back and ticks it, that row reads as a fail forever.

Over the years, people route around it. One technician unticks Pass for anything the lab adjusted. Another leaves it ticked and writes "adj." in Comments. A third uses No for "restricted to the 0–25 mm range" because there is nowhere else to put it. None of this was careless. Each person made a reasonable local decision in a field that had only two values.

What one column was actually storing

When you pull every row where Pass is False and read it alongside Comments, the certificate and the dates, you usually find the same five categories:

1. Genuine failure. The as-found reading was out of tolerance, and the instrument was repaired, scrapped or downgraded.
2. Adjusted. The instrument was out of tolerance or near the limit as found, the lab adjusted it, and it left in tolerance.
3. Limited or conditional. It passed only over part of its range or for certain uses.
4. Not assessed. The row was created in anticipation of an event that never produced a result: a gauge that was lost, withdrawn or never sent.
5. Default False. The event happened, the certificate says pass, and nobody went back to tick the box.

There are usually ambiguities among the True rows as well. A tick can hide an as-found reading that was outside tolerance, because the lab adjusted the instrument and someone recorded only the final state.

Why an auditor cares which one it was

These are not cosmetic distinctions. Each category leads to a different obligation.

ISO 9001 clause 7.1.5.2 requires you to decide whether the validity of previous measurement results was adversely affected when equipment is found unfit for its purpose, and to take action. IATF 16949 goes further. Clause 7.1.5.2.1 expects calibration records to include out-of-specification readings as received and an assessment of the risk to product already measured with that instrument. ISO/IEC 17025 clause 7.8.4 asks calibration certificates to report results before and after any adjustment or repair, where available.

Put a real certificate next to each category:

• A genuine failure needs a documented impact assessment. If you import it as Fail, the auditor will reasonably ask to see that assessment.
• An adjusted instrument that was within tolerance as found needs no product recall review. An adjusted instrument that was outside tolerance as found needs the same review as a failure. A single Yes/No cannot tell you which it was.
• A limited instrument needs its limitation visible to the person checking it out. Otherwise someone uses a gauge on a range it was never verified for.
• A not assessed row should not become a calibration record at all. Importing it as Fail fabricates a failure. Importing it as Pass fabricates a calibration, and that is worse.
• A default False row with a passing certificate is a pass. Leaving it as Fail pollutes your history and makes any interval analysis look pessimistic.

If you map Yes to Pass and No to Fail wholesale, you import every one of these errors into a system whose whole purpose is to be believed.

A mapping method that holds up

The approach below has worked for me. It takes days, not months, because you only review what the Boolean can’t tell you.

1. Split the population before you read anything

Export the history table with Pass, Comments, dates, lab and certificate reference. Split it into four groups: True with no comment, True with a comment, False with a certificate, and False without one. In most databases the first group is by far the largest and can map straight to Pass. Spend your time on the other three.

2. Write the triage rules down

Before anyone classifies a row, agree the rules in writing. For example:

• False, certificate present and stating conformity → Pass. Record the correction reason.
• Any row with "adj", "adjusted" or "re-set" in Comments → pull the certificate and capture the as-found and as-left values.
• False, no certificate, no later event on the same date → not assessed. Do not import it as a calibration.
• Comments mentioning range or use restrictions → conditional result, with the limitation written into the notes.

Written rules make this a controlled data correction instead of one person’s judgment. They also go into your migration record, so the reasoning is still available when someone asks in two years.

3. Recover the as-found values from certificates

This is the step that pays for itself. For every adjusted or failed row, the external certificate usually still has the as-found reading even though your database threw it away. Capturing as-found and as-left separately turns "No" into evidence, for example: out by 3.1 % as found, adjusted, 0.4 % as left.

4. Resolve the genuine failures, don’t just relabel them

For each confirmed failure, check whether an impact assessment exists anywhere: an NCR, an email, a paper file. If it does, reference it in the record notes. If it doesn’t, that is a finding waiting to happen whether or not you migrate. It is far better to raise it yourself now than to let an auditor find it in the new system’s clean report.

Where the results land in PrüfDesk

PrüfDesk records a calibration result as pass, fail or conditional pass. It stores as-found and as-left values and units separately, per-point measurements with a within-tolerance flag, notes, certificate number, issuing lab and the certificate file. That gives the categories above somewhere to go:

• Genuine failure → Fail, with as-found captured and the impact assessment referenced in notes.
• Adjusted → Pass, with as-found and as-left both recorded so the adjustment is visible. If the as-found value was out of tolerance, treat the record like a failure for impact review.
• Limited → Conditional pass, with the limitation stated in notes.
• Not assessed → no calibration record. The instrument’s status reflects its real last calibration, or shows it as never calibrated.
• Default False with a passing certificate → Pass, with the correction noted.

Calibration records in PrüfDesk are append-only once saved, which is exactly why you want the classification right before import rather than after. From then on, due dates are auto-calculated from the real last calibration, and a correct history feeds the audit-ready PDF reports directly.

"Can’t we just import it as-is and fix it later?"

This is the objection I hear most often, and it is reasonable to ask. The honest answer is that "later" rarely comes, and an immutable record is designed not to be quietly edited. Correcting it afterwards means adding correction entries on top of wrong ones, and every auditor who samples that instrument sees both. Fixing the classification before import costs a few days. Fixing it afterwards costs you an explanation at every audit.

The other version of the objection is "it’s only history." It stops being only history the first time a customer complaint traces back to a part measured with a gauge that your records call a failure with no assessment attached.

Pre-migration checklist for result fields

• [ ] Count True and False rows, and split each by whether Comments is populated
• [ ] Agree written triage rules for adjusted, limited, not assessed and default False
• [ ] Pull certificates for every adjusted or failed row and capture as-found and as-left
• [ ] Confirm an impact assessment exists for each genuine failure, or raise one
• [ ] Exclude not-assessed rows from calibration history, and document why
• [ ] Keep the triage rules and correction log with your migration record

Where to start

You don’t need to fix all of this this week. Run one query, Pass = False, and read the first thirty rows alongside their certificates. You’ll know within an hour how many meanings your column has been carrying. If you’d like to see how those results map into PrüfDesk before you commit to anything, we’re happy to walk through a sample of your history table with you.