Membership Database with Reporting

Hi @OCE_PTO - I think the answer to getting the data you want is going to come from the base design. If I was building this, I would think in terms of different “things” I want to track. I would have tables for:

• People
• Dues
• Donations

Students, Parents and Teachers are all essentially the same thing - people or members - just each with a different type, so I would have these in the same table:

Arguably, Dues and Donations are the same things - financial transactions - and could be dealt with in the same table, but presumably, dues are regular, subscription-type payments and donations are more one-off, so there are differences and it might serve you to track these separately.

Have a look at this simplified version showing people and dues:

Dues can be logged and each payment linked to a person:

Back in the People table, with a rolloup, you can see the total dues and with a bit of formula work, you can see the dues by type:

There are lots of possibilities to report by person type - a grouped report on the Dues table or just the columns in the People table. Your dues table has a date field, so you could easily form a month/year field from this and report on dues by month/year.

You could set-up donations as its own table - essentially a copy of Dues, perhaps with some different information and link this to People in the same way. In the People table you could then see Total Dues, Total Donations and, summing the two, Total Contribution.

Hope this helps

JB

Thanks JB - I actually figured this kind of solution would be the first response. Unfortunately, because of the many-to-many relationships involved, I don’t think this will work for me.

You see, to track membership by class, I have to associate teachers with students. So the Teachers table that links to Students, that contains all students in his or her class. However, students also have to be associated with their parents. So the Parents table has a column that links to Students as well, and associates each family member with one or more students. Each student also may have one or more family members.

Here’s the thing - some teachers are also parents. So if I combined them all into one table, not only would I lose the ability to link students to parents and students to teachers, even if I kept students separately, having teachers and parents in the same table would mean that a teacher’s child(ren) would also get included her class incorrectly, as there would be no way to differentiate a teacher relationship vs. a parent relationship to a child.

At least, that’s how I understand it. Feel free to correct me if I’m wrong!

@OCE_PTO

My initial version for PTO-data.
It has many tables.
Link to the base is as following.

Rollup fields need to be created.

Neal

Hi @OCE_PTO - I’ve made a few changes to the base - see if this works for you.

I’ve added some new views for students, parents and teachers:

I’ve made “type” a multi-select field so that a person can be a parent and teacher at the same time. I’ve also added two link fields - self-referencing links to the people table - that allow you to link one person with another. This pattern is often used where you have a thing of one type (person) but there is some sort of hierarchy, e.g. student/teacher, team manager/team member.

So, to see Teacher Jane’s pupils, I can just filter where teacher = Jane and this won’t include the children she is a parent of (unless she is the teacher and the parent of course)

The parent and teacher link fields use the parent and teacher views so when selecting a teacher, for example, you only see teachers.

Not sure if this will work for you as how you want to report on the data might influence the table structure, but this is the “textbook” way of linking objects of the same type.

JB