Expert guide

Data Quality Checks for Microsoft Access with Queries and AI

Find the bad data before it reaches an invoice, a report or a migration

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: create one saved query per data problem (blanks in required fields, duplicates, orphans, out of range values, bad formats), log each query's count into a tblDataIssues table on a schedule, and review it on a dashboard form. Use AI for what rules cannot catch: near-duplicate names, notes that contradict status fields, and miscategorised items. Then prevent recurrence with validation at entry. Our Access optimisation work starts with exactly this audit.

Bad data in an Access database is rarely dramatic. It is a customer entered twice with slightly different names, an order line pointing at a product that was deleted, a ship date before the order date, a phone number field holding an email address. Each one is small. Together they produce reports nobody trusts, duplicate mailings, failed migrations and invoices that need correcting.

Ten Check Queries Every Database Needs

CheckWhat it findsQuery pattern
1. Missing required valuesCustomers with no email or phoneWHERE Nz(Email,'')='' AND Nz(Phone,'')=''
2. Exact duplicatesSame name and postcode twiceGROUP BY Name, Postcode HAVING Count(*)>1
3. Orphan child recordsOrders for deleted customersLEFT JOIN parent WHERE parent key Is Null
4. Childless parentsOrders with no linesLEFT JOIN lines WHERE line key Is Null
5. Out-of-range numbersNegative quantities, 0 pricesWHERE Qty<=0 OR UnitPrice<=0
6. Impossible datesShip before order, future birth datesWHERE ShipDate<OrderDate OR DOB>Date()
7. Invalid formatsEmails without @, short phone numbersWHERE Email Not Like '*@*.*'
8. Inconsistent categoriesStatus values outside the agreed listWHERE Status Not In (SELECT Status FROM tblStatus)
9. Stale open recordsJobs open for 180+ daysWHERE Status='Open' AND OpenedOn<Date()-180
10. Totals that do not add upHeader total differs from sum of linesJoin header to grouped lines, compare

Example: orphan order lines

SELECT l.LineID, l.OrderID, l.ProductID
FROM tblOrderLines AS l
LEFT JOIN tblOrders AS o ON l.OrderID = o.OrderID
WHERE o.OrderID Is Null;

Example: header total mismatch

SELECT o.OrderID, o.OrderTotal, s.LineSum
FROM tblOrders AS o
INNER JOIN (SELECT OrderID, Sum(Qty * UnitPrice) AS LineSum
            FROM tblOrderLines GROUP BY OrderID) AS s
  ON o.OrderID = s.OrderID
WHERE Abs(o.OrderTotal - s.LineSum) > 0.01;

Logging Results to an Issues Table

Public Sub RunDataChecks()
    Dim db As DAO.Database, qd As DAO.QueryDef, n As Long
    Set db = CurrentDb
    For Each qd In db.QueryDefs
        If qd.Name Like "qdc_*" Then              ' naming convention for checks
            n = DCount("*", qd.Name)
            db.Execute "INSERT INTO tblDataIssues (CheckName, IssueCount, RunOn) " & _
                       "VALUES ('" & qd.Name & "', " & n & ", Now())", dbFailOnError
        End If
    Next qd
End Sub

Name every check query with a prefix such as qdc_ and the routine picks it up automatically. A dashboard form shows the latest count for each check and the trend over the past few weeks; a rising count is a process problem, not a data problem.

Where AI Adds Checks Rules Cannot Express

Near-duplicate customers and suppliers

Rules catch exact duplicates. "Smith & Sons Plumbing", "Smith and Sons Plumbing Ltd" and "Smiths Plumbing" need judgement. Build candidate pairs that share a postcode or phone number, then ask AI whether each pair is the same organisation and why. Review the "same" verdicts before merging.

For each pair, answer: same | different | unsure, with a 10-word reason.
Treat different branches of the same company as different.
1. "Smith & Sons Plumbing" (LS6 2AB) | "Smith and Sons Plumbing Ltd" (LS6 2AB)
2. "North Dental Care" (M14 5RT) | "Northside Dental" (M14 5RT)

Notes that contradict structured fields

A job marked Completed whose notes say "waiting for part, return visit needed" is a data quality problem that no query will find. Ask AI to compare the status with the last note and flag contradictions.

Miscategorised items

Products filed under the wrong category, expenses under the wrong account, tickets with the wrong type. Send name, description and current category; ask whether the category fits and, if not, which one does from your list.

The VBA plumbing for these calls is in connecting MS Access to the OpenAI API. Write AI verdicts to a review table, never directly into the data. The Excel equivalent for messy lists is cleaning messy Excel data with AI.

Fix the Cause, Not Just the Rows

ProblemPrevention in Access
OrphansEnforce referential integrity in the Relationships window
DuplicatesUnique index on the natural key; duplicate check in the form's BeforeUpdate event
Blank required fieldsRequired = Yes on the field, or form-level validation with a clear message
Inconsistent categoriesCombo box bound to a lookup table with Limit To List = Yes
Bad formatsValidation Rule and Input Mask on the field
Impossible datesTable-level validation rule: [ShipDate] >= [OrderDate]

Before a Migration, Always Run This

SQL Server enforces rules Access let slide: foreign keys reject orphans, unique constraints reject duplicates, and data types reject text in number fields. Running these checks first turns migration failures into a cleanup list. See AI-assisted Access to SQL Server migration. If the problems stem from table design, such as repeated columns or multivalued fields, the fix is structural; our Access database design service handles that.

FAQ

Access data quality FAQ

Checks, fixes and prevention

Run a set of saved queries that each look for one kind of problem: required fields left blank, duplicates, orphan child records, values outside expected ranges, invalid formats, and inconsistent spellings. Store the results in an issues table with a count per check, review it weekly, and fix the causes, not just the rows.

AI helps with the problems rules cannot express: spotting that 'Acme Ltd' and 'ACME Limited' are the same customer, that a free-text note contradicts a status field, or that a product description does not match its category. It also helps write the rule-based check queries quickly from a description.

Use a LEFT JOIN from the child table to the parent and filter where the parent key is Null, for example orders whose CustomerID does not exist in tblCustomers. The Find Unmatched Query Wizard builds this for you. Enforcing referential integrity in the Relationships window prevents new orphans.

Only after you understand why it happened and have a backup. Mass updates can merge records that should stay separate or overwrite correct values. Fix in small, reviewed batches, log what you changed, and add validation at the point of entry so the problem does not return.

Weekly for most operational databases, and always before month-end reporting or a migration. The checks can run automatically on database open or from a scheduled task, writing results to a dashboard form.