Table Structure Issue - Bowling Database (1 Viewer)

[Robecca]

I had all my tables built and I started desiging my queries for my forms. I did not have any problems with my tables until now, when I started to bring build the queries/forms for team points.

My database is for our summer bowling league. We have teams of 3, each week we draw for team members. Teams are created based on what lane# you draw. We bowl 4 games. 1st game is to determine the position line up for the 2nd game, no points awarded. 2nd game - 4th game, the team on that pair of lanes that wins the match is awarded 10 points to add into their score. After 4 games, the team with the most pins wins.

To this point, I've checked in the bowlers and assigned them to teams and captured each bowler's games and their individual points if they beat their opponent. All working great.

However, I can't seem to get the working between teams and team points.

Every bowler is assigned a team
Each team bowls 4 games and can be awarded 10 points for games 2-4 to be added to their game totals.

I built a table for assigning bowlers to a team(CheckInTeam. Fields are:
ChkTeamID (PK Autonumber
ChkInID (is PK in the CheckInBowler table)
TeamID (is PK in the Team table, holding the name of the team)

I then built TeamBonusPoint table with these fields:
TeamPointID (PK Autonumber)
ChkTeamID (is PK in CheckInTeam table)
GameID (is PK in Game table)
TeamBonus (number - where the 10, 5 or 0 are entered for bonus points)

It feels like my issue is the assigning bowlers to a team, but that worked fine for entering their games, but for team points, it seems like I'm having to enter all the ChkTeamID's, which would be 3 when I only want the team once.

I did not see this coming! Interacting the bowlers & games worked fine, thought this would work the same way, but no.

Any help would be great ... hope I explained it well. Robecca

 

[plog]

I think you need to step back and walk through this a level at a time. I also have some questions about how this all works. Let's tart with those:

1. Are you using this database to track individual frame scores? I.e. Jared got 8 pins on his first throw of the 3rd frame of the 2nd game, and then 1 pin on the subsequent ball?

2. How many teams are competing against another in a game? Is it Team 1 v. Team 2, or could it be more than 2 teams all in the same game?

Could you post your database?

 

[Robecca]

Hi

Our summer league (Scratch Trio) is different b/c we pay out $ to the top 6 teams each week (we bowl on Tuesday nights). We bowl on sport or PBA patterns. We have 14 teams, blind draw, which means new teams of 3 each week based on the Scratch Trio average at the beginning of that night.
We bowl the 1st game (called position round) to establish the lineup for game 2 which starts the “head to head” bowling. Top ranked team bowls 2nd ranked team and so forth through to 13th bowling 14th team. Reason we only have 14 teams is that our house only has 14 lanes. The team that wins game 2 gets an extra 10 points. At the same time, each member of that team is bowling their opponent and will receive 10 points if they win their game. That means for game 2, a team could get an extra 40 points to add to their score. These bonus points are kept separately from the bowling scores b/c averages are based on how you bowl. Game 3 & game 4 are handled the same way as game 2, we realign based on top ranked team.

I am capturing only their games and I need to quickly enter everyone’s games and the bonus points (team and individual). I plan to enter game 1’s scores while we’re bowling game 2 and so forth.

After bowling is done, I need to generate a report to give the “leaderboard” stats for the evening … the team with the highest points first descending to team with the least amount of points.

I have everything working until I tried to build the query & form for capturing the team bonus points. The games bowled by each bowler and their individual points are working perfectly.

Attached is my database.

 

[plog]

Your drowning me in irrelevant details (pay out $ to top 6 teams...we bowl on Tuesday nights...PBA patterns), repeated information and poorly defined words (game, team, "head to head" bowling).

First let's use these definitions:

game - one person bowling 10 frames
set - one team where each team member bowls 1 game
round - all the teams bowling 1 set
match - the entirety of all bowling (4 rounds)


Let me see if I can put the process into steps and correct me when I am wrong. When I am wrong, correct just what I am wrong about in the shortest possible way.

1. People are randomly assigned to lanes (1-14), each lane is now a team.

2. A round is played (Round 1) and the teams are reassigned lanes based on the ranking among other teams of that round. Scores for this round are discarded.

3. A round is played.

Now here's what I don't understand. You say you pair the teams up to compete, but you say that the team that wins game 2 gets an extra 10 points. How do you win--by having a better set score than the team you are squaring off against or by having the best set score of all teams? How many teams get the 10 point bonus--7 or 1?

 

[Robecca]

Sorry, thought the background information would be helpful.

2. Scores are never discarded, next round is added.

Teams get extra 10 points by having a better round against the team they are squaring off against. 7 teams will get an extra 10 points

 

[plog]

I thought the 1st round scores aren't used in calculating total match points. Also, do teams keep their lane and opponent assignments the same throughout the match? In Round 3 and 4 is team 1 going against team 2, team 3 v. team 4, etc.? Or do you reshuffle opponents after each round?

Lastly, are you keeping track of each individual bowlers game scores, or are you keeping score only at the team level?

 

[Solo712]

I had all my tables built and I started desiging my queries for my forms. I did not have any problems with my tables until now, when I started to bring build the queries/forms for team points.

My database is for our summer bowling league. We have teams of 3, each week we draw for team members. Teams are created based on what lane# you draw. We bowl 4 games. 1st game is to determine the position line up for the 2nd game, no points awarded. 2nd game - 4th game, the team on that pair of lanes that wins the match is awarded 10 points to add into their score. After 4 games, the team with the most pins wins.

To this point, I've checked in the bowlers and assigned them to teams and captured each bowler's games and their individual points if they beat their opponent. All working great.

However, I can't seem to get the working between teams and team points.

Every bowler is assigned a team
Each team bowls 4 games and can be awarded 10 points for games 2-4 to be added to their game totals.

I built a table for assigning bowlers to a team(CheckInTeam. Fields are:
ChkTeamID (PK Autonumber
ChkInID (is PK in the CheckInBowler table)
TeamID (is PK in the Team table, holding the name of the team)

I then built TeamBonusPoint table with these fields:
TeamPointID (PK Autonumber)
ChkTeamID (is PK in CheckInTeam table)
GameID (is PK in Game table)
TeamBonus (number - where the 10, 5 or 0 are entered for bonus points)

It feels like my issue is the assigning bowlers to a team, but that worked fine for entering their games, but for team points, it seems like I'm having to enter all the ChkTeamID's, which would be 3 when I only want the team once.

I did not see this coming! Interacting the bowlers & games worked fine, thought this would work the same way, but no.

Any help would be great ... hope I explained it well. Robecca

You explained it well, Robecca.

The problem stems likely from your using ChkTeamID field in the TeamBonusPoints table where you should be collecting them with TeamID which I trust identifies the teams uniquely. If the ChkTeamID is used for assigning bowlers to a TeamID then there will be 3 entries required for each TeamID on any given night. So substitute the ChkTeamID with TeamID and you will likely be fine. I wanted to test this but I can't open your accdb file.

Best,
Jiri

 

[Robecca]

@ Plog

All rounds are calculated in the total match points. Difference is Round 1 does not get any bonus points.

Lanes are reshuffled after each game b/c we want the team in the lead to bowl the 2nd leading team and so forth.

We track the individual bowler’s score by game. Team scores for each round (2-4) are only for determining team bonus points. At the end of the night by team we will have their match total, which is calculated by bowler’s scores (all games), bowler’s individual bonus points and team bonus points.

@ Solo712

I wasn’t sure if I had too many tables. Now, I wonder if I had the ChkInID in the CheckInTeam table (which gave me 3 entries). When actually I should have had the ChkTeamID in the CheckInBolwer table (this way the 3 bowlers which already have their own records all have the same ChkTeamID).

Thanks! Robecca

 

[Solo712]

@ Solo712

I wasn’t sure if I had too many tables. Now, I wonder if I had the ChkInID in the CheckInTeam table (which gave me 3 entries). When actually I should have had the ChkTeamID in the CheckInBolwer table (this way the 3 bowlers which already have their own records all have the same ChkTeamID).

One thing at a time, Robecca. I think your ChkTeamID table is fine if its purpose is to relate bowlers (ChkInID) and teams (TeamID). Actually it's a good structure and if you add a date field to the table you will be able to recreate past team lineups and results.

I would first try to check if switching ChkTeamID to TeamID in the TeamBonusPoints will do the trick of collecting the points for the teams correctly.

Best,
Jiri

 

[Robecca]

My purpose is to relate bowlers and teams. Actually we do want to see past team lineup & results. I will give your suggestion a try tomorrow, since I'm bowling tonight, I won't be back on before then. Have a great evening!

 

[Robecca]

I tried yesterday the switching of ChkTeamID to TeamID in the TeamBonusPoints table. Seemed to work fine until I did a query with the CheckInTeam and then I was getting multiple lines per team/game.

I ended up deleting ChkInID from the CheckInTeam table and putting the ChkTeamID into the CheckInBowler table. After doing this, I could get my total pins for each team which is adding bowler scores+ bowler bonus points + team bonus points.

I am done with tables and forms now, it leaves me with building my reports. I know a couple will be tricky for me.

Thank you Jiri, for your suggestion, it got me thinking about what I finally tried!