I have a table of club members. Lets call it Members
I have another table of positions in the club. Lets called it Positions.
As you can imagine, the above two tables are largely static i.e. they rarely change
I have a third table that lists the positions which members have volunteered for, now and in prior years… Let’s called this Member-Positions table. So this table is keyed by Member ID, Position and Date.
As you can imagine, overtime we will have new records getting added to this table.
I would like to generate a view so one would know for each position how many time a member has volunteered. Let’s say there are 2 positions, Position1and 2
3 members, Member1, 2 and 3.
I would like to create a view that will present user the following information. I believe it would need to use Member-Position table but not sure this count will work as expected:
Member1 0 *ideally this view should show 0 when there is no Member-Position for that member* Member2 3 Member3 5
Member1 2 Member2 5 Member3 0