Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 36 ยท Projects and Practice

Access Practice Set and Troubleshooting

Test yourself with a practice set for Microsoft Access: SQL exercises with solutions, Access SQL versus standard SQL, design questions, common errors and a checklist.

This final lesson is a workshop. First a runnable SQL practice set with solutions, then the main differences between Access SQL and standard SQL, a design review exercise, a table of common errors and fixes, and a checklist for finishing an application. Try each exercise before you open the solution.

The SQL exercises use the same small employees / departments / projects data. Because Access itself is not available on this site, solutions are written in standard SQL that SQLite runs; the Access spelling is shown next to anything that differs.

Exercise data

SQL Load the practice data

Press Run (or Ctrl + Enter) to see the result here.

SQL practice questions

Write a query for each, then compare with the solutions below.

  1. List employees earning more than 5000, highest first.
  2. Count employees per department, including departments with no employees.
  3. Show each employee with their department name; employees without a department must still appear (label them "(none)").
  4. Which departments have an average salary above 5500?
  5. Who earns more than the average salary of their own department?
  6. List every employee with their manager's name (managers may be missing).
  7. Total hours per project, with the project title, for projects that have at least 100 hours.
  8. Employees hired in 2022 or later with their years of service (as of 2025-03-10).
SQL Solutions 1 to 4

Press Run (or Ctrl + Enter) to see the result here.

SQL Solutions 5 to 8

Press Run (or Ctrl + Enter) to see the result here.

Access SQL versus standard SQL

Access's query language (the Jet/ACE SQL dialect) is close to ANSI SQL, but you will trip over these differences. They are shown here as read-only references because the site's runner uses SQLite.

Task Access SQL Standard / SQLite
Wildcard in LIKE Like "Sm*", ? one character, # one digit LIKE 'Sm%', _ one character
String quotes double quotes "text" (single also works) single quotes 'text'
Date literal #2025-03-10# '2025-03-10'
Today Date(), Now() CURRENT_DATE, date('now')
If/else IIf(cond, a, b) CASE WHEN cond THEN a ELSE b END
Null replacement Nz(x, 0) COALESCE(x, 0)
Concatenate & (and +) ||
Boolean True = -1, False = 0 1 / 0
Top rows SELECT TOP 5 ... LIMIT 5
Pivot TRANSFORM Sum(x) SELECT ... PIVOT col CASE + SUM
Multiple joins nested parentheses required no parentheses
Subquery as a table needs an alias needs an alias
ALTER TABLE etc. limited DDL, field types TEXT(50), LONG, CURRENCY many more types
Full outer join not supported (use UNION of left and right joins) supported in most servers
Comments none in the editor -- and /* */

The crosstab query is Access's famous extra. TRANSFORM turns row values into columns (this is read-only Access SQL):

TEXT
TRANSFORM Sum(Amount) AS Total
SELECT Region
FROM Sales
GROUP BY Region
PIVOT Format([SaleDate], "mmm");

You can produce the same shape in standard SQL with conditional aggregation, which you can run:

SQL Stand-in for a crosstab: CASE with SUM

Press Run (or Ctrl + Enter) to see the result here.

Design review exercise

A manager gives you this spreadsheet columns: Student, Course1, Grade1, Course2, Grade2, Course3, Grade3, Teacher, TeacherPhone. Without opening Access, list: (a) the 1NF violation, (b) the 3NF violation, (c) the tables you would create, (d) the keys. A suggested answer: the repeating CourseN/GradeN groups break 1NF; TeacherPhone depends on Teacher, not on the student, a transitive dependency. Tables: Students, Courses (with a TeacherID), Teachers (phone), and the junction Enrolments (StudentID, CourseID, Grade) with the composite key. Compare with normalization.

Common errors and fixes

Message or symptom Likely cause Fix
"Too few parameters. Expected 1." (3061) a misspelled field or control name in SQL, so Access treats it as a parameter check names in the query and the form control references
"Data type mismatch in criteria expression" (3464) comparing text with a number, or a missing quote or # around a value quote text, hash dates, convert with CLng/CDate
"The changes you requested ... duplicate values in the index" (3022) unique index or primary key violated check the data; use a find-duplicates query
"You cannot add or change a record because a related record is required in ..." (3201) referential integrity: no matching parent add the parent first or choose an existing one
"Could not delete from specified tables" (3086) related records exist, or a query joins several tables delete child rows first; set Unique Records to Yes
"Operation must use an updateable query" (3073) the query has a join, totals or distinct that makes it read-only base the form on a simpler query
"Write conflict" another user saved the same record choose Save, Copy or Drop; see multi-user
"Invalid use of Null" (94) a Null assigned to a String or Long variable Nz() or Variant variables
"Object variable not set" (91) a recordset or object used before Set assign it or test Is Nothing
Query is slow linked tables, missing indexes, domain functions in queries index join fields, filter early, use pass-through
#Name? in a control the control source refers to a missing field or function fix the spelling or the module name
Form shows #Deleted the linked row was deleted, or a missing rowversion on a server table requery; add rowversion (upsizing)

A quick way to diagnose "Data type mismatch" is to search the data itself for rows that cannot convert. This runnable query lists non-numeric text in a column that you intend to treat as numbers:

SQL Find the row that breaks a number conversion

Press Run (or Ctrl + Enter) to see the result here.

Finish-line checklist

  • Tables normalized, keys and relationships with integrity, validation rules in the tables.
  • Queries named and saved; no hard-coded dates or user names; parameters where users choose values.
  • Forms bound to filtered queries; error handling and a central error log in every procedure that touches data.
  • Front end split from the back end, ACCDE delivered from a trusted location, back end encrypted if needed.
  • Backups scheduled and restored once in a test, compact scheduled, a version table in place.
  • A short document for the next person: table list, relationships diagram, how to deploy a new front end.

You now have the full path from a first table to a deployed, multi-user, maintainable Access application. Return to the Microsoft Access track to revisit any chapter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which Access function replaces SQL's COALESCE?

    Nz(value, default) returns the default when the value is Null.
  2. 2. Which wildcard matches any number of characters in Access LIKE?

    Access uses * (and ? for one character); standard SQL uses % and _.
  3. 3. A 'Too few parameters. Expected 1.' error almost always means:

    Access treats an unknown name as a parameter to prompt for, then fails when none is supplied.
  4. 4. How do you get a crosstab layout in standard SQL?

    SUM(CASE WHEN ... THEN x ELSE 0 END) per column reproduces a crosstab.

Summary

  • Practice the core patterns: filter and sort, grouped counts with outer joins, HAVING, correlated subqueries, self joins and conditional aggregation.
  • Know the Access dialect: * wildcards, IIf, Nz, # dates, TOP, TRANSFORM, nested join parentheses.
  • Recognize common errors by number and message, and fix the cause rather than suppressing the error.
  • Use the checklist before shipping: design, integrity, split, security, backup and documentation.

Next: Back to the Microsoft Access track