Expert guide
Lab Sample Tracking Database in Microsoft Access
From receipt to certificate: every sample, test, result and hand-off recorded
Call us at +1 (385) 386-3860
Short answer: register each sample with a unique, barcoded ID; assign the required tests from a test catalogue with specification limits; record custody events and storage location; enter results against each sample-test with analyst and timestamp; flag out-of-spec values automatically; require review before release; and report turnaround time and backlog. Access suits small, non-validated or lightly regulated labs; we design these through Access database design and development.
Small testing labs, quality labs inside manufacturers, environmental consultancies and university groups often run on a sample spreadsheet and paper worksheets. It works until a client asks where their sample is, two analysts test the same sample, or a certificate goes out with a value nobody reviewed. A sample database brings order: one ID per sample, a clear status, and a complete record of who did what.
The Tables
| Table | Key fields |
|---|---|
| tblClients / tblProjects | ClientID, Name, Contact, Default turnaround |
| tblSamples | SampleID, SampleNo, ClientID, ProjectRef, Matrix, CollectedOn, ReceivedOn, ReceivedBy, Condition, StorageLocation, Status, DueDate |
| tblTests | TestID, Code, Method, Units, LowerSpec, UpperSpec, StdTurnaroundDays |
| tblSampleTests | SampleTestID, SampleID, TestID, AssignedTo, Status |
| tblResults | ResultID, SampleTestID, Value, Qualifier (<LOQ etc.), InstrumentID, EnteredBy, EnteredAt, OutOfSpec, ReviewedBy, ReviewedAt |
| tblCustody | SampleID, EventAt, Action (received, moved, aliquoted, disposed), FromLocation, ToLocation, By |
| tblInstruments | InstrumentID, Name, CalibrationDue |
Registration and Labels
Registration form: client, project, matrix, collected and received dates, condition on receipt, tests requested.
SampleNo generated as year-month-sequence and printed as a barcode label immediately.
Due date calculated from the slowest requested test's turnaround, or the client's agreed turnaround.
Custody event 'Received' written automatically with the user and time.
Due date on registration:
Me.DueDate = DateAdd("d", Nz(DMax("StdTurnaroundDays", "tblTests", _
"TestID In (SELECT TestID FROM tblSampleTests WHERE SampleID=" & Me.SampleID & ")"), 5), _
Me.ReceivedOn)For working-day turnaround, use a small function that skips weekends and lab holidays rather than DateAdd with calendar days.
Results Entry With Automatic Spec Checks
' txtResult is bound to tblResults.Value (avoid naming the control "Value")
Private Sub txtResult_AfterUpdate()
Dim lo As Variant, hi As Variant
lo = DLookup("LowerSpec", "tblTests", "TestID=" & Me.Parent!TestID)
hi = DLookup("UpperSpec", "tblTests", "TestID=" & Me.Parent!TestID)
Me.OutOfSpec = (Not IsNull(lo) And Me.txtResult < lo) Or _
(Not IsNull(hi) And Me.txtResult > hi)
Me.EnteredBy = Environ("USERNAME")
Me.EnteredAt = Now()
End SubLock results after review: AllowEdits off when ReviewedBy is filled, with changes requiring a reason and an audit record.
A reviewer cannot be the same person as the analyst who entered the result.
Out-of-spec results appear on a separate review list for a second check.
Instruments past calibration due date cannot be selected.
Status and Turnaround
qryBacklog:
SELECT s.SampleNo, c.Name, s.ReceivedOn, s.DueDate,
DateDiff("d", Date(), s.DueDate) AS DaysLeft,
Sum(IIf(st.Status = "Complete", 1, 0)) AS TestsDone,
Count(st.SampleTestID) AS TestsTotal
FROM (tblSamples AS s
INNER JOIN tblClients AS c ON s.ClientID = c.ClientID)
INNER JOIN tblSampleTests AS st ON s.SampleID = st.SampleID
WHERE s.Status <> "Reported"
GROUP BY s.SampleNo, c.Name, s.ReceivedOn, s.DueDate
ORDER BY s.DueDate;| Metric | Calculation |
|---|---|
| Turnaround time | Reported date − received date, by test and client |
| On-time rate | Samples reported by due date ÷ samples reported |
| Backlog | Open samples by days left and by analyst |
| Out-of-spec rate | Out-of-spec results ÷ results, by test and client |
| Re-test rate | Tests repeated ÷ tests completed |
Certificates of Analysis
An Access report per sample lists tests, methods, results, units, specifications and pass/fail, with reviewer and date. It exports to PDF and can be emailed to the client automatically once released; see exporting Access reports to PDF with VBA.
Where AI Helps a Lab
Extracting values from instrument printouts or scanned worksheets into a staging table for analyst confirmation.
Reading supplier certificates of analysis for incoming materials.
Summarising a project's results into a narrative for a report, using only approved values.
Classifying free-text observations on receipt (leaking container, wrong preservative) into condition codes.
Document capture follows scanning documents into Access with AI. Quality failures can raise records in the NCR log.
Regulated Labs and Scale
Labs working under accreditation or regulations that require validated systems and strict electronic records rules need audit trails, access control and validation documentation. Many start in Access and move the back end to SQL Server for stronger security and auditing; see Access to SQL Server migration. We are happy to advise on whether Access is appropriate for your lab before building anything.
FAQ
Lab sample tracking FAQ
Samples, results, custody and turnaround