Jump to content

Count of related records, sort of


RSGC

This topic is 579 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I put this in Relationships which seems close to the right place, but I could be wrong. 

 

I am not sure how to describe this accurately, let alone search for a previous discussion . . . happy to be pointed to an existing thread.

I have a three table instances, two tables involved.

Table: EVENTS with fields:

              ID

              PrimaryParticipant_ID

              SecondaryParticipant_ID

              Points

 

Table: PARTICIPANTS with Fields

              ID

              MostFrequent_SecondaryParticipant_ID

              This table has two instances: ParticipantsPrimary and ParticipantsSecondary

 

Relationships currently:

              Events::PrimaryParticipant_ID = ParticipantsPrimary::ID

              Events::SecondaryParticipant_ID = ParticipantsSecondary::ID

 

When an event is created, PrimaryParticipant_ID is populated with a ParticipantsPrimary::ID value. And SecodnaryParticipant_ID is populated with a ParticipantsSecondary::ID value. For every event there are two related people, one “primary” and one “secondary.”

I would like to end up with the Participants::ID of the person with the most related records in the Events::SecondaryPartipants field in the Participants:: MostFrequent_SecondaryParticipant_ID field.

Expressed a different way: for each Participants::ID I would like a count of all the related records in the Events table that have the same SecondaryParticpant_ID, and then, take the SecondaryParticipant_ID with the highest count and have that populate the Participants:: MostFrequent_SecondaryParticipant_ID field.

Still not sure that makes any sense, but I know what I am shooting for. At the end of the day, I need to produce a report that lists each primaryparticipant, a summary value from the events table (the sum of Events::Points), and the name of the person that was most frequently the secondary participant that participated with PrimaryParticipant. I have it working, except for finding the most frequent secondary participant.

Link to comment
Share on other sites

16 minutes ago, RSGC said:

the most frequent secondary participant.

Do I understand correctly that you want to restrict this to people who were the secondary participant in events where the current participant was the primary? IOW, not the most frequent participant opposite the current participant, regardless of their respective roles?

 

Link to comment
Share on other sites

I am not sure what you mean by "Current Participant." The end report will be of Primary participants. A sum of the POINTS in ALL events (regardless of the secondary participant, and the secondary particpant who participated the most time with the primary participant. Example:

Person A is primary participant in 6 events:

    Event 1 / PP: A / SP: B

    Event 2 / PP: A / SP: C

    Event 3 / PP: A / SP: D

    Event 4 / PP: A / SP: B

    Event 5 / PP: A / SP: B

    Event 6 / PP: A / SP: C

Probably obvious, but I mean the above 5 rows to be records in the events table, PP is primary Participant, and SP is secondary participant. For the example, these are all the Events where A was the primary participant. 

II would like, I think, the MostFrequent_SecondaryParticipant_ID field in the Participants table "B" because B was the secondary participant who most frequently participated when "A" was the primary participant. I say "I think" because possibly there is some way to keep track of "B" without storing it in a field, but I am not there yet in my thinking. 

 

 

 

Link to comment
Share on other sites

I mean that if the events were:

• Event 1 / PP: A / SP: B
• Event 2 / PP: A / SP: B
• Event 3 / PP: A / SP: C
• Event 4 / PP: C / SP: A
• Event 5 / PP: C / SP: A

then in your report participant A would  be shown as having 3 events, with B being the most frequent secondary participant. Even though A participated in 5 events, with C being the most frequent other participant.

 

Link to comment
Share on other sites

Let me try and clarify what I need on the report. Using the same records as above, but adding the points field, and some more events:

   Event 1 / PP: A / SP: B / Points: 2

    Event 2 / PP: A / SP: C / Points: 1

    Event 3 / PP: A / SP: D / Points: 2

    Event 4 / PP: A / SP: B / Points: 3

    Event 5 / PP: A / SP: B / Points: 4

    Event 6 / PP: A / SP: C / Points: 2

   Event 7 / PP: C / SP: B / Points: 5

    Event 8 / PP: C / SP: D / Points: 4

    Event 9 / PP: C / SP: D / Points: 5

    Event 10 / PP: B / SP: X / Points: 4

    Event 11 / PP: B / SP: X / Points: 5

    Event 12 / PP: B / SP: Y / Points: 6

 

Report Output:

Primary Participant                Secondary Participant             Total Points

  A                                                B                                                12

  C                                                D                                                14

  B                                                X                                                15           

 

The report has a sub-summary section only,  and the columns for Primary Participant and Points work as desired, showing just the three rows for thise set of data. It is making the "Secondary" Participant" column reliably show the "most frequent" secondary participant that I am struggling with. 

 

 

 

Link to comment
Share on other sites

Such report can be easily produced from the Events table. You only need to add a summary field that counts the ID field (and of course, a summary field that totals the points).

Use a layout that has only a leading sub-summary when sorted by PrimaryParticipant_ID. Place all three fields in this part, as shown in your example output.

Now comes the magic part: sort the records (1) by PrimaryParticipant_ID, ascending and (2) by SecondaryParticipant_ID, descending + reorder by summary field (the field that you defined to count the records). This will cause the subgroup with the most frequent secondary participant to be the first subgroup in the group - and any field placed in the leading sub-summary will take its value from the first record in this subgroup.

However, in case of a tie only one value will be shown. And of course, any primary participant with no events will be excluded.

 

 

Edited by comment
  • Like 1
Link to comment
Share on other sites

This topic is 579 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.