Excel Spreadsheet Data

[Guest]

I am a ice hockey person that keeps the stats for my local club, for my
interest only, for now. I am now thinking of expanding this to other clubs,
which when you consider the way I do it in Excel would be very hard to
accomplished. It works great for one club but not to great for many.

I have a very limited knowledge of Access, but enough to be able to set up
several tables the way I want. But now comes my problem, I don’t know how to
extract the information from my spreadsheet to Access. The original data
will always be housed in Excel; I just need a way to create a report that
will give me the information for several different teams at once.

Below is a small sample of how the data looks in Excel.


No Name G A +/- Sh PIM
3 J. Beckett 0 0 -1 3 2
4 N. Martz 0 0 +1 4 0
5 S. Slonina 0 1 0 3 0
6 B. Young 0 0 +1 0 2
8 B. Hodge 2 0 +1 5 0
Team: 0 0 0
Totals: 4 8 1 43 20

No Name Min SH SVS GA SOG-S
40 D. Dubnyk (SOL) 65:00 39 35 4 2-6



I have cut down the actual information to show just a few players, but you
get the idea. So what I want to be able to do is pull from Excel this game,
from a worksheet that I have called Game1, the number of goals for each
player and so forth for all other information. Also each game will have, its
own worksheet, called Game#.

So is this possible

 

[dcichelli]

Excel...get rid of it!!


Ok let's talk database design. You need several tables. Remember, each
table should house information about a SINGLE entity. So, you will need
a seperate table for players and games.


In your Players table you will have these fields (for example):

PlayerID - Autonumber
PlayerNum - Number
PlayerName - Text (e.g. J. Beckett, N. Martz)
PlayerPosition - Text (e.g. goalie, forward)
TeamID - Lookup field to team table

PlayerID PlayerNum PlayerName PlayerPosition TeamID
1 3 J. Beckett Goalie
Killlers (1)
2 4 N.Martz Left Forward
Chillers (2)
3 5 S. Slonina Right Forward
Scorers (3)
4 6 B. Young Backup Goalie
Icemen (4)



You then need a team table:

TeamID - Autonumber
TeamName - Text
TeamCoach - Text
TeamTown - Text


TeamID TeamName TeamCoach TeamTown
1 Killers B.Smith St.
Paul, MN
2 Chillers J.Armstrong
Chicago, IL
3 Scorers K.Stalworth
Calgary, AL
4 Icemen N.Michaelson Albany,
NY
5 Hotties G.Underworth Boisie,
ID


In your Game table you will have only info that applies to each game:

GameID - Autonumber
GameLocation - text (Calgary, St. Paul, Albany)
GameTeamA - Lookup to Team table
GameTeamB - Lookup to Team table

GameID GameLocation GameTeamA GameTeamB
1 Calgary Killers (1)
Icemen (4)
2 St. Paul, MN Scorers (3)
Chillers (2)
3 Albany, NY Hotties (5)
Chillers (2)



You then need a GameStats table that relates players to each game:

GameStatID - Autonumber
GameID - Lookup field to the table Game.GameID
TeamAID- Lookup field to the table team.TeamID
TeamBID - Lookup field to the table team.TeamID
PlayerID - Lookup field to the table Player.PlayerID
Goals number
Assists - number
.....whatev

GameStatID GameID PlayerID G A
1 1 N.Martz (2) 0
0
2 1 J. Beckett (1) 1
1
3 1 S. Slonina (3) 1
1
4 2 S. Slonina (3) 2
2
5 3 N.Martz (2) 2
0
6 3 J. Beckett (1) 0
1
7 2 B. Young (4) 2
0
8 1 B. Young (4) 1
0
9 2 J. Beckett (1) 0
0
10 3 B. Young (4) 1
2
11 2 N.Martz (2) 0
0



Wow! So many awesome queries can come from this ---- can u dig it?

 

[Guest]

I went looking just today to see if anybody ever replied to my question, and
just saw that you did. I have to apologize for not checking sooner. I also
never got a E-Mail from the board that you had responded. I will look over
your respond fully, this weekend but at first glance I'm very exicted,
becasue it seems that it will work just fine.