Scan Documents into Microsoft Access with AI

The Pipeline at a Glance

• 1. Intake folder

  Scanners, a shared mailbox rule or a phone app drop files into \Docs\Inbox.

• 2. Extract

  A process sends each file to the AI model and receives JSON with the defined fields.

• 3. Stage

  Results go into tblDocStaging with the file path and status New.

• 4. Validate

  Queries mark each row Ready or Review with a reason.

• 5. Review

  A form shows the scan next to the extracted fields for correction.

• 6. Post

  An append query moves Ready rows to live tables; files move to \Docs\Processed\yyyy\mm.

Step 1: Define the Fields per Document Type

{
  "doc_type": "delivery_note",
  "delivery_number": "string",
  "delivery_date": "YYYY-MM-DD",
  "customer_name": "string",
  "customer_account": "string or null",
  "po_number": "string or null",
  "lines": [{ "sku": "string", "description": "string", "qty": number }],
  "signed": true/false,
  "received_by": "string or null",
  "notes": "anything handwritten or unclear"
}

Step 2: The Staging Table

Step 3: Validation Queries

-- Unknown customer
UPDATE tblDocStaging AS s
SET s.Status = "Review", s.Reason = "Customer not found"
WHERE s.Status = "New"
  AND DCount("*", "tblCustomers",
             "AccountNo='" & Nz(s.CustomerAccount, "") & "'") = 0;

-- Duplicate delivery number already posted
UPDATE tblDocStaging AS s
SET s.Status = "Review", s.Reason = "Duplicate delivery number"
WHERE s.Status = "New"
  AND DCount("*", "tblDeliveries",
             "DeliveryNo='" & s.DeliveryNumber & "'") > 0;

-- Unsigned delivery note
UPDATE tblDocStaging SET Status = "Review", Reason = "Not signed"
WHERE Status = "New" AND Signed = False;

-- Everything else passes
UPDATE tblDocStaging SET Status = "Ready" WHERE Status = "New";

Step 4: The Review Form

• A continuous form listing Review rows with their Reason.

• A detail form with the extracted fields on the left and a WebBrowser control or Edge browser control showing the PDF on the right.

• Buttons: Approve (sets Ready), Reject (sets Rejected with a note), and Open file.

• ReviewedBy and ReviewedOn filled automatically.

Step 5: Running the Extraction

Scanning Tips That Raise Accuracy

• Scan at 300 dpi in greyscale; colour rarely helps and makes files larger.

• One document per file; split batches with the scanner's blank-page or barcode separator.

• Redesign your own forms with labelled boxes and printed reference numbers.

• Ask field staff to photograph forms flat, in good light, filling the frame.

• Add a QR code with the job number to forms you print; it removes the hardest field.

Where This Pays Off