Expert guide
Use ChatGPT to Write MS Access SQL Queries
Access SQL has its own dialect. Tell the AI, give it your schema, and it gets it right.
Call us at +1 (385) 386-3860
Short answer: start every prompt with "Microsoft Access SQL (ACE)", paste a short schema of the tables and fields involved, and describe the result you want with one example. Ask for square brackets around field names, Access-style nested joins, # date literals and * wildcards. Paste the SQL into Query Design → SQL View and test it. For staff who need answers without SQL at all, DatabaseAI turns plain-English questions into read-only Access queries.
Microsoft Access runs thousands of small and mid-sized businesses, and most of the people using those databases have never written SQL. AI assistants can close that gap, but only if you know one thing: the SQL they write by default is not Access SQL. Paste a SQL Server query into Access and you get "Syntax error in FROM clause" or a query that silently returns nothing.
Access SQL vs Standard SQL: What AI Gets Wrong
| Feature | Access SQL | SQL Server / standard |
|---|---|---|
| Wildcards in LIKE | * and ? | % and _ |
| Date literals | #2026-11-01# | '2026-11-01' |
| Conditional value | IIf(condition, a, b) or Switch() | CASE WHEN ... END |
| Null replacement | Nz(field, 0) | ISNULL / COALESCE |
| Three or more joins | Parentheses around each join pair | No parentheses needed |
| String concatenation | & (or +) | + or CONCAT |
| Top rows | SELECT TOP 10 (ties included) | TOP / LIMIT / FETCH |
| Current date | Date() or Now() | GETDATE() / CURRENT_DATE |
| Cross-tab | TRANSFORM ... PIVOT | PIVOT operator or conditional aggregation |
Step 1: Give the AI Your Schema
The AI cannot see your database, so describe it. You only need the tables involved in the question, their fields and how they relate.
Microsoft Access SQL (ACE). Tables:
tblCustomers: CustomerID (AutoNumber, PK), CompanyName (Text), Region (Text),
CreatedOn (Date/Time)
tblOrders: OrderID (AutoNumber, PK), CustomerID (Number, FK → tblCustomers),
OrderDate (Date/Time), Status (Text: Open/Shipped/Cancelled)
tblOrderLines: LineID (PK), OrderID (FK → tblOrders), ProductID (FK),
Qty (Number), UnitPrice (Currency)
tblProducts: ProductID (PK), ProductName (Text), Category (Text)
Rules: wrap every field in [square brackets], use Access-style
parentheses for multiple joins, # for dates, * for wildcards.Database Tools → Database Documenter produces a full report of tables and fields if you do not want to type it. Trim it to names and types; leave out any data.
Step 2: Ask for the Result, With an Example
Write a SELECT query that returns, for each Region, the total sales value (Qty × UnitPrice) of shipped orders in 2026, highest first. Example row: West | 184,250.00
A correct Access answer looks like this. Note the nested joins:
SELECT c.[Region], SUM(l.[Qty] * l.[UnitPrice]) AS [SalesValue]
FROM ([tblCustomers] AS c
INNER JOIN [tblOrders] AS o ON c.[CustomerID] = o.[CustomerID])
INNER JOIN [tblOrderLines] AS l ON o.[OrderID] = l.[OrderID]
WHERE o.[Status] = "Shipped"
AND o.[OrderDate] >= #2026-01-01# AND o.[OrderDate] < #2027-01-01#
GROUP BY c.[Region]
ORDER BY SUM(l.[Qty] * l.[UnitPrice]) DESC;Using a half-open date range (>= start and < next start) avoids missing orders entered with a time on 31 December, a classic Access mistake when people write BETWEEN #2026-01-01# AND #2026-12-31#.
More Worked Examples
Customers with no orders in the last 12 months
SELECT c.[CustomerID], c.[CompanyName]
FROM [tblCustomers] AS c
WHERE NOT EXISTS (
SELECT 1 FROM [tblOrders] AS o
WHERE o.[CustomerID] = c.[CustomerID]
AND o.[OrderDate] >= DateAdd("m", -12, Date())
);Order status label with IIf and Nz
SELECT o.[OrderID],
IIf(o.[Status] = "Open" AND o.[OrderDate] < Date() - 14,
"Overdue", o.[Status]) AS [StatusLabel],
Nz(DSum("[Qty]*[UnitPrice]", "tblOrderLines",
"[OrderID]=" & o.[OrderID]), 0) AS [OrderValue]
FROM [tblOrders] AS o;DSum works but is slow on large tables because it runs once per row. Ask the AI to rewrite domain functions as joins or subqueries when a query takes more than a few seconds; see faster alternatives to DLookup.
Monthly sales cross-tab by category
TRANSFORM SUM(l.[Qty] * l.[UnitPrice]) AS [Sales]
SELECT p.[Category]
FROM ([tblOrders] AS o
INNER JOIN [tblOrderLines] AS l ON o.[OrderID] = l.[OrderID])
INNER JOIN [tblProducts] AS p ON l.[ProductID] = p.[ProductID]
WHERE o.[OrderDate] >= #2026-01-01#
GROUP BY p.[Category]
PIVOT Format(o.[OrderDate], "yyyy-mm");Safe update query
UPDATE [tblOrders] SET [Status] = "Cancelled" WHERE [Status] = "Open" AND [OrderDate] < #2025-01-01#;
Before running any UPDATE or DELETE from AI, change it to a SELECT with the same WHERE clause and check the rows it returns. Back up the back-end file first. Action queries in Access cannot be undone.
Fixing the Errors AI Queries Hit Most
| Access error | Likely cause | Fix |
|---|---|---|
| Syntax error in FROM clause | Missing join parentheses, or reserved word as table name | Nest joins; bracket names |
| Enter Parameter Value | A field name is misspelled or does not exist | Check spelling against the table |
| Data type mismatch in criteria expression | Quotes around a number, or date in quotes instead of # | Match the literal to the field type |
| You tried to execute a query that does not include the specified expression as part of an aggregate function | A selected field is not in GROUP BY | Add it to GROUP BY or aggregate it |
| Query too complex | Too many nested IIf or unions | Split into saved queries; see our guide |
| Returns no rows with LIKE | % wildcard used | Use * |
From AI Queries to a Better Database
If the same questions get asked every week, save the queries with clear names and build them into a report or a form with filters, as covered by our Access reports and forms service. If queries are slow, the structure needs attention: indexes, normalisation, or a move of the back end to SQL Server. An Access consultant can review it. For writing VBA rather than SQL, read using AI for Access VBA.
FAQ
ChatGPT and Access SQL FAQ
Syntax, errors and safer ways to query