Stop Storing Payments by Month in Microsoft Access: The Beginner Table Mistake That Breaks Reports

If you started your billing database in Microsoft Access like an Excel spreadsheet, you're not alone... but you're probably making reports WAY harder than they need to be. In this week’s TechHelp Quick Queries, I cover a classic beginner mistake (and a simple upgrade that makes your whole database smarter).

The problem being addressed:
One of my students built a billing system where each customer record has separate fields for January, February, March, etc. It looks clean at first, but the moment you want to answer a basic business question like "How much money did we collect today?" everything starts falling apart.

Why common solutions fail:
When you store repeating monthly payments as separate fields, every new need creates more clutter: a paid flag for each month, then a date paid for each month, then an amount paid for each month... and suddenly one table record turns into a monster. Reporting becomes painful because you have to check a dozen different fields just to total deposits for one day. And when the next year rolls around? Now you're duplicating customer data just to start over with another set of month fields.

The practical solution presented:
The better approach is using a related payments table. Instead of one record holding twelve months of payment fields, you store each payment as its own record with the customer ID, payment date, and amount paid (and optionally what billing period the payment applies to). This makes end-of-day reports simple, lets you query payments by date instantly, and keeps your database scalable without redesigning everything every January.

Who the content is for:
This one is for beginners (especially anyone transitioning from Excel to Access) who are building billing, membership, or subscription databases. If you've ever created one table and started adding "JanPaid, FebPaid, MarPaid..." columns, this video will save you a lot of future headaches.

What is coming next:
I also answer a bunch of other great questions in this episode, including tabbed documents vs overlapping windows, Power Query merge performance compared to Access joins, combo box list caching, fixing report footer gaps, hiding tables with VBA (and why it is not real security), and whether Access is still worth learning in 2026. Lots of good stuff in this Quick Queries!

Live long and prosper,
RR