Invoicing/membership list

New Contributor

I have a group that I work with that used to have their membership list in Access, but the database is now corrupt and old. I had migrated them to QuickBooks several years ago, but am thinking of moving them back to Access because QuickBooks doesn't provide the reporting they want without a lot of fussing. I don't do their financials, so there's really no need to use QuickBooks, we're really only using it as a database, but when I did this we were using very old and outdated Access software and... well, that's just what happened.

The structure is that there are organizations, and each organization has multiple people. Each year the people pay dues, at a flat rate per person. So I'm looking for a way to create a form that would pull ALL the people with organization A into one printable document, all the people with organization B into another printable document, etc. 

I'm also a little torn between having one big table or breaking it up into smaller bites, with a table for the members, a table for the organization, a table for the co-op (groups of organizations), and tables based on the year of membership. The old database was done that way. 

Looking for any advice, starting points, etc. Thanks.

8 Replies

Hi @jpellowe 

I think there are few options; 

  • Import from outdated Access into new MS Access;
    • Create a new blank database in Access
    • use Import feature to import all objects from the older Access database. Not sure how its corrupted, maybe there is a chance to import some or all objects
    • if you can access to QuickBooks (QB), you can flush all tables and import the recent data from QB. You will need the Qodbc driver from QB website, I think its free for read-only access) for accessing data in QB tables,
    • If you cannot access QB,  you may continue with existing data
    • star making changes 
  • download Charity Contributions template, free @Microsoft site, see if it helps
  • or download Membership Tracking Templates template from Access-templates site, see if it helps

For the structure, I think you need following tables

  • organizations; fields OrgID (PK), name, etc
  • people, PeopleID (PK), Full name, Address etc.
  • Orgs_People (intersection table); OrgPeopleID (PK), OrgID, PeopleID, Inactive (Y/N)
  • Dues (child table); OrgPeopleID, Years, PayDate, Amount

This structure supports many-to-many; meaning, one person might be a member of multiple organizations. I hope that is helpful. 

@jpellowe ,,

 

  1. First you need to create a Table in the Database, should comprises fields like,,

Member ID

Name

Organization

City

Member's type

Membership Fee

Fee paid Date

Cell No

Mail ID

 

     2. Then you need a user form for data entry into the Table.

     3.  After that using the Member's  Table, you can create Query to filter records, like Members belongs to which organization, Member's Type,, Year and others.

    4. Finally Report for Invoice. 

@jpellowe 

First things first. A relational database application is neither a spreadsheet (like that one big table you mention would be) nor is it an invoicing system like Quickbooks. Access is a development tool to create relational database applications--and that is exactly what you need here. Access can, of course, incorporate both the membership rosters AND a module to manage invoicing, although that invoicing function probably won't be as sophisticated as Quickbooks. It will be adequate, though.

 

That means the starting point is to invest sufficient time and energy into learning how relational database applications are created. Then you can apply those principles to your own situation.

 

Let's start with the three basic components, or layers of a relational database application.

 

  1. A Data Layer: This is the component of a relational database application in which all data is stored. In Access this consists of the tables. Multiple tables. A spreadsheet, in contrast, is one big ugly messy "table-like" structure. 
    Each table is part of a structured relationship between the entities stored in the tables. For example, one table will be "Member". Another table "Organization". The relationships here are
    One Member belongs to Many Organizations.
    AND
    One Organization has Many Members. 

    Here you have what is called a "Many-to-Many" relationship. It requires a third table, which stores the links, or relationships between the members and the organizations to which they belong. 
    NOTE: even if a member only belongs to a single organization at any point in time, this "Many-to-Many" relationship holds because, I assume, members can join different organizations at different times, or at the same time.
    If that does NOT describe your situation, of course, you'll adopt different rules. However, given the very limited amount of information available to us, that's the first assumption.

    Go learn about "Normalization", which is the process by which we design and build a set of tables for a relational database application.
  2. An Interface Layer: This consists of the forms and reports you custom create for YOUR situation. Users interact with their data through the forms and reports, and ONLY through them, never directly with tables.
  3. A Logic Layer: This is the VBA and macros you write to manage and manipulate your data and your interface.

    Access provides tools to custom create all three components needed for a valid relational database application. 

Again, start by learning what Normalization is and how to apply it. Create tables for the Entities, or things, you need to track and designate the relationships between and among those tables. From there, the interface and logic will grow.

@jpellowe 

I just re-read your original post and realized I overlooked a crucial detail. You mention multiple tables for each year for memberships. That is not the way it should be done. Memberships should be tracked in a table with a FIELD IN THAT TABLE to designate "MembershipYear". 

@George Hepworth Thank you for that information. I'm unsure how to build the report that would pull in all the members for each organization. One organization might only have one member, and one might have 10. 

@jpellowe 

Let's start with the tables. That's the foundation. All else depends on that.

Can you show us what you have, and even better some anonymized sample data for it?

@George Hepworth I'm so sorry, I had at least drafted a response to this but apparently never sent it. 

 

I'm attaching a spreadsheet that shows the data that I'm looking for. I was able to get the corrupted file to open, and there are 30+ forms and an 30+ tables, which seems like way too many for the kind of data I'm tracking, which is really pretty simple. Who are you, what organization do you belong to, what cooperative does that organization belong to, and have you paid your dues? And then of course the corresponding forms and reports to do the invoicing. 

@jpellowe 

No problem. I started on a sample database and got sidetracked, so it's kind of my fault too,