‘±0.02’, ‘+0.05/-0’, ‘see dwg’: Turning an Access Tolerance Column into Limits a System Can Judge

You are three days into the field-mapping spreadsheet for leaving the Access calibration database. Instrument ID, serial, location and interval each mapped cleanly to a target field. Then you reach the Tolerance column, apply a filter to see the distinct values, and the list keeps scrolling: ±0.02, +/-0.02 mm, +-.02, 0.001 in, +0.05/-0, 4%, ±4% of setting, GO/NO-GO, see dwg, as per OEM, and a lot of blanks.

Nobody did anything wrong. That column was built for a person to read. The technician opened the form, read "±0.02", compared it with the reading, and ticked Pass. Access never had to understand the tolerance, because a human did that part every time.

A calibration system has to understand it. If it is going to judge an as-found reading, flag an out-of-tolerance condition, or show an auditor why a gauge passed, it needs numbers: a nominal, a lower limit, an upper limit and a unit. Free text gives it none of those. This article covers how to get from one column to the other without losing history or inventing precision.

Why a text tolerance is a records problem, not a tidiness problem

It is tempting to treat this as cosmetic: move the text into a notes field and deal with it later. Look at what the standards ask for, though.

ISO 9001 clause 7.1.5.2 requires that when measuring equipment is found unfit for its intended purpose, you determine whether the validity of earlier measurement results has been adversely affected. IATF 16949 clause 7.1.5.2.1 goes further. Calibration records must include out-of-specification readings as received, plus an assessment of the risk to the equipment’s intended use. AS9100 and ISO/IEC 17025 set comparable expectations for the decision on conformity and for what follows from it.

Each of those obligations depends on one question being answerable mechanically: was the as-found reading inside the limits? When the limit is the string +0.05/-0, only a person can answer, one record at a time, from memory of what the string meant. That leaves you with no out-of-tolerance report, no list of affected instruments when a reference standard turns out to be wrong, and no consistent pass/fail across technicians. You have a judgment that was made correctly most of the time, and no means of showing which times.

What is actually hiding in the column

Before parsing anything, sort the entries by what kind of tolerance they express, not by how they are spelled. Most Access Tolerance columns hold four kinds:

Symmetric limits

±0.02, +/-0.02 mm, +-.02. These are the easy case: nominal minus and plus the same value. The variation is only in spelling. Watch the character encoding when you export: depending on the export path, a ± written in one code page can come out as ± in another. Export as UTF-8 and check a few rows by eye before you trust the parse.

Asymmetric limits

+0.05/-0, +0/-0.03, 0.05 / 0. These are a common source of false passes once flattened. When a parser reads +0.05/-0 as "±0.05", a reading 0.04 below nominal passes when it should fail. You need two separate numbers, and the sign of each must survive.

Relative limits

4%, ±4% of setting, 1% FS. Torque tools are the usual case. Click torque wrenches are commonly checked against a percentage of the set value, and the limit changes at every test point. "Percent of reading" and "percent of full scale" give very different bands at the bottom of the range, and the text often leaves it unclear which one was meant.

Not a numeric tolerance at all

GO/NO-GO, see dwg, as per OEM, 6H, blank. A thread plug gauge is accepted or rejected by attribute checks and by the wear limits in its governing standard. It has no single ± value. see dwg points to a document the database never held. These rows must not be forced into numbers. They need a different record type, or a reference to the controlled document that holds the limit.

Then there is the ambiguous case that justifies the whole exercise: 0.001 in. That could be ±0.001 in, a total band of 0.001 in, or the instrument’s resolution typed into the wrong column. No parser can tell which. Only someone who knows the instrument can.

A worked normalisation pass

The method I have used, and would use again, is to review patterns, not rows.

1. Group by pattern. Strip the digits from each entry and replace them with a token, so ±0.02 mm and ±0.15 mm both become ±N mm. Count each pattern. The list of distinct patterns is usually far shorter than the list of rows. Run the query against your own data to see how much shorter, and you can then estimate the effort honestly.

2. Write one parsing rule per pattern. For every pattern decide the output: tolerance type (symmetric, asymmetric, relative, attribute, reference), lower limit, upper limit, unit, and for relative limits the basis (reading or full scale). Units are often missing. Infer them only from something documented, such as the instrument type’s range or the procedure, and mark every inferred unit as inferred.

3. Send the ambiguous rows to a named reviewer. Anything without a confident rule, including 0.001 in, blanks and the unit-less entries on instruments that could be metric or imperial, goes to a review list with the instrument, the original text and a proposed interpretation. The reviewer is a person with a name and a date, not "the team". Their decision becomes part of the migration record, so if an auditor later asks why this gauge carries ±0.001 in, you can show who decided and on what basis.

4. Recompute historical pass/fail. This step is what makes the pass more than cleaning. Using the new structured limits, recompute pass/fail for every historical as-found reading and compare the result with the Pass that was originally recorded. Each disagreement means one of two things. Either your parsing rule is wrong, so you fix the rule, or the original judgment was wrong, so you have a historical out-of-tolerance condition that nobody assessed. The second kind is uncomfortable, but finding it yourself before go-live is much better than having an auditor find it afterwards. Handle it the way 7.1.5.2 requires: assess the impact on earlier results and record what you concluded.

When recomputing, compare values at the resolution the reading was taken at, not as raw floating-point numbers. Access Single and Double fields store approximations. A reading of exactly 0.020 against a limit of 0.020 can come out on the wrong side of the comparison if you compare stored binary values directly. Round both values to the instrument’s resolution first.

The objection: "We’ll migrate the text and fix it afterwards"

I understand the appeal. The cutover date is fixed, the Tolerance column is the messiest field you have, and a notes field accepts anything. There are three problems with doing that.

First, "afterwards" seldom arrives. Once go-live is behind you, the new system is the record and every technician is learning it, so nobody has a week free for a cleaning pass.

Second, the new system starts out unable to do the thing you bought it for. It holds as-found and as-left values but cannot judge them against anything, so pass/fail is still a person’s tick, just entered on a newer screen.

Third, you lose step 4. Recomputing history only works while the old Pass values and the readings are both in front of you and the migration is still open. After that, the historical out-of-tolerance conditions stay buried in text that no query can read.

Where the structured limits go

In PrüfDesk, the check for an equipment type lives in a reusable calibration procedure template. Every instrument of that type follows the same steps, and a per-unit override covers the one gauge that really is different, the one your Access database probably handled by cloning the equipment type and adding "SPECIAL" to its name. Each calibration then logs as-found and as-left values with a pass/fail result and its certificate, and those records feed the audit-ready PDF compliance reports. None of that removes the normalisation work. It gives the result somewhere to live where it gets used on every calibration, rather than being read by eye.

Pre-migration checklist for the Tolerance column

• Export the column as UTF-8 and confirm that ±, µ and ° survived.
• Group entries by pattern and record the count of rows per pattern.
• Classify each pattern as symmetric, asymmetric, relative, attribute or reference.
• Write a parsing rule per pattern, and mark every inferred unit as inferred.
• Assign a named reviewer to the ambiguous rows, with a deadline before the final extract.
• Recompute historical pass/fail against the new limits, at the reading’s resolution.
• Treat every real disagreement as a potential historical out-of-tolerance condition and assess it under 7.1.5.2.
• Keep the original text alongside the structured limits, so the source of every value can be traced.

If you have reached this column in your own mapping spreadsheet, start with step 1. The pattern count will tell you whether this is an afternoon’s work or a fortnight’s, and that is worth knowing before the cutover date is fixed. When you want to see where the cleaned limits would sit in a working calibration program, PrüfDesk is ready for that conversation.