Expert guide

NCR Nonconformance Log in Microsoft Access

Every nonconformance recorded, dispositioned and costed, with repeat issues driven to corrective action

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: create related tables for NCRs, Parts, Suppliers, Work Centres, CAPAs and Attachments; give each NCR a controlled number, source, part and lot, quantity, severity, containment, disposition, cost and root cause; enforce required fields on an entry form; and report open NCRs by age, cost of poor quality, and Pareto by part, supplier and cause. Link repeat or major NCRs to a CAPA record. Our manufacturing database in Access often includes this module.

Quality problems recorded on paper tags and in a shared spreadsheet are hard to learn from. The same supplier defect appears five times a year under five different descriptions; the cost of rework is never totalled; an auditor asks for the CAPA behind a customer complaint and it takes a day to find. A structured NCR log makes nonconformances countable, costable and traceable, which is what quality systems and auditors expect.

The Tables

TableKey fields
tblNCRNCRID, NCRNo, RaisedOn, RaisedBy, Source, PartID, Revision, Lot, SupplierID, WorkCentreID, QtyAffected, Description, Severity, Containment, Disposition, DispositionBy, Cost, RootCauseCat, CAPAID, Status, ClosedOn
tblPartsPartID, PartNo, Description, Customer
tblSuppliersSupplierID, Name, Approved status
tblWorkCentresWorkCentreID, Name, Department
tblCAPACAPAID, CAPANo, ProblemStatement, RootCause, Actions, Owner, DueDate, EffectivenessCheck, Status
tblNCRAttachmentsNCRID, FilePath, Description (photos, inspection reports)

Controlled NCR Numbering

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim yr As String, nextNo As Long
    yr = Format(Date, "yyyy")
    nextNo = Nz(DMax("Val(Mid(NCRNo, 6))", "tblNCR", "Left(NCRNo, 4) = '" & yr & "'"), 0) + 1
    Me.NCRNo = yr & "-" & Format(nextNo, "0000")      ' e.g. 2026-0142
    Me.RaisedOn = Now()
    Me.RaisedBy = Environ("USERNAME")
    Me.Status = "Open"
End Sub

In a multi-user database, two inspectors could get the same number in the same second. For heavy use, generate numbers from a counter table updated inside a transaction, or let the AutoNumber be the identity and add the formatted number on save.

The Entry Form

  • Required: source, part, quantity, description and severity before the record can be saved.

  • Part combo box shows part number, description and customer.

  • Supplier required when source is Incoming inspection; work centre required for In-process.

  • Disposition options limited to your approved list, and only users in the MRB/quality role can set them.

  • Attachment button to link photos and inspection reports stored on the server.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Source = "Incoming inspection" And IsNull(Me.SupplierID) Then
        MsgBox "Select the supplier for incoming inspection NCRs.", vbExclamation
        Me.SupplierID.SetFocus: Cancel = True: Exit Sub
    End If
    If Me.Status = "Closed" And IsNull(Me.Disposition) Then
        MsgBox "An NCR cannot be closed without a disposition.", vbExclamation
        Me.Disposition.SetFocus: Cancel = True
    End If
End Sub

Dispositions and Cost of Poor Quality

DispositionCost to record
Use as is (with concession)Engineering and approval time
ReworkLabour hours × rate + materials
ScrapMaterial + labour already invested
Return to supplierHandling, freight; recoverable amount separately
Customer returnFreight, replacement, credit, admin

Recording a cost on every NCR, even an estimate, turns quality from a count into money. A monthly cost of poor quality figure gets management attention that a defect count never does.

Reports Quality Managers Use

  • Open NCRs by age and owner, with overdue highlighted.

  • Pareto of NCR count and cost by part, supplier, work centre and root cause category.

  • Supplier scorecard: NCRs and rejected quantity per supplier per quarter.

  • Repeat issues: the same part and root cause category three or more times in 12 months without a CAPA.

  • CAPA status: open actions, due dates and effectiveness checks pending.

qryRepeatWithoutCAPA:
SELECT p.PartNo, n.RootCauseCat, Count(*) AS Occurrences, Sum(n.Cost) AS TotalCost
FROM tblNCR AS n INNER JOIN tblParts AS p ON n.PartID = p.PartID
WHERE n.RaisedOn >= DateAdd("m", -12, Date()) AND n.CAPAID Is Null
GROUP BY p.PartNo, n.RootCauseCat
HAVING Count(*) >= 3
ORDER BY Sum(n.Cost) DESC;

Where AI Helps the Quality Team

  • Root cause suggestions

    Suggest a root cause category from the description for the engineer to confirm.

  • Similar past NCRs

    Find earlier NCRs with similar descriptions even when wording differs.

  • CAPA problem statements

    Draft a clear problem statement from the linked NCRs.

  • Monthly quality summary

    Turn the Pareto and cost figures into a short narrative for the management review.

Technical detail and customer information may be confidential; use approved tools. See connecting Access to an AI API and AI summaries in Access reports. Quality losses also feed OEE calculations, and lab results tie in with lab sample tracking.

Audit Readiness

  • Never delete NCRs; void them with a reason.

  • Record who set the disposition and when.

  • Keep attachments linked and backed up with the database.

  • Produce an NCR report per record that matches your quality procedure's form.

We design quality modules as part of Access database design and development, with reports built through our reports and forms service.

FAQ

NCR log FAQ

Nonconformance, dispositions and CAPA

A nonconformance report (NCR) documents a product, material or process that does not meet a specification: what was found, where, how much is affected, the immediate containment, the disposition (use as is, rework, scrap, return to supplier) and, for significant issues, the corrective action to stop it recurring.

NCR number, date, source (incoming inspection, in-process, final inspection, customer complaint, audit), part and revision, lot or batch, supplier or work centre, quantity affected, description, severity, containment, disposition, cost, root cause category, CAPA reference, owner, due date and closure date.

An NCR records and dispositions a specific nonconforming item. A corrective and preventive action (CAPA) addresses the root cause so the problem does not recur. Not every NCR needs a CAPA; repeat or high-severity NCRs usually do, and the log should link them.

NCRs relate to parts, lots, suppliers, work centres, CAPAs and attachments. Access keeps those relationships, supports a proper entry form with validation, gives each NCR a controlled number, and lets several inspectors enter records at once. Excel struggles once several people edit the log.

AI can suggest a root cause category from the description, find similar past NCRs, draft the problem statement for a CAPA, and summarise monthly quality trends. Dispositions and root cause conclusions stay with qualified quality staff.