Help on updating and normalizing an old database. 2

[Cwoolley]

I have spent countless hours working on a database that tracks foosball stats. We have been tracking stats with this database since early 2004. (Please no geek comments... ;-) We track the scores, but also the winning/losing percentages of positions, etc.

The table structure is very simple, but I have a very hard time creating queries. I wonder if my understanding of normalization is incorrect and if my table structure is bad. The underlying database is so simple, it seems to me that I should be able to create queries and reports with more ease than I do. Perhaps it is my own shortcomings, but I was hoping to get some thoughts from you experts.

DB Structure: (3 tables)

Games
- GameID (PK)
- Type <-- Doubles or Singles
- ScoreA
- ScoreB
- Date

Players
- PlayerID (PK)
- FirstName
- LastName
- Inactive <-- Yes/No
- PicName
- email

Positions
- PositionID (PK)
- GameID (FK to Games)
- PlayerID (FK to Players)
- Position <-- DefenseA, DefenseB, OffenseA, OffenseB

Each Game (doubles) ends up with four players. (ie. the Positions table has 4 entries for each game. 2 entries for each singles game.)

The main problem that I am experiencing is that anytime I want to create a report. I have to create several queries that build on each other, before I get to any interesting data. Is that typical behavior for a normalized database, or is my table structure messed up? For instance, to build a report that counts the number of games a player has won and who that player partnered with is quite a project.

I would like to rebuild/update this database. Any suggestions from you experts would be greatly appreciated.

 

[fneily]

So it seems your Positions table can be considered "teams". So you have players that need to be assigned to a team. And they can change teams. Then these teams need to be assigned to a game on a certain date with a score.
I'd have:
tblPlayers with PK PlayerID and the rest as you stated.
tblPosition with PK PositionID, Description
Description is DefA, DefB, OffA, or OffB. So 4 records.
Need a table to bring together players and positions:
tblTeam with multi-field PK PositionID, PlayerID

Finally, a game table to bring everything together:
tblGame with multi-field PK of GameID, PositionID
other fields are Date, Score.

Let's see: Game4 is being played by DefA and OffB on 10/15/2010. Score was DefA 16, OffB 7.

tblGame has two records:
Game4, DefA, 10/15/2010, 16
Game4, OffB, 10/15/2010, 7

In a query, you can compare the score on one record to the other record and find the winner. Or you can concatenate the records of a game into one (search forums for concatenting child records or see the FAQ). Who played for DefA? Connect to tblTeam on PositionID (DefA). tblTeam will give a list of PlayerID's on DefA. Connect to tblPlayers to get name.
So in mass, you can find all the teams that won, make a list of all the players on the winning teams and then do a count of each player.
Seems to work. My first pass. May change it later.

 

[Cwoolley]

Thanks for the input fneily.

I'm not sure if I understand everything you said. I apologize for my ignorance...

A couple of clarifications.

A doubles game always has 4 players; 2 teams with an offensive player and a defensive player on each.

'Teams' are not static at all. The database semi-randomly assigns partners to each other based on their skill level, how long they've sat out, etc. and a player will play on different sides of the table (a&b) with each different game, playing both Offense and Defense, with many different partners.

For instance: First game, Tracy (OffA) and Marshall (DefA) play against Chad (OffB) and Jackson (DefB). Score 5-2 for Chad and Jackson.
Second game, new players on the table randomly assigned. TeamA: Mark (OffA), Josh (DefA)
TeamB: Jeff (OffB), Cody (DefB)
Score: 5-3 for TeamA
Game3:
TeamA: Cody (OffA), Tracy (DefA)
TeamB: Chad (OffB), Marshall (DefB)
Score: 5-0 for TeamB
And so on...

Can you clarify what you mean by "multi field PK". Does that mean tblTeam has more than one Primary Key? If so, I don't understand that concept.

Thanks for any additional input you may have.

Chad ;-)

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

First a speech. Access is not like the rest of Office. It has alot of non-intuitive concepts. One must spend alot of time reading books, taking courses, trial and error, etc. Weeks, months, years. Examples are normalization, relationships, multi-field PK's, VBA, ADO/DAO and on and on. So when someone mentions a multi-field PK, you break out the books. And in my opinion, you can't start creating Access databases unless you learn how to Normalize the tables. PHV mentions some references to read.

So let's see if this'll work. May Codd have mercy on my soul.(that is an Access pun)

tblPlayer like yours. PK PlayerID
tblPosition with PK PositionID, Description
PositionID is DefA, DefB, OffA, OffB
tblGame with Pk GameID, Date, Description
tblParticipants with ParticipantID, TeamID, PlayerID, PositionID

Eamples:
tblPosition
DefA Defense for A
DefB Defense for B
OffA Offense for A
OffB Offense for B

tblParticipants
1 TeamADate Tracy OffA
2 TeamADate Marshall DefA
3 TeamBDate Chad OffB
4 TeamBDate Jackson DefB
5 TeamANewDate Bob DefA
Notice to distinguish between TeamA on different dates, I attached a date. You may create your own way of separating them.

tblGame
Game1 10/10/2010 Giants against Ravens
Game2 10/11/2010 Rams against Broncos

Now bring everything together in tblContests
tblContests
ContestID GameID TeamID Score
1 Game1 TeamADate 2
2 Game1 TeamBDate 5
3 Game2 TeamANewdate 0
4 Game2 TeamBNewDate 5

Again, by using what I mentioned in the first post, you can compare scores of games and find which team won. Then connect to tblParticipants through TeamsID to get playerID's. Connect to tblPlayer to get names. Etc. Etc.

 

[Cwoolley]

PHB & fneily:

Thank you both for the info. I will thoroughly read through this information and try to implement. I really do appreciate your time.

I understand the concept of spending time researching and putting in the time to understand difficult concepts. I have been a member of tek-tips for over 5 years (probably longer). Most of the time I find my answers by searching through the valuable info that you and others leave. This particular issue is one of the few questions I've ever asked.

The answers you've given me make it very clear to me that my 'simple' database, is not as simple as it would first appear. I thought I had created a complexity out of something that should have been done more elegantly and simply.

Your posts have confirmed the fact that it isn't as straight forward as it seems to me it should be and that I'm struggling because it is the nature of the beast.

Just for fun, I've attached a couple of my queries so you can see the solutions that I've come up with. I'm hopeful that I'll be able to update this database with the helpful info you've given me and we'll have some great foos nights into the future!

Thanks Again...

Chad ;-)

Explanation of Attachments:

Not attached - [Doubles Report] is a form that allows a person to see his percentages with all other players. It is dependent upon two queries [DoublesWins] and [DoublesLosses]

[DoublesWins] and [DoublesLosses] are dependent upon [Doubles2]

[Doubles2] is dependent upon [da] [oa] [db] and [ob] as well as the [Games] table

This example is one of the reasons I posted the original question. It seems to me that a report like this should be easier to create. I've had to create 7 queries to make a 'simple' report.

Thanks Again!