Calculate How Many Times I've Played a Game with Another Player

[Ashley_Jackson]

Hi all, I've got a fun little challenge on my hands designing a system to track our family's board game collection and gameplay.  Here's my base structure:

1. Games - list of all the games in our collection
2. Play Log - tracks the date, time, location, players, and outcome of all the games we play
3. Score Sheets - this is the connecting table between the logs and the players since there can be many players in a log and a player can play many games
4. Players - list of players

let's use a sample record, tracked all the way through my base:

Myself, hubby, and son play a game of Monopoly.  The play log tells me we played at our home on 10/16/2023 at 6:00pm and then lists the 3 of us as players linked to individual records in the Score Sheets table.  In each Score Sheet record, our final scores are recorded and calculated to determine the winner which is fed back into the Log table as the outcome.

Now, multiply that same scenario by 10-15 more games played with a variety of other people.

Here's what I would like to see:

A list of each person I've played a game with and how many games I've played with each of them.

My brain is blocked on this one.  I was able to achieve something similar in which I wanted to see a list of the games I have played with how many times I've played each one.

The generate list of games I've played along with how many times I've played each.

Does anyone have suggestions on how I could accomplish this?  Thanks so much!!!

[Ashley_Jackson]

EDIT:  Scratch that... I just realized my totals for games played does NOT work correctly... so I am back to square one!  lol - somebody save me!

[SalimSalim]

To figure out how many times you've played a game with another player, check the game’s history or stats section if it has one. Many online games track your matches and who you’ve played with. I remember trying to keep track of my Spider Solitaire games manually before finding a version online that kept all the stats for me. If your game doesn’t have this feature, maybe jot down each session for a while and see if a pattern emerges. It's a bit old school, but it works!

[hooram]

To calculate how many times you've played a game with each person, you need to create a process to track and count player pairings. Start by identifying all pairs of players from each game session recorded in the Play Log and Score Sheets tables. Aggregate these pairs across all game sessions to determine the frequency of each pairing. You can use a pivot table or a database query to count how often each player pair appears. This will give you a comprehensive list of each person you’ve played with and how many games you’ve shared with them.