Converting Excel scoresheet to Access database

[jdp311]

Right now I've got a spreadsheet which has all the scores for a medium size poker league I'm running. It's getting a little too big to keep using Excel (though Excel is perfect for this) so I'm wondering how to get started in moving over to Access.

You can see the spreadsheet in html form here: http://www.jcorner.net/pokerresults1.htm

It's just a basic spreadsheet with the names in one column, a bunch of columns with scores and then columns for the total (sum), average and games played (count) for each person.

I see examples and tutorials all over the place on-line but they all seem to deal with addresses and phone numbers. I need some help with all the math and formulas that I'll need. For instance, it seems that the SUM fuction in Access only works with columns (fields). In other words I can get the SUM of all the game 1 scores but not the SUM for all of Bob's games.

Thanks in advance for any help you can give or if anyone could even point to any tutorials or examples that would help with a job like this.

- Josh

 

[Pat Hartman]

it seems that the SUM fuction in Access only works with columns (fields). In other words I can get the SUM of all the game 1 scores but not the SUM for all of Bob's games.

Yes and No. Yes it is true that in relational databases (not just Access), aggregate functions work within a column and do not work across a row. However, you would not store your data in a relational table in the same mannor as you store it in a spreadsheet. You will need three tables:

tblPlayer
PlayerID (autonumber primary key)
PlayerName
...any other info about a player but NOT totals

tblGames
GameID (autonumber primary key)
GameDate
...any other info about a game but NOT totals

tblPlayerGame
PlayerID (long integer, foreign key to tblPlayer, primary key fld1)
GameID (long integer, foreign key to tblGames, primary key fld2)
Score (the data that is presently at the intersection of your rows and columns)

To normalize this data will require a bunch of queries and a little clean up on the spreadsheet.
1. Remove any extraneous rows or columns leaving only the "table" portion of the sheet. What are those unlabeled columns that appear between games 1 and 2, 3 and 4, 5 and 6, etc.? They should be removed or labeled. Label the first column as PlayerID
2. import the spreadsheet into an Access table so it will be easy to work with.
3. To populate the player table, create an append query that selects the PlayerID column and the Names columns and append them to the PlayerID and PlayerName columns of tblPlayer.
4. To populate the game table, type in the game dates and let the autonumber enter the game numbers.
5. To populate the tblPlayerGame you'll need an append query for each column of data so given the sheet you are showing, you'll need to run 45 append queries. This isn't really as tedious as it sounds. Create one query, run it and modify it so that each time you run it it appends data from a different column. This append query will select the PlayerID, and the data from ONE column, There will also be a literal value that needs to be supplied for the GameID. So when you are appending data for game 1, use 1 as the value for GameID. 2 for game 2, 3 for game 3, etc. So each time you run this query, you will be changing TWO things, the literal value of GameID and the game column to append this time.

OK, take a deep breath. Once all the data is appended. You will verify it by running queries that sum the data. Then you can compare Total, Average, and Games to the original values in the spreadsheets. The query to produce the totals will look like:

Select p.PlayerID, p.PlayerName, Sum(g.Score) AS ScoreTotal, Avg(g.Score) as ScoreAverage, Count(*) As GameCount
From tblPlayer as p, Inner Join tblPlayerGame as g ON p.PlayerID = g.PlayerID
Group by p.PlayerID, p.PlayerName;

If you want to produce a spreadsheet like report, you can create a crosstab query that joins the above query to the tblPlayerGame that wll look very much like your original sheet.

You should also download my many-to-many sample db that you will find in the samples forum. I know it doesn't seem like an event/venue db could possible be relevant but the structure is EXACTLY like what you need. This db will show you how to use forms to do your data entry.

 

[jdp311]

I haven't absorbed all of that yet but it looks like exactly the kind of explanation I needed, thank you very much!

p.s. the funky extra columns were because some games counted twice but I'm changing that so in the database it will be just one score per game.

 

[Pat Hartman]

You can even do that with Access. Add a weighting field to the tblGame. Then you can multiply the score times this weight. Just default it to 1.

 

[jdp311]

I created a test database with a small amount of data so I could try the query which is the part I'm having the most trouble wrapping my head around.

I created three test tables with the names you used and entered 5 players with various scores.

I then created a query and pasted your query formula in there and got a syntax error. I tried changing up the formula, taking out the tblPlayer as p and all that and just putting in tblplayer.PlayerID when I needed to refer to it for instance but it still doesn't like the syntax.

Any idea what might be causing that?

Actual error: Syntax error in FROM clause. When I hit ok it highlights the word INNER from INNER JOIN

 

[Pat Hartman]

I see an extraneous comma between p and Inner. Sorry.

From tblPlayer as p Inner Join tblPlayerGame as g ON p.PlayerID = g.PlayerID
Group by p.PlayerID, p.PlayerName;