We help IT Professionals succeed at work.

Microsoft Access: Creating a payment system.


I am creating a database for a sports club and our members can pay in the following ways:

1. Per visit.
2. For one month.
3. 3 month contract.
4. 6 months contract.
5. 12 months contract.

What I need to achieve:

1. To be able to see what contract a client is using.
2. To see if they have payed for the latest month or if the are in debt for 1+ months.
3. How many months are left to go on their contract.
4. Input monthly payments (Date, type (cash, credit card, direct debit etc.))
5. See if they have an active contract or if it has finished already.

So far I've got tables for:

Clients. (Client information)
Clubs. (Data for the 2 clubs that we have right now)
Contracts. (Type of contract, start date, end date, fee, active or not)
Payment. (Payment Date, Payment Fee, Payment Type)
Purchase type. (1 time card, 1 month card, 3 months contract, 6months contract. etc.)

Now then, I am not sure if this design is ok, and how should I tie this together in order for the system to work properly?

Best Regards,

Watch Question

Sounds like you're on the right track.  

The best way to come at any software project is to establish what's wanted, prioritize (what is actually needed, what will give value quickly, what is clear, what is not dependent on anything else), then do the least amount of work possible to show the client something of value that they can use.

In this case, if it was me, I'd probably do thing in this order:
1. Create a bunch of blank forms that will be placeholders for the applications major areas of work.
2. Create a switchboard to give access to all these forms, either by hand or using the Switchboard Wizard.
Demo this to your clients so they can get a feel if you understand what they need.
3. Client information - straightforward.
4. Club information - again straightforward.
Demo it again, maybe even deliver a first version (if you're confidant about supplying updates without trashing their existing data - a whole other topic!)
5. Now start on the Contracts, Payments, and Purchase Types as they are all linked.  You'll need to create look-up entry forms for the Purchase Types and Contract Types, and data entry for Payment.

You may need to think about this: what if Payment Types and Contract Types change over time?  Do they negotiate exceptions or cut special deals with folks?  

Don't get into making up possibly useful stuff (that's called "gold-plating") but do make sure you talk to them about the odd exceptions to the general rules - and deal with those exceptions first.  In most clients minds, something they don't do very often isn't something that's important.  In your mind, as a developer, it's those edge cases that can crash the whole system or lead to something having to be recorded outside the system and the client ends up hating it!  

Suppose the club chairman wants to offer a club sponsor a half-price deal for their family.  Rare, but a disaster if the system can't handle it as they might lose the sponsor!  Keep asking questions about things like this.  It's happened to me too often, someone says after it's been delivered "You know, we should have told you about that odd time when blah blah happens" or "I know we said that it always happens like this, but actually once in a blue moon we have to do that" and your entire architecture was designed around "this" not "this and that"!

So, use the easy quickly delivered stuff to open up your clients.  People often literally don't know what they do at work, and won't know how they need your system to operate, so work WITH that, not against it.  Give them little bits of work to look at, and it will jog their memory, and have them realize what they actually need, not what they thought they wanted.

I've worked on 60-odd projects now, and the ones where it was all designed up front, then built to spec and delivered ALL failed!  The ones where we built and delivered in really short bursts ALL succeeded - even though we often didn't give the client what the client originally thought they wanted!

Analyze, prioritize, design, propose, get acceptance, build, deliver, get feedback, repeat until the budget is reached, the budget is extended, or the client gets enough to say "stop".

Does that all make sense?



Thank you for your reply. As it happens, I am actually the sports club owner :), could barely call myself a developer. However I have built Access databases previously, although I could never make them work if I tried to normalize them. Due to the fact that I am building it for myself, you have very correctly noted that sometimes we do make special offers, free passes etc. The problem is that at the moment we are using a simple Excel Table for all our client management, which makes it EXTREMELY difficult to manage and organize anything. Especially to look for debtors, get their contact details, and make our employees call them. Thus I wanted to create a database, the main problem with it is that I am not sure how to track the payments, payment subscriptions and deals. Not sure how to set-up the fact that a client has payed a fee or missed it this month etc. If you could help me out a little or tell me what to read, I would be very thankful.
Well, one thing that might be an idea is to use the new database wizard in Access itself to see if any of the existing template database will help you out.

There's a template on Microsoft's website at http://office.microsoft.com/en-ca/templates/club-membership-database-TC001047743.aspx that you might want to look at.

Lastly, just Google for "club membership database" and see what's out there.  If your goal is to learn how to program in Access, then that's one thing.  If it's to handle the club membership, then don't go inventing any wheels!

(If it IS to learn Access, then check http://msdn.microsoft.com/en-us/office/dd547097.aspx.  My favorite books were a set: the Access Developer's Handbook (Desktop), Access Enterprise Developer's Handbook, and VBA Developer's Handbook.  They're for older editions of Access but the principles they cover are still relevant and solid.)

I've spent many years developing with Access, and my personal nightmare is dealing with a client I like, but who doesn't understand that they're asking me to take on a three month project to design and build a reliable system for them.  On one hand, they're used to going to Best Buy and getting Microsoft Office for a few hundred bucks in all it's glory, and on the other hand there's me telling them I'm going to charge them $25,000 to create their "little" application!  Ow!

I'm not ripping them off: I've got to analyze the business problem, design a solution, apply all the years of studying database theory, apply all my experience in the trenches, help them avoid pitfalls they won't have thought about, do so incrementally to make sure I don't do any redundant work, AND pay extortionate professional liability insurance!

So I'm available for specific questions, but general guidance is a minefield as it can step into a major commitment that I'm just not available to make.  

I hope that all makes sense.


Thank you very much,

The resources you have provided were very helpful!