Excel or Access (1 Viewer)

Wilkesy93

New member
Local time
Today, 21:32
Joined
Mar 12, 2021
Messages
9
Hi Everyone,

I currently store golf statistics on a week by week basis on a golfer by golfer basis in Excel. I store things such as Player Name, what the tournament was, course name, then lots of info about the course and then finally the statistics of the golfer for that week.

I'm adding about 250 rows per week and now the spreadsheet is running extremely slowly, because I also have formulas which calculate average stats for each golfer for the last 8 weeks, last 16 weeks etc...

My first question is, is Access better than Excel to store this data, if so I can import the spreadsheet into Access but I'm left with a lot of duplicate values, for instance, golfer name, week number, tournament name, course name etc.... Is there a way round this?
 

Ranman256

Well-known member
Local time
Today, 17:32
Joined
Apr 9, 2015
Messages
4,339
access can import excel sheets.
Access will allow you to ask questions like who did what when?
and do stats too.

excel can store the data but you have to build everything yourself.
Go access.
 

mike60smart

Registered User.
Local time
Today, 21:32
Joined
Aug 6, 2017
Messages
1,899
Hi
Are you able to upload a zipped copy of the Excel file you use?
 

plog

Banishment Pending
Local time
Today, 16:32
Joined
May 11, 2011
Messages
11,611
A database isn't a fast spreadsheet, or Excel with Forms. A database is a whole different way of working. It will be a learning curve, but Access is probably a better tool to use if you do it properly.

First and foremost is setting up your tables properly--notice I used the plural--there will be more than 1 table. That process is called normalization:


Read up on it, do a few tutorials and then try and apply it to your data. Once done, set up the Relationship Tool in Access, expand all the tables to show their fields, related them properly then post a screenshot of it back here and we can talk thru your table structure.
 

mike60smart

Registered User.
Local time
Today, 21:32
Joined
Aug 6, 2017
Messages
1,899
Hi
What do the following stand for?
SG:0TT
SG:APP
SG:BS
sg:ATG
SG:T2G
Sg: PUTT
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Sep 12, 2006
Messages
15,614
I agree with @plog

For example, I bet you hardly ever write any code in Excel
You can't do anything decent in Access without a lot of code. You actually add code to limit what you can do with Access commands, as you can do so much damage to your data without meaning to - and there's no undo, as there is in Excel.

The end result is far more powerful, but you can't "hack" a database the way you can "hack" a spreadsheet.

This isn't intended to put you off, it's just there's a bit of a learning curve.
 

Wilkesy93

New member
Local time
Today, 21:32
Joined
Mar 12, 2021
Messages
9
Hi
What do the following stand for?
SG:0TT
SG:APP
SG:BS
sg:ATG
SG:T2G
Sg: PUTT
They're various statistics that I track.

Strokes Gained: Off the Tee
Strokes Gained: Approach
Strokes Gained: Ball Striking (OTT + APP)
Strokes Gained: Around The Green
Strokes Gained: Tee to Green (BS + ATG)
Strokes Gained: Putting
Strokes Gained: Total (T2G + Putt)
 

Wilkesy93

New member
Local time
Today, 21:32
Joined
Mar 12, 2021
Messages
9
I agree with @plog

For example, I bet you hardly ever write any code in Excel
You can't do anything decent in Access without a lot of code. You actually add code to limit what you can do with Access commands, as you can do so much damage to your data without meaning to - and there's no undo, as there is in Excel.

The end result is far more powerful, but you can't "hack" a database the way you can "hack" a spreadsheet.

This isn't intended to put you off, it's just there's a bit of a learning curve.
Never code in excel, just sort of force formulas to do what I want them to, which probably explains some of the slow speed as they're probably not written very well. Happy to learn, still reading through the Wikipedia post from above
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:32
Joined
Sep 21, 2011
Messages
14,047
Hi Everyone,

I currently store golf statistics on a week by week basis on a golfer by golfer basis in Excel. I store things such as Player Name, what the tournament was, course name, then lots of info about the course and then finally the statistics of the golfer for that week.

I'm adding about 250 rows per week and now the spreadsheet is running extremely slowly, because I also have formulas which calculate average stats for each golfer for the last 8 weeks, last 16 weeks etc...

My first question is, is Access better than Excel to store this data, if so I can import the spreadsheet into Access but I'm left with a lot of duplicate values, for instance, golfer name, week number, tournament name, course name etc.... Is there a way round this?
Perhaps set Excel to Manual calculation?
Sounds to me you would be better off staying with Excel?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:32
Joined
Feb 19, 2002
Messages
42,976
Access won't be a straight conversion but it will give you options you don't have with the spreadsheet approach. Part of the conversion will eliminate the duplication immediately. For example, you would probably start with the players you are tracking and assign them all ID's. Then as you import the tournament data, you will link to the already imported players on the full name field but use their new ID's to tie it all together.

Start by laying out what you think your main tables will be and a summary of the data they will each contain. Post your questions as you attempt to dissect the spreadsheet into tables.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Sep 12, 2006
Messages
15,614
Do you store round results by hole, stroke index and that sort of thing.
That's tailor made for a database.

I wrote one at one time that stored round results for players. I looked for it a while back unsuccessfully. If I come across it, I will upload it. I might even have another go.

This sort of table structure, anyway. Can you have a look and let me know if I am missing anything? Does it make sense? Where do you see your stats fitting in? In the following I would have the first field in each table as an autonumber PrimaryKey, and the same named field and value as the foreign key in linked tables.

So for example, the CourseDetails table below consists of (up to) 18 records (per course), one for each hole of the related course.

Players - PlayerID, Name, DOB, Handicap, (You probably need a Handicap History, as it will affect the round result taking handicap into account)
Courses - CourseID, Name, Address, (some venues have multiple course)
CourseDetails - CourseDetailsID, CourseID, HoleNumber, Length, Par, StrokeIndex
-you may want different versions for different tees, ladies/mens etc
RoundsPlayed - RoundID, PlayerID, CourseID, Dateplayed, Conditions, RoundType, Notes
RoundDetails, RoundDetailsID, RoundID, HoleNumber, Par, AdjustedPar, StrokesTaken, Notes

I think the only thing above that denormalises the above data is storing the adjusted par in the round details table, as this can be calculated from the CourseDetails table. However, one issue is that the golf club might change the length of holes over time, even the par numbers, and the stroke index. It complicates things if you need to track changes, as the course you played last year might not be the same as you played this year. But that's why I stored the par and the adjusted par as well as the actual shots in the round details table. You don't need to store hole outcomes such as birdie, par, because you calculate that each time. I also didn't store the total shots per round, because that is the sum of the shots for the holes played.

If you want to get really silly, you could record each shot separately, club used, distance, and so on. I imagine the PGA must do that for some stats, maybe the drive details, number of shots tee to green, number of putts, lost balls, in water, penalty shots, in bunkers, fairways hit, all that sort of thing. Every little change you want to include means you need to consider the data model, which is why the data analysis is so important.

The thing about this project is that it's eminently doable. It's small enough to be doable relatively easily - there's only a handful of tables - but it's interesting enough to be a great learning tool for Access, especially as it's a subject you will understand. A couple of days should get you well on the way.
 
Last edited:

Wilkesy93

New member
Local time
Today, 21:32
Joined
Mar 12, 2021
Messages
9
Hi Everyone,

Thanks for all the advice super useful. I've done some reading overnight and think I have the beginnings of what I want, I've posted it below, if you could let me know if i'm along the right lines that would be super useful

Table 1 (Player)

Player Id, Player Name

Table 2 (Course)

Course ID, Course name, Type of Green, Location, Type of Course,

Table 3 (Performance)

Player ID (or should this be player name), Course ID (should this be course name), Tournament, Date, Then the Strokes Gained data from that weeks tournament.


A quick question, when it comes to type of green, in my spreadsheet I listed the types of green as different columns and put a 1 when that type applied and left it blank when it didn't. Is it better to do the same with the database with a yes/no field or is it better to have a Text field with the different types?

Thanks again for everyones help
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Sep 12, 2006
Messages
15,614
Green Type

You do not want to have separate columns in the course table, or the holes table to distinguish features about the course. Effectively, you put all the entries for a given attribute into a single column (as you could do in Excel) and then use a filter to distinguish the particular characteristic (if you need to).

Surely the type of green corresponds to a hole, rather than a course?

But rather than having text values, you should have a separate table listing all the green types, with a numeric key. In the "holes" table you store the index value of the selection, using a combo box. This way there are no typos, and it's far easier to manage. You can only pick from the items in the table, so you provide different results by adding extra entries to the selection table. If you do it this way, you add more "analysis types" to the selection tables without ever needing to modify your code. You can also modify a description without needing to change records that have used the corresponding index value - although this does mean you need to take care how you make amendments - So:

1. elevated
2. sloping
3. whatever

Maybe a separate table for green size if it's important
1. small
2. medium
3. large

obviously you do have to consider all the different attributes you want to analyse.
 
Last edited:

Minty

AWF VIP
Local time
Today, 21:32
Joined
Jul 26, 2013
Messages
10,355
As an avid golfer (when not in lockdown) I have been looking at creating a scoring database for some time.
I might (if I ever get time) wing through this example data and set something up. No promises though.
 

plog

Banishment Pending
Local time
Today, 16:32
Joined
May 11, 2011
Messages
11,611
We get a lot of Excel people here who are trying to move to Access, and they struggle because its a different manner of thinking. Let me say, that with what you posted for tables; you are at the top of the class. You're tables so far show that you are getting it.

I don't think Table3 should be as proposed. I think it should be called [Tournaments] and not have player information. It should be something like this:

Tournaments
Tourn_ID, autonumber, primary key
ID_Course, number, foreign key to CourseID in Courses table
Tourn_Date, date, date of tourn (possibly 2 fields--start and end date)


Then Table4 will hold the players of the tournament and possibly their scores. I say possibly because it all depends on how grainular you want to store scores. Do you want to know scores hole by hole? Round by round? Or just total for the tournament? In any case the next table would have these fields:

TournamentPlayers
tp_ID, autonumber, primary key
ID_Tourn, number, foreign key to Tourn_ID in Tournaments
ID_PLayer, number, foreign key to PlayerID in Players

And as you see, you store the ID not the name (or any other data) when you make connections between tables. ID_Player goes into TournamentPlayers and then we use queries to combine the 2 tables and get the rest of the Player information when we need it.
 

Wilkesy93

New member
Local time
Today, 21:32
Joined
Mar 12, 2021
Messages
9
We get a lot of Excel people here who are trying to move to Access, and they struggle because its a different manner of thinking. Let me say, that with what you posted for tables; you are at the top of the class. You're tables so far show that you are getting it.

I don't think Table3 should be as proposed. I think it should be called [Tournaments] and not have player information. It should be something like this:

Tournaments
Tourn_ID, autonumber, primary key
ID_Course, number, foreign key to CourseID in Courses table
Tourn_Date, date, date of tourn (possibly 2 fields--start and end date)


Then Table4 will hold the players of the tournament and possibly their scores. I say possibly because it all depends on how grainular you want to store scores. Do you want to know scores hole by hole? Round by round? Or just total for the tournament? In any case the next table would have these fields:

TournamentPlayers
tp_ID, autonumber, primary key
ID_Tourn, number, foreign key to Tourn_ID in Tournaments
ID_PLayer, number, foreign key to PlayerID in Players

And as you see, you store the ID not the name (or any other data) when you make connections between tables. ID_Player goes into TournamentPlayers and then we use queries to combine the 2 tables and get the rest of the Player information when we need it.
Thanks for this very helpful, so if I want to collect data on each player for the tournament as a whole but multiple stats would I just add fields on to the TournamentPlayers table for each stat I wanted to collect?

Feel like I'm getting somewhere!
 

plog

Banishment Pending
Local time
Today, 16:32
Joined
May 11, 2011
Messages
11,611
I want to collect data on each player for the tournament as a whole but multiple stats would I just add fields on to the TournamentPlayers table for each stat I wanted to collect?

You would probably need a new table. If it was just 2 stats you could cram them in TournamentPlayers, but more than that and you need a new table:

TournamentStats
ts_ID, autonumber, primary key
ID_tp, number, foreign key to tp_ID in TournamentPlayers
ts_Type, text, this will hold the name of the stat ("Longest Drive", "Birdies", "Beers Drunk", etc.)
ts_Value, number, this will hold the value of the stat

Again, that's just the most likely scenario, it really depends on the data itself.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:32
Joined
Sep 12, 2006
Messages
15,614
I think maybe the idea of "stats" in general is an issue

You wouldn't normally store anything you could calculate from first principles. If stats can be extracted from other information in the database then you don't really need to store the statistic at all,. I actually struggle to see what sort of statistic you could calculate - unless you really mean "general impressions" rather than true statistics.

Hence it may be better to extend the information in the database to enable the stats to be produced, I am surprised you don't want to store hole by hole results - but I guess you could add facilities to do that later.
 

mike60smart

Registered User.
Local time
Today, 21:32
Joined
Aug 6, 2017
Messages
1,899
Thanks for this very helpful, so if I want to collect data on each player for the tournament as a whole but multiple stats would I just add fields on to the TournamentPlayers table for each stat I wanted to collect?

Feel like I'm getting somewhere!
The attached are screenshots of how your data would look like in Access
On the TournamentYear screenshot you can look at previous years or add a New Year.
The Players Firstname & Surname are in separate Columns due to the Import of Data.
The Combobox for selecting a Player is looking up a List of Current Players
Once all of the Players have been selected for all of the Tournaments then the Controls for Firstname & Surname can be deleted.

Would this suit your requirements?
 

Attachments

  • Coursedetails.JPG
    Coursedetails.JPG
    135.4 KB · Views: 330
  • TournamentYear.JPG
    TournamentYear.JPG
    196.9 KB · Views: 327

Users who are viewing this thread

Top Bottom