Need a summary count of records - needs a union of two tables?

Mahesh
Participant V

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:

========================
Position1

 Member1    0  *ideally this view should show 0 when there is no Member-Position for that member*
 Member2    3
 Member3    5

Position2

 Member1    2  
 Member2    5
 Member3    0

Possible?

0 7 514
7 REPLIES 7

LeventK
Participant V

@Mahesh
If you group your Member-Positions table as per [Position] column and use a column aggregate of COUNT than you can have the view you want. It will look like:

Position1 (2)

 Member2    3
 Member3    5

Position2 (3)

 Member1    2  
 Member2    5
 Member3    0

Thanks for thinking along and suggestion @LeventK.

Letโ€™s say Member1 never had held Position1. So, like your example, there will not be an entry for Member1 under Position1. That is not ideal.

What is ideal is every member shows under each position, and show value of 0 if they did not ever hold that position.

Would that work?

@Mahesh
I have copied and pasted it from your post, so it might be a copy & paste error, sorry. If you have COUNT column for each memberโ€™s position, than it will surely display a 0 (zero) count for that aggregate.

@LeventK, I am sorry but I do not understand.
We are grouping Member-Position records by Position and showing a count, correct?

If so, the count will be done on number of Member-Position records, right?

So if a member has never had that position, that member will not even show for that position.
In other words, one wonโ€™t even see a record with count 0 for that Member for that Position.
For this to work, there should be at least once that Member to have had that Position.

Sorry for not understanding your solution.

@Mahesh
The best grouping you can get is like this. Click on your name on the Home screen.

Thanks for putting together a sample @LeventK
I like how you have leverage the Group Aggregate to do the count.
I did not realize one could have the group aggregate happen at more than the top level.
In this case you have it showing on Position (your screenshot) and it shows the next lower level i.e. for Member level. I am wondering how you achieved it
I notice you have Member_No twice (see red box in screenshot). Is that the trick for getting count at Member level (the successive level) ?

@Mahesh
You can copy the Community Samples app from my portfolio here > https://www.appsheet.com/portfolio/245151
After copying checkout the MemberList Table & View

Top Labels in this Space