Nov 20 2020 01:23 PM
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.
Nov 20 2020 07:18 PM - edited Nov 23 2020 08:58 PM
Hi @jpellowe
I think there are few options;
For the structure, I think you need following tables
This structure supports many-to-many; meaning, one person might be a member of multiple organizations. I hope that is helpful.
Nov 21 2020 01:00 AM
@jpellowe ,,
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.
Nov 21 2020 06:23 AM - edited Nov 21 2020 06:25 AM
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.
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.
Nov 21 2020 06:27 AM
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".
Nov 23 2020 05:38 AM
@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.
Nov 23 2020 09:54 AM
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?
Dec 10 2020 11:49 AM
@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.
Dec 10 2020 01:17 PM
No problem. I started on a sample database and got sidetracked, so it's kind of my fault too,