Expert guide
Law Firm Matter Tracking in Microsoft Access
Matters, parties, conflicts and deadlines in one database your team can trust
Call us at +1 (385) 386-3860
Short answer: a small-firm matter database in Access needs related tables for Clients, Matters, Parties (with roles), KeyDates, Tasks, TimeEntries and Document links. Build a conflict search across clients and parties, a deadlines view sorted by days remaining with responsible lawyer, and a matter dashboard. Keep documents in your file system or document management system and link them. For billing, pair it with the law firm billing spreadsheet or build billing into the database.
Small law firms often run on a mix of a matter spreadsheet, a calendar, Outlook folders and memory. It works until a deadline is missed, a conflict is spotted late, or a partner asks "what is open for this client?" and nobody can answer in less than an hour. A structured matter database fixes the fundamentals without the cost and complexity of a full practice management platform.
The Core Tables
| Table | Key fields | Notes |
|---|---|---|
| tblClients | ClientID, Name, Type (person/organisation), Contact details | One row per client, ever |
| tblMatters | MatterID, MatterNo, ClientID, Title, PracticeArea, ResponsibleLawyer, OpenedOn, Status, ClosedOn | Matter number generated, e.g. 2026-0142 |
| tblParties | PartyID, MatterID, Name, Role, Organisation | Opposing parties, counsel, witnesses, related entities |
| tblKeyDates | KeyDateID, MatterID, DateType, DueDate, Responsible, Completed | Court dates, filing deadlines, limitation dates |
| tblTasks | TaskID, MatterID, Description, AssignedTo, DueDate, Status | Day-to-day work |
| tblTime | TimeID, MatterID, Timekeeper, WorkDate, Hours, Narrative, Billable | Feeds billing |
| tblDocuments | DocID, MatterID, Title, FilePath, AddedOn | Links only; never store files in Access |
Conflict Search
The most valuable query in the database. Search both clients and parties, and show context so the reviewer can judge.
PARAMETERS [Search name] Text ( 255 ); SELECT "Client" AS Source, c.Name, m.MatterNo, m.Title, "Client" AS Role, m.Status FROM tblClients AS c INNER JOIN tblMatters AS m ON c.ClientID = m.ClientID WHERE c.Name Like "*" & [Search name] & "*" UNION ALL SELECT "Party", p.Name, m.MatterNo, m.Title, p.Role, m.Status FROM tblParties AS p INNER JOIN tblMatters AS m ON p.MatterID = m.MatterID WHERE p.Name Like "*" & [Search name] & "*" OR p.Organisation Like "*" & [Search name] & "*" ORDER BY 3;
Wrap it in a form with a search box, a results list and a "Record this check" button that saves who searched, for which name and for which prospective matter. That record is evidence the check was done.
Name variations
Search short distinctive fragments ("Hendrick" rather than "Hendricks Holdings LLC").
Search former names, trading names and key individuals of organisations.
AI can suggest variations to search, such as initials, spelling variants and parent companies; a person runs and reviews every search.
Deadline Tracking
qryUpcomingDeadlines:
SELECT k.DueDate, k.DateType, m.MatterNo, m.Title, k.Responsible,
DateDiff("d", Date(), k.DueDate) AS DaysLeft
FROM tblKeyDates AS k INNER JOIN tblMatters AS m ON k.MatterID = m.MatterID
WHERE k.Completed = False AND m.Status = "Open"
AND k.DueDate <= Date() + 30
ORDER BY k.DueDate;Show this list on the database's start screen, filtered to the current user by default.
Colour by DaysLeft: overdue, within 7 days, within 30.
Email each lawyer a weekly digest of their next 30 days (Outlook automation from Access).
Require a second person to mark critical dates complete.
Deadline calculation rules, such as court rules and limitation periods, are legal judgements. The database tracks the dates your lawyers enter and reminds; it does not calculate legal deadlines unless rules have been designed and verified by the firm.
Matter Dashboard
| View | Question it answers |
|---|---|
| Open matters by lawyer and practice area | Who is carrying what? |
| Matters with no activity in 60 days | What has stalled? |
| Unbilled time by matter | What should be invoiced? |
| Upcoming key dates | What is at risk this month? |
| New matters per month by source | Where is work coming from? |
Time and Billing
Time entries can feed invoices directly from Access reports, or be exported to the law firm billing spreadsheet. Keep rates in a timekeeper rates table with effective dates so historic invoices remain correct when rates change.
AI, Confidentiality and the Firm's Obligations
Summarising matter notes
Turn months of notes into a status summary for a partner review.
Extracting dates from correspondence
Suggest key dates found in letters for a lawyer to confirm and enter.
Drafting routine client updates
From structured status fields, for a lawyer to edit.
Client confidentiality obligations apply to any AI use. Use only tools approved by the firm, avoid sending privileged content to consumer services, and consider a private local model for sensitive work. The integration itself is described in connecting Access to an AI API.
Build Notes That Save Pain Later
Split the database: front end per user, back end on the server; see our splitting guide.
Use AutoNumber keys internally and a separate human-readable matter number.
Never delete matters; close them, with a closed date and reason.
Log who changed key dates and when.
Back up the back end nightly and test a restore.
Splitting is covered in how to split an Access database. We build matter databases like this as fixed-scope custom Access database projects, and an Access consultant can review an existing system for gaps.
FAQ
Law firm matter tracking FAQ
Tables, conflicts, deadlines and AI