Add AI Summaries to Microsoft Access Reports

Design: Generate, Store, Approve, Display

• Generate

  A button or scheduled routine builds the input from an aggregate query and calls the AI.

• Store

  The draft goes into tblReportSummaries with the report name, period and status.

• Approve

  A manager edits the text on a form and marks it Approved.

• Display

  The report header shows the approved summary for the period being printed.

Step 1: The Summary Table

Step 2: Build the Input From an Aggregate Query

qrySalesSummaryByRegion (saved query, parameters from a form):
SELECT [Region],
       SUM(IIf([OrderDate] >= [Forms]![frmReportMenu]![txtStart]
               AND [OrderDate] <= [Forms]![frmReportMenu]![txtEnd],
               [LineTotal], 0)) AS ThisPeriod,
       SUM(IIf([OrderDate] >= DateAdd("yyyy", -1, [Forms]![frmReportMenu]![txtStart])
               AND [OrderDate] <= DateAdd("yyyy", -1, [Forms]![frmReportMenu]![txtEnd]),
               [LineTotal], 0)) AS SamePeriodLastYear
FROM qryOrderLines
GROUP BY [Region];

Step 3: Generate the Draft With VBA

Public Sub DraftSalesSummary(ByVal pStart As Date, ByVal pEnd As Date)
    On Error GoTo Fail
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim inputText As String, draft As String, diff As Double

    Set db = CurrentDb
    Set rs = db.QueryDefs("qrySalesSummaryByRegion").OpenRecordset()
    ' (parameters resolve from frmReportMenu, which must be open)

    Do While Not rs.EOF
        diff = Nz(rs!ThisPeriod, 0) - Nz(rs!SamePeriodLastYear, 0)
        inputText = inputText & rs!Region & ": " & _
            Format(rs!ThisPeriod, "$#,##0") & " vs " & _
            Format(rs!SamePeriodLastYear, "$#,##0") & " last year (" & _
            Format(diff, "+$#,##0;-$#,##0") & ")" & vbCrLf
        rs.MoveNext
    Loop
    rs.Close

    draft = AskAI("Write a 3–4 sentence summary of regional sales for a " & _
                  "management report. Use only these figures; do not calculate " & _
                  "new ones. Mention the largest increase and largest decrease. " & _
                  "Plain business English.", inputText)

    db.Execute "INSERT INTO tblReportSummaries " & _
        "(ReportName, PeriodStart, PeriodEnd, InputText, DraftText, Status) VALUES (" & _
        "'rptMonthlySales', #" & Format(pStart, "yyyy-mm-dd") & "#, #" & _
        Format(pEnd, "yyyy-mm-dd") & "#, '" & Replace(inputText, "'", "''") & "', '" & _
        Replace(draft, "'", "''") & "', 'Draft')", dbFailOnError

    DoCmd.OpenForm "frmApproveSummary", , , "ReportName='rptMonthlySales' AND " & _
        "PeriodStart=#" & Format(pStart, "yyyy-mm-dd") & "#"
    Exit Sub
Fail:
    MsgBox "Could not draft summary: " & Err.Description, vbExclamation
End Sub

Step 4: Approve, Then Display

• frmApproveSummary shows InputText (read-only), DraftText, and an editable ApprovedText pre-filled with the draft.

• An Approve button sets Status, ApprovedBy and ApprovedOn.

• In rptMonthlySales, add a text box in the Report Header with Control Source =DLookup("ApprovedText","tblReportSummaries","ReportName='rptMonthlySales' AND PeriodStart=#" & Format([Forms]![frmReportMenu]![txtStart],"yyyy-mm-dd") & "# AND Status='Approved'").

• Set Can Grow = Yes so the box expands to fit.

• Add a small line under it: AI-drafted, approved by [ApprovedBy] on [ApprovedOn].

Exporting and Emailing the Report

DoCmd.OutputTo acOutputReport, "rptMonthlySales", acFormatPDF, _
    "C:\Reports\Monthly Sales " & Format(Date, "yyyy-mm") & ".pdf", False

Reports That Benefit Most

Guardrails

• Aggregates only: never send detail rows or personal data for a summary.

• Same source query for summary and report.

• Human approval before printing or sending.

• Store input, draft and approved text so you can show how a statement was produced.