Bowling - Design Recap Form for Entry

[Robecca]

I am building a bowling database and I had a tabbed form for entering games, however, my brother would rather have a form that looks like a recap.

I don’t know how to get a form to look like this when it takes 4 records per bowler.

My Scores table has these fields:
ScoreID (PK)
CheckInID (FK - brings in bowler, week, team info)
GameID (FK - Game1, Game2, Game3, Game4)
LaneID (FK - lane assignment)
Score - bowling score

Thanks for any help! Robecca

 

[Solo712]

I am building a bowling database and I had a tabbed form for entering games, however, my brother would rather have a form that looks like a recap.

I don’t know how to get a form to look like this when it takes 4 records per bowler.

My Scores table has these fields:
ScoreID (PK)
CheckInID (FK - brings in bowler, week, team info)
GameID (FK - Game1, Game2, Game3, Game4)
LaneID (FK - lane assignment)
Score - bowling score

Thanks for any help! Robecca

Hi Robecca,
you need to design your db tables before you start using them to create forms or execute particular queries. Specifically, you will need to decide whether games are attributes of scores, whether, or how, scores relate to assigned lanes or if a table hooking bowlers with teams for a particular night is a good place to place "team info".

As it stands, your sample table looks pretty confused about the entities and their relationships.

Best,
Jiri

 

[Robecca]

Jiri,

Thank you for responding.

I either gave too much information on CheckInID or I am lost.

My goal with my Score table is to capture by bowler, the game# & score bowled. I was including Lane# b/c we switch lanes after each game and thought that was the place to put it.

I took a short intermediate Access course from our local college and my instructor indicated that I needed a table that would capture one game per bowler per record. I thought I’d accomplished it.

My CheckInID represents the bowler, but it relates the bowler to the team and week bowled.

I’d appreciate your comments if I am not doing this right. I am wanting to learn and grow, I love working with Access, however except the recent class, I am self-taught.

Thanks! Robecca

 

[Solo712]

Jiri,

Thank you for responding.

I either gave too much information on CheckInID or I am lost.

My goal with my Score table is to capture by bowler, the game# & score bowled. I was including Lane# b/c we switch lanes after each game and thought that was the place to put it.

The Score table does not make any sense. Scores or 'game results' are not entities but attributes wholly dependent on an entity called 'game'. So they should be in that table or closely tied to that table via TeamID.

The game table is the pivot aound which you should build your structures. You identify the game by the date, the sequence number ( which you indicate is 1 to 4), lane assigned and the teams participating. If you always have two teams per game (which I assume is the case) you can keep the scores with of the two teams in the same game record. The game table should not care a fig what the identities of the bowlers are. It assigns the scores to the team's L/M/A positions, and the team itself (I believe you indicated the bonus was on a team). If you need to know who the bowlers are in the team's positions on that night you query the CheckInID. There is no reason to duplicate this info in the game table or anywhere else.

So eg. you have a game ID #55, which is sequence#3, on date x, in lane #8, with teamsID #8 and #11. This game keeps two sets of L/M/A scores and the bonus scores. All there is to it.

If then, you need a query such as you indicated in the SCRATCH TRIO
you find Team 1 games ' 1 to 4 on date x, and fill in CheckIn Bowlers to the positions L/M/A. You are done.

I took a short intermediate Access course from our local college and my instructor indicated that I needed a table that would capture one game per bowler per record. I thought I’d accomplished it.

Not sure what he/she saw. On what you are showing me, I would say you need a separate score card per game - not bowler.

My CheckInID represents the bowler, but it relates the bowler to the team and week bowled.

I’d appreciate your comments if I am not doing this right. I am wanting to learn and grow, I love working with Access, however except the recent class, I am self-taught.

Thanks! Robecca

No shame being self-taught, Robecca, if you are self-teachable. Your CheckInID table looks correct, BTW. You will need to add the L/M/A position. If these change from game to game you will have to have separate checkin's for each game.

Best,
Jiri

 

[Robecca]

Jiri,

Let me see if I am grasping this b/c I don’t feel I am.

I should call my table Game instead of Score b/c our score bowled is an attribute of the Game.

My Game table should have:
GameID
Sequence Number (representing Game1 – Game4)
Date
Lane Number
L Position
M Position
A Position
TeamID
Bonus

Where does CheckInID come in? I am not seeing it, is it in my Game table like I had it in Score?

Thanks! Robecca

 

[Solo712]

Jiri,

Let me see if I am grasping this b/c I don’t feel I am.

I should call my table Game instead of Score b/c our score bowled is an attribute of the Game.

My Game table should have:
GameID
Sequence Number (representing Game1 – Game4)
Date
Lane Number
L Position
M Position
A Position
TeamID
Bonus

Where does CheckInID come in? I am not seeing it, is it in my Game table like I had it in Score?

Thanks! Robecca

Hi Robecca,

The CheckIn table is linked to GameID through TeamID
You might want to split the above GameID table into Game and GameTeamScorecard tables

Game Table


GameID <--------------> GTSC
Sequence
Date
Lane
...etc.

GameTeamScorecard table:

GTSID
GameID
TeamID
L Position
M Position
A Position
Bonus

There would be 2 ScoreCard records per game (but could be more if you decide to play a 3-team game). Now once you get the hang of the relational scheme you will see that the CheckIn table connects to the score via TeamID association which persists for the 4 games on a given night.


CheckInID
BowlerID
TeamID <-------------> GTSC
Date
Position

You don't have to duplicate it anywhere. I have quickly put together the structure for you to see how I envision it. The example attached has eight teams. I created a "RECAP" query with the games of the selected team of course being rows and not columns. I hope you see the relationships in a differet light now.

Best,
Jiri

 

[Robecca]

Jiri,

I will have to take time to process what you are showing me. I will get back to you today or tomorrow.

Thank you! Robecca

 

[Robecca]

Jiri,

I am trying to work this out through my mind. With the tblGame and tblGTSC, then I still multiple records that need to be on 1 line in my recap form ... correct?

Thanks! Robecca

 

[Solo712]

Jiri,

I am trying to work this out through my mind. With the tblGame and tblGTSC, then I still multiple records that need to be on 1 line in my recap form ... correct?

Thanks! Robecca

Not necessarily. Take a peek at the pivoted query and form that I have created. You could do more sophisticated transformations with VBA / SQL.

Best,
Jiri

 

[Robecca]

Jiri,

How do you make a pivot table editable? My brother wants a form that looks like the recap sheet I attached for entering game scores.

Thanks! Robecca